Re: [GENERAL] alter table add column - specify where the column will go?
On 2010-11-24, Daniel Verite dan...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
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. 2010/11/24 Dmitriy Igrishin dmit...@gmail.com Hey Alexander, Ordinal positions of columns can't be set manually by ALTER TABLE. 2010/11/24 Alexander Farber alexander.far...@gmail.com 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy. -- // Dmitriy.
Re: [GENERAL] alter table add column - specify where the column will go?
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
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't think of a proper example. Does anybody have an example that would show this? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
On Wed, Nov 24, 2010 at 09:37:02AM +, 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
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. 2010/11/24 Grzegorz Jaśkiewicz gryz...@gmail.com 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. -- // Dmitriy.
Re: [GENERAL] alter table add column - specify where the column will go?
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ö -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
* 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 Weimerfwei...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
2010/11/24 Florian Weimer fwei...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
* Grzegorz Jaśkiewicz: 2010/11/24 Florian Weimer fwei...@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 Weimerfwei...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
On 11/24/2010 12:31 PM, Florian Weimer wrote: * Grzegorz Jaśkiewicz: 2010/11/24 Florian Weimerfwei...@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 attachment: Fredric_Fredricson.vcf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
On Wed, Nov 24, 2010 at 4:43 AM, Thomas Kellerer spam_ea...@gmx.net 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. 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. Does anybody have an example that would show this? Regards Thomas 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
Re: [GENERAL] alter table add column - specify where the column will go?
On 11/24/2010 03:32 AM, Peter Bex wrote: On Wed, Nov 24, 2010 at 09:37:02AM +, 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? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
Stuart McGraw smcg2...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
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 that the column order is decided internally, rather than by table definition. Not when SQL compatibility requires that the order be maintained. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
On 24 November 2010 08:42, Alexander Farber alexander.far...@gmail.comwrote: 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 no Why do you want to do anything like that? regards Szymon Guz
Re: [GENERAL] alter table add column - specify where the column will go?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table add column - specify where the column will go?
Hey Alexander, Ordinal positions of columns can't be set manually by ALTER TABLE. 2010/11/24 Alexander Farber alexander.far...@gmail.com 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- // Dmitriy.
Re: [GENERAL] alter table add column - specify where the column will go?
Easier to read... login, logout On Wed, Nov 24, 2010 at 8:45 AM, Szymon Guz mabew...@gmail.com wrote: no Why do you want to do anything like that? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
