Is the column order predictable when using *?

Question

If I run

SELECT *
FROM my_table;

is the column order predictable?

What are the underlying design aspects of postgres that determine this order?

Answer 1

The order of the columns for the * shorthand in the result is defined by the ordinal position of the columns in the table.

The SQL standard states:

The columns are referenced in the ascending sequence of their ordinal position within T.

^{(T is the table in question)}

And as far as I know Postgres sticks to that - I could however not find an explicit statement for that in the Postgres manual.

For a simple statement like that, the order of the columns is well defined by the

select column_name 
from information_schema.columns
where table_name = '...'
  and schema_name = '...'
order by ordinal_position

or you can look at the table definition e.g. by using \d in psql

BUT: you should never rely on that order in your application code (and select * is considered bad coding style for anything that is not an ad-hoc query)

If you need the columns in a specific order, list them in the order you need them.

---

The above is only valid for simple statement that gets data from a single table. I don't know if there is a rule for the order of columns when using select * with a multi-table query.

Answer 2

The order of columns in a table is stored in the system table pg_attribute (Postgres internals use the terms "relation" and "attribute" for what SQL calls "table" and "column"). It is also used to determine the layout of the data on disk, so it is a fairly fundamental property of the table.

On the face of it, this means you can rely on it, but you will have problems if you assume the same order on different copies of a database. The order is defined entirely based on when columns were added to the table, and as discussed in this previous question, there is no way to reorder it (without effectively rewriting the entire table on disk).

It's also possible that the query will be executed in such a way that columns are not returned in the stored order. For instance, it might use a covering index, and return the data in the order the columns are listed in the index definition (I don't know if Postgres currently does this, but there's no guarantee that it won't.)

You are far better off relying on either a set of columns specified in your SELECT query, so that you can guarantee their order; or using SELECT * but treating the result as a set of unordered key-value pairs.

Personally, I prefer to avoid SELECT * in any production code, because I would prefer to have an SQL query fail with a clear error if a column has been removed or renamed, than have the query succeed but not give the expected data.

Answer 3

You can use DESCRIBE TABLE first to get column order of your table.

Ah you tagged as PostgreSQL, DESCRIBE TABLE is for MySQL. So you should do something:

SELECT
 COLUMN_NAME
FROM
 information_schema.COLUMNS
WHERE
 TABLE_NAME = 'my_table';

Here is the explanations: http://www.postgresqltutorial.com/postgresql-describe-table/