pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
unnest in SELECT 4+ messages / 4 participants [nested] [flat]
* unnest in SELECT @ 2011-05-20 19:13 Karl Koster <kkoster@kdresources.com> 0 siblings, 1 reply; 4+ messages in thread From: Karl Koster @ 2011-05-20 19:13 UTC (permalink / raw) To: pgsql-sql I have a couple of questions regarding unnest. 1) If I issue a select statement "select unnest(vector1) as v from some_table", I cannot seem to use the column alias v in a WHERE or HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this the way it is supposed to work? 2) If I issue a select statement "select unnest(vector1) as v1, unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and vector2 has a length of 4, the result set will have 12 rows with the data of vector1 repeating 4 times and vector2 repeating 3 times. Shouldn't the content of the shorter array(s) simply be return null in it's respective column and the result set be the size of the longest array? Thanks, Karl ^ permalink raw reply [nested|flat] 4+ messages in thread
* unnest in SELECT @ 2011-05-20 19:45 Karl Koster <klkoster@optonline.net> 0 siblings, 1 reply; 4+ messages in thread From: Karl Koster @ 2011-05-20 19:45 UTC (permalink / raw) To: pgsql-sql I have a couple of questions regarding unnest. 1) If I issue a select statement "select unnest(vector1) as v from some_table", I cannot seem to use the column alias v in a WHERE or HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this the way it is supposed to work? 2) If I issue a select statement "select unnest(vector1) as v1, unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and vector2 has a length of 4, the result set will have 12 rows with the data of vector1 repeating 4 times and vector2 repeating 3 times. Shouldn't the content of the shorter array(s) simply be return null in it's respective column and the result set be the size of the longest array? Thanks, Karl ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unnest in SELECT @ 2011-05-21 06:58 Andreas Kretschmer <akretschmer@spamfence.net> parent: Karl Koster <klkoster@optonline.net> 0 siblings, 0 replies; 4+ messages in thread From: Andreas Kretschmer @ 2011-05-21 06:58 UTC (permalink / raw) To: pgsql-sql Karl Koster <klkoster@optonline.net> wrote: > I have a couple of questions regarding unnest. > > 1) If I issue a select statement "select unnest(vector1) as v from > some_table", I cannot seem to use the column alias v in a WHERE or > HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this > the way it is supposed to work? Yes. It's the same as: test=# select 1 as a where a = 2; ERROR: column "a" does not exist LINE 1: select 1 as a where a = 2; in other words: the column a is unknown for the where-condition at this time. > > 2) If I issue a select statement "select unnest(vector1) as v1, > unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and > vector2 has a length of 4, the result set will have 12 rows with the > data of vector1 repeating 4 times and vector2 repeating 3 times. Yeah, it's a cross-join. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: unnest in SELECT @ 2011-05-22 03:23 Craig Ringer <craig@postnewspapers.com.au> parent: Karl Koster <kkoster@kdresources.com> 0 siblings, 0 replies; 4+ messages in thread From: Craig Ringer @ 2011-05-22 03:23 UTC (permalink / raw) To: Karl Koster <kkoster@kdresources.com>; +Cc: pgsql-sql On 05/21/2011 03:13 AM, Karl Koster wrote: > I have a couple of questions regarding unnest. > > 1) If I issue a select statement "select unnest(vector1) as v from > some_table", I cannot seem to use the column alias v in a WHERE or > HAVING clause. I can use it in an ORDER BY or GROUP by clause. Is this > the way it is supposed to work? Yes, and it's what the SQL standard requires. Otherwise, how would this query work? SELECT a/b FROM sometable WHERE b <> 0; ? The SELECT list has to be processed only once the database has already decided which rows it applies to and how. Use unnest in a FROM clause, eg: SELECT v1.* FROM unnest(vector) ... This may require a join and/or subquery to obtain 'vector'. > 2) If I issue a select statement "select unnest(vector1) as v1, > unnest(vector2) as v2 from some_table" and vector1 has a length of 3 and > vector2 has a length of 4, the result set will have 12 rows with the > data of vector1 repeating 4 times and vector2 repeating 3 times. > Shouldn't the content of the shorter array(s) simply be return null in > it's respective column and the result set be the size of the longest array? unnest is a set-returning function, and it doesn't really make that much sense to have them in the SELECT list anyway. Few databases support it, and PostgreSQL's behavior is a historical quirk that I think most people here hope will go quietly away at some point. Use unnest in a FROM clause. -- Craig Ringer ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-05-22 03:23 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-05-20 19:13 unnest in SELECT Karl Koster <kkoster@kdresources.com> 2011-05-22 03:23 ` Craig Ringer <craig@postnewspapers.com.au> 2011-05-20 19:45 unnest in SELECT Karl Koster <klkoster@optonline.net> 2011-05-21 06:58 ` Andreas Kretschmer <akretschmer@spamfence.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox