column order in SELECT * statement - guaranteed?

Question

I am using an ORM (sqlalchemy) to fetch data from a PG database. I want to avoid specifying all the table column names in my hand crafted SQL statements*.

My assumption so far is that the returned columns are in the order of the DDL statements used to create the db tables. So far this is working - but I want to know if this is merely luck, or if it is specifically addressed in the (ANSI) SQL specification.

i.e. does ANSI SQL (and thus presumably the database) guarantee the order of columns returned in a SELECT * statement?

I am using PostgreSQL 8.4 as my backend db

• yes, I am aware that using hand crafted SQL statements with an ORM defeats the purpose of an ORM, but needs must ...

Poinsettia

You probably have your reason to do so, but this a bad practice. You should explicitly select columns you want to use. – Affra

Answer 1

Let's consider the SQL standard, section 7.9 <query specification> as specified here:

http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

<query specification> ::=
          SELECT [ <set quantifier> ] <select list> <table expression>
[...]
<select list> ::=
            <asterisk>
          | <select sublist> [ { <comma> <select sublist> }... ]

[...]
Syntax Rules
1) Let T be the result of the <table expression>.
3) Case:
       a) [...]
       b) Otherwise, the <select list> "*" is equivalent to a <value
          expression> sequence in which each <value expression> is a
          <column reference> that references a column of T and each
          column of T is referenced exactly once. The columns are ref-
          erenced in the ascending sequence of their ordinal position
          within T.

So, in other words, yes, the SQL standard specifies that columns are to be projected according to their ordinal position within T. Note, that things get a bit tricky, when your <table expression> consists of several tables involving JOIN .. USING or NATURAL JOIN clauses. However, when selecting from a simple table, you're probably fine assuming that the order is as expected.

For completeness, the meaning of an ordinal position within T for tables is explained further down in 11.4 <column definition>:

General Rules
     5) [...] The ordinal position included
        in the column descriptor is equal to the degree of T. [...]

And then in 11.11 <add column definition> (for ALTER TABLE statements)

General Rules
     4) [...] In particular, the degree of T
        is increased by 1 and the ordinal position of that column is
        equal to the new degree of T as specified in the General Rules
        of Subclause 11.4, "<column definition>".

There are quite a few other SQL statements and clauses that depend on the formal specification of ordinal positions within <table expressions>. Some examples:

13.8 <insert statement> 
     (when omitting the `<insert column list>`)
20.2 <direct select statement: multiple rows>
     (when `<sort specification>` contains an `<unsigned integer>`)

Postgres, in particular, is quite standards-compliant, so if you really want to SELECT *, go ahead!

Asarum

I think you are right. Otherwise choosing column position (ADD COLUMN col2 ... AFTER col1) would make no sense. – Affra

@basgys: even if the SQL standards actually specifies that, I would never rely on any order (btw: Postgres does not support ADD COLUMN ... AFTER) – Gillmore