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

Hello,

is there a syntax to add a column not at the last place, but
somewhere inbetween or do I have to dump/restore the table?

For example if I'd like to add last_logout right after last_login:

 \d pref_users;
                Table "public.pref_users"
   Column   |            Type             |   Modifiers
------------+-----------------------------+---------------
 id         | character varying(32)       | not null
 first_name | character varying(32)       |
 last_name  | character varying(32)       |
 female     | boolean                     |
 avatar     | character varying(128)      |
 city       | character varying(32)       |
 lat        | real                        |
 lng        | real                        |
 last_login | timestamp without time zone | default now()
 last_ip    | inet                        |
 medals     | smallint                    | default 0

Thank you
Alex

Alexander Farber, 24.11.2010 08:42:
> is there a syntax to add a column not at the last place

No, because the order of the column is irrelevant (just as there is no order on the rows in a table)
Simply select them in the order you like to have.

Thomas

Hey Alexander,

Ordinal positions of columns can't be set manually by ALTER TABLE.




--
// Dmitriy.

Easier to read... login, logout


On Wed, Nov 24, 2010 at 8:45 AM, Szymon Guz <mabewlun@gmail.com> wrote:
> .... no
> Why do you want to do anything like that?
>

Alexander Farber, 24.11.2010 08:49:
>> Why do you want to do anything like that?
>>
> Easier to read... login, logout

I understand the "easier to read" part.
But what do you mean with "login, logout"?

Thomas

Please note, that in cases when you can't do simple
dump - fix - restore (e.g. in production) you can always create
view(s) with ordinal positions of columns convenient for you.




--
// Dmitriy.

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.

Grzegorz Jaśkiewicz, 24.11.2010 10:37:
> 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.
>
I always try to convince people of this as well, but when they ask me under which circumstances this could happen, I
can'tthink of a proper example. 

Does anybody have an example that would show this?

Regards
Thomas

On Wed, Nov 24, 2010 at 09:37:02AM +0000, Grzegorz Jaśkiewicz wrote:
> 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.

People have been saying that on this list forever, and I agree you
shouldn't *depend* on column order, but why does INSERT syntax allow
you to omit the column names?

INSERT INTO sometable VALUES (1, 2, 3);

If columns inherently don't have an ordering, this shouldn't be
possible because it would make no sense.

Things like this INSERT syntax and the fact that columns are always
returned in the same order when you "SELECT *" or when you check the
table definition with \d condition people to expect being able to
influence the order of columns.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth

It is easy to create view based on SELECT which explicitly specifies
the columns names. IMO it is better to hide tables structures behind views
and work with them, since views are not materialized and it is easy to drop
and recreate or just add another view into the database. With this approach
you can than SELECT * FROM my_view without care of physical ordinal
positions in a tables.




--
// Dmitriy.

On 2010-11-24 10.43, Thomas Kellerer wrote:
> Grzegorz Jaśkiewicz, 24.11.2010 10:37:
>> 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.
>>

SELECT * also makes the code harder to read since you have to lookup the
table definition to see what it'll return.

You'll also be wasting resources to handle the data you'll never use.
Maybe it doesn't matter for one resultset in one call but it quickly
adds up. (This is something ORMs usually are very bad at.)

> I always try to convince people of this as well, but when they ask me
> under which circumstances this could happen, I can't think of a proper
> example.

select * from ta join tb on ta.id=tb.aid;

Add another column to "ta" and you get a different resultset.

In general if you do any changes to your schema you need to go watch out
for code using SELECT * since it easily breaks.
For example if I do something like this in Python it will break if I add
another column:
a, b, c = resultset.next()

Had I used "SELECT a, b, c" it wouldn't. It's a lousy example but not
that uncommon.

>
> Does anybody have an example that would show this?

I still don't have an example of when the internal ordering of a tables
column could change.

Anyhow, "SELECT *" is bad practice leading to error prone code and
wasting resources.

My 2c,
/r

>
> Regards
> Thomas
>
>
>

--
Regards,
Robert "roppert" Gravsjö

* 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)

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

2010/11/24 Florian Weimer <fweimer@bfk.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

* Grzegorz Jaśkiewicz:

> 2010/11/24 Florian Weimer <fweimer@bfk.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)

> 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.

If the database looked at the column names, the result would be
(1, 2), (4, 3), not (1, 2), (3, 4).

--
Florian Weimer                <fweimer@bfk.de>
BFK edv-consulting GmbH       http://www.bfk.de/
Kriegsstraße 100              tel: +49-721-96201-1
D-76133 Karlsruhe             fax: +49-721-96201-99

On 11/24/2010 12:31 PM, Florian Weimer wrote:

* Grzegorz Jaśkiewicz:

2010/11/24 Florian Weimer <fweimer@bfk.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)

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.

If the database looked at the column names, the result would be
(1, 2), (4, 3), not (1, 2), (3, 4).

