sql - select * from (select a,b,c ...) guaranteed order of result columns? -

i had comment in code review:

it's better enumerate fields explicitly. "select *" doesn't guarantee order

is true in case query select * (select a,b,c ...)? can't imagine database engine re-order columns in result, imagination more logical database engines.

the advice against select * when you're querying tables directly.

in databases it's possible insert new column partway through table, such table t (a, b) becomes table t (a, c, b). postgresql not (yet) support this, can still append columns, , can drop columns anywhere, can still t (a, c) if add c , drop b.

this why it's considered bad practice use * in production queries, if application relies on ordinal column position read results. really, applies situations not specify fields elsewhere in query.

in case in subquery. * safe , imo quite acceptable in usage.

this:

select * (select a,b,c ... t) 

is fine.

this:

select * t 

or

select * (select * t) 

are problematic, because leave column-order undefined application. that's problem if application assumes column order without checking query metadata.

personally prefer qualify columns of time, there sure times when * readable option.

it's also, imo, quite fine use select * when client application reads columns name, not ordinal. if app never cares if c 2nd column or 3rd because uses result metadata build row dictionary (like perl's dbi or python's psycopg2 can do) there's no real reason not use *.

there can performance costs select * when need subset of columns, though. missed opportunities use index-only scans, unnecessary fetching of out-of-line toasted data, , bandwidth wasted unwanted values, among other things. of time it's still not great idea.