Re: [GENERAL] inconsistency in aliasing
as far as I know, this bit (statement evaluation) wasn't implemented then. It only got there in 8.4, so you can have even subselects evaluated. So it isn't a bug, it just wasn't implemented to work that way back than, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] inconsistency in aliasing
Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, -- http://www.critikart.net -- 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] inconsistency in aliasing
On Wednesday 14 January 2009 11:46:11 Louis-David Mitterrand wrote: Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, Looks and smells like a bug. -- Fahrbahn ist ein graues Band weisse Streifen, grüner Rand -- 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] inconsistency in aliasing
In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] inconsistency in aliasing
I don't know, if this is an inconsistence or a bug, but here a possible workaround: select current_timestamp::abstime::int4 as score, current_timestamp::abstime::int4 + 1 as score + 1 order by score + 1; Ludwig This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ...
Re: [GENERAL] inconsistency in aliasing
what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? dbalinglung DataproSoft Developer - Original Message - From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org To: pgsql-general@postgresql.org Sent: Wednesday, January 14, 2009 5:46 PM Subject: [GENERAL] inconsistency in aliasing Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, -- http://www.critikart.net
Re: [GENERAL] inconsistency in aliasing
On Wed, Jan 14, 2009 at 06:31:55PM +0700, dbalinglung wrote: what for of the syntax command +1 on order by ? maybe just wrong to given result about the error query on order by, it's BUG ? *PARSE ERROR* -- 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] inconsistency in aliasing
In response to Louis-David Mitterrand : Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias Ouch, my fault, muddled with WHERE Thx, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 3:46 AM, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... A number in an order by refers to a column. select a, b, c/d as f from table order by 3; will order by the third field in the select list. I'm guessing the + 1 is trying to add col 1 to a field it can't find. doubt it's a bug, more like a quirk. -- 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] inconsistency in aliasing
Reg Me Please wrote: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Thanks, Looks and smells like a bug. Read http://www.postgresql.org/docs/8.3/static/queries-order.html quote The sort expression(s) can be any expression that would be valid in the query's select list /quote score+1 is not valid in the query's select list (as well as score anyway) quote For backwards compatibility with the SQL92 version of the standard, a sort_expression can instead be the name or number of an output column /quote so that's why score alone works in the order by, despite it not being valid in the select list. quote Note that an output column name has to stand alone, it's not allowed as part of an expression /quote Which looks like the very issue discussed here, and it just behaves as documented. 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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net -- 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] inconsistency in aliasing
--- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net you can't use operator in the group by, try this select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 -- 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] inconsistency in aliasing
On Wed, Jan 14, 2009 at 05:53:57AM -0800, Lennin Caro wrote: --- On Wed, 1/14/09, Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org wrote: From: Louis-David Mitterrand vindex+lists-pgsql-gene...@apartia.org Subject: Re: [GENERAL] inconsistency in aliasing To: pgsql-general@postgresql.org Date: Wednesday, January 14, 2009, 11:27 AM On Wed, Jan 14, 2009 at 12:06:47PM +0100, A. Kretschmer wrote: In response to Louis-David Mitterrand : Hi, This works: critik=# select current_timestamp::abstime::int4 as score order by score; This doesn't: critik=# select current_timestamp::abstime::int4 as score order by score + 1; ERROR: column score does not exist LINE 1: ...urrent_timestamp::abstime::int4 as score order by score + 1 ... Any idea ? Yes, you can't use the alias in the ORDER BY. Use the real column-name. select current_timestamp::abstime::int4 as score order by current_timestamp::abstime::int4; Did you try select current_timestamp::abstime::int4 as score order by score; ? This seems to be an order by alias -- http://www.critikart.net you can't use operator in the group by, try this Really? select current_timestamp::abstime::int4 as score order by 1 + 1 ; score 1231941662 (1 row) -- http://www.critikart.net -- 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] inconsistency in aliasing
Lennin Caro wrote: select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 That additional score2 is not needed in the select output. This works just fine: = select score from (select current_timestamp::abstime::int4 as score) subsel order by score+1; The point is that the subselect makes score available as a valid expression to the upper select. 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] inconsistency in aliasing
2009/1/14 Daniel Verite dan...@manitou-mail.org: Lennin Caro wrote: select score,score+1 as score2 from ( select current_timestamp::abstime::int4 as score) order by score2 That additional score2 is not needed in the select output. This works just fine: = select score from (select current_timestamp::abstime::int4 as score) subsel order by score+1; I'm been watching that the string name of order by with operator just work fine if the column name is in the select clause. But if you use alias this not work... I'm don't believe is a bug, is a string name question. You can't add 1 to an alias, but you can add 1 to a field... but the results is the same The point is that the subselect makes score available as a valid expression to the upper select. 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 -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