It seems that UNION does not do what you think it does.
Consider you original example, slightly altered:
# SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c2, 1 AS c1) AS x;
 c1 | c2
----+----
  1 |  2
  2 |  1


If you change a column name in the first SELECT in the UNION:
# SELECT c1,c2 FROM (SELECT 1 AS c3, 2 AS c2 UNION SELECT 2 AS c2, 1 AS c1) AS x;
ERROR:  column "c1" does not exist
LINE 1: SELECT c1,c2 FROM (SELECT 1 AS c3, 2 AS c2 UNION SELECT 2 AS...
               ^

But if you change the column names in the second SELECT in the UNION this is ignored:
# SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4) AS x;
 c1 | c2
----+----
  1 |  2
  2 |  1
Apparently, in a UNION the column names are derived from the first statement only.

Postgresql 8.4.5

/Fredric

    Fredric Fredricson wrote:

> But if you change the column names in the second SELECT in the UNION this is
> ignored:
> # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4)
> AS x;
>  c1 | c2
> ----+----
>   1 |  2
>   2 |  1
> Apparently, in a UNION the column names are derived from the first statement
> only.

The example upthread demonstrates that in certain contexts, column positions
are relevant whereas column names are not. The modified query you show here
doesn't lead to any different conclusion.

The allegation that row.* doesn't come with a deterministic column order
remains pretty much unsubstantiated at this point.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Even if an example doesn't exist, you can at least imagine a scenario where an improvement to postgresql is made such that the column order is decided internally, rather than by table definition.  If the warning isn't given now, that improvement won't be possible.  So I read that as "Don't rely on the table order, it's not part of the interface/contract and we're going to change it if we want to".

Derrick

On 11/24/2010 03:32 AM, Peter Bex wrote:
> On Wed, Nov 24, 2010 at 09:37:02AM +0000, Grzegorz Jaśkiewicz wrote:
>> 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.
>
> People have been saying that on this list forever, and I agree you
> shouldn't *depend* on column order, but why does INSERT syntax allow
> you to omit the column names?
>
> INSERT INTO sometable VALUES (1, 2, 3);
>
> If columns inherently don't have an ordering, this shouldn't be
> possible because it would make no sense.

Looking in an old copy of a draft 2003 sql standard,

sec-7.12 (p 341)
 which describes queries, Syntax Rules, para 3 describes
the * select list and
3b says,

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

This is the first time I've ever looked at the 1000+ page spec and I
haven't tried to chase down all the definitions so I don't pretend to
be authoritative but it sure sounds to me (as your observation above
implies) that SQL *does* have an explicit notion of column order.

Perhaps those claiming that no order is guaranteed by SELECT * could
provide some support for that from the SQL standards?

Stuart McGraw <smcg2297@frii.com> writes:
> This is the first time I've ever looked at the 1000+ page spec and I
> haven't tried to chase down all the definitions so I don't pretend to
> be authoritative but it sure sounds to me (as your observation above
> implies) that SQL *does* have an explicit notion of column order.

Yes, it does.  If it did not, they would never have provided the option
of omitting the target-column-name list from INSERT.

As for the original issue, the ability to add a column somewhere other
than at the end is on the TODO list, but it's been there for quite some
time so don't hold your breath waiting for it to get done.  There are
several discussions in the pgsql-hackers archives about why it isn't
a simple thing to do.

In the meantime, if the OP wants it bad enough he can do something
involving CREATE TABLE ... AS SELECT ... to build a new table with
the columns in the desired order, and then rename it to replace the
old table.

            regards, tom lane

On Nov 24, 2010, at 9:42 AM, Derrick Rice wrote:
>
> Even if an example doesn't exist, you can at least imagine a scenario where an improvement to postgresql is made such
thatthe column order is decided internally, rather than by table definition. 

Not when SQL compatibility requires that the order be maintained.


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On 2010-11-24, Daniel Verite <daniel@manitou-mail.org> wrote:
>     Fredric Fredricson wrote:
>
>> But if you change the column names in the second SELECT in the UNION this is
>> ignored:
>> # SELECT c1,c2 FROM (SELECT 1 AS c1, 2 AS c2 UNION SELECT 2 AS c3, 1 AS c4)
>> AS x;
>>  c1 | c2
>> ----+----
>>   1 |  2
>>   2 |  1
>> Apparently, in a UNION the column names are derived from the first statement
>> only.
>
> The example upthread demonstrates that in certain contexts, column positions
> are relevant whereas column names are not. The modified query you show here
> doesn't lead to any different conclusion.
>
> The allegation that row.* doesn't come with a deterministic column order
> remains pretty much unsubstantiated at this point.

It's deterministic, just subject to change (eg if the table is
re-created with a different order, or if a column is dropped and
re-added)

I try to always use column and table names when dealing with tables
other people could mess with.

If dealing with a temp table I sometimes take shortcuts as the source
is all in one place, so anyone messing with it will hopefully be aware
of the consequences of their actions.

--
⚂⚃ 100% natural