Re: alter table add column - specify where the column will go?

2010/11/24 Florian Weimer <fweimer(at)bfk(dot)de>:
> * Grzegorz Jaśkiewicz:
>
>> just never use SELECT *, but always call columns by names. You'll
>> avoid having to depend on the order of columns, which is never
>> guaranteed, even if the table on disk is one order, the return columns
>> could be in some other.
>
> This can't be true because several SQL features rely on deterministic
> column order.  Here's an example:
>
> SELECT 1 AS a, 2 AS b UNION SELECT 3 AS b, 4 AS a;
>
>  a | b
> ---+---
>  1 | 2
>  3 | 4
> (2 rows)
>

Read again what I wrote please.

Yes, most DBs do a good job to keep it consistent, but they don't have
to. So unless you specify column names explicitly (like you did in the
example above), there's no guarantees.

Most people struggle with long table names in joins and stuff, for
instance: SELECT foo.one, bar.two FROM foo join ... Because they
forget about the aliases, like SELECT a.one, b.two FROM foo a JOIN bar
b ..

--
GJ