Skip site navigation (1) Skip section navigation (2)

Using relations in the SELECT part

From: Giuseppe Sacco <giuseppe(at)eppesuigoccas(dot)homedns(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Using relations in the SELECT part
Date: 2017-03-29 07:25:08
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
I am writing to this list since I wrote a query that I cannot really
understand. So, thanks to anyone who will light my darkness :-)

I have a table with two columns, the first one is a key, the second one
is a list of car plates. What I need to extract is a result set that
contains two columns, the first one should always be the key, the
second one only one plate. If the record contains many plates, the
result set should contains a row for each plate.


postgres=# create temporary table t
 (key varchar primary key, plates varchar);
postgres=# insert into t values ('000000','AA888BB CC777DD GG333JJ'),
postgres=# select * from t;
  key   |         plates          
 000000 | AA888BB CC777DD GG333JJ
 111111 | ZZ888KK

This is what I would like to extract from t:
  key   |         plate
 000000 | AA888BB
 000000 | CC777DD
 000000 | GG333JJ
 111111 | ZZ888KK

the solution I found is:

postgres=# select key,
 unnest(regexp_split_to_array(plates, E'\\s+')) AS plate from t;
  key   |  plate  
 000000 | AA888BB
 000000 | CC777DD
 000000 | GG333JJ
 111111 | ZZ888KK

What did I write? The first operation is to convert the original space
separated list into an array, then convert that array to a relation
that contains many records.

Early questions:

1. why may I put in the SELECT part (instead of the FROM) a relation?
When I studied SQL, I was told to put all relations in FROM, and put in
the SELECT part only the colmns or expressions with columns for
formatting the output.

2. why postgresql create a cartesian product using a first element (a
single columns "key") and a second element (a relation "plate")?

3. how postgresql define the second element? it is not "static" since
it depends fomr the first element: it depends on the current record.
For every "key", there a different "plate" result set.

Furthermore, let's assume postgres does a cartesian product, if I add a
new relation as third element, does it create 4x3 product? Let's see:

postgres=# select key,
 unnest(regexp_split_to_array(plates, E'\\s+')) AS plate1,
 unnest(regexp_split_to_array(plates, E'\\s+')) AS plate2 from t;
  key   | plate1  | plate2  
 000000 | AA888BB | AA888BB
 000000 | CC777DD | CC777DD
 000000 | GG333JJ | GG333JJ
 111111 | ZZ888KK | ZZ888KK

4. what happened? May this be somewhta related to IMMUTABLE function?
Is unnest an immutable function? And, in any case, why this this is not
a cartesia product?

Let's try in a different way, with a different array:

postgres=# select key,
 unnest(regexp_split_to_array(plates, E'\\s+')) AS plate1, 
 unnest('{1,2}'::int[]) AS array2 from t;
  key   | plate1  | array2 
 000000 | AA888BB |      1
 000000 | CC777DD |      2
 000000 | GG333JJ |      1
 000000 | AA888BB |      2
 000000 | CC777DD |      1
 000000 | GG333JJ |      2
 111111 | ZZ888KK |      1
 111111 | ZZ888KK |      2

this time it is a cartesian product. Why postgresql acts differently?

Thank you,


pgsql-general by date

Next:From: Karsten HilbertDate: 2017-03-29 09:37:15
Subject: Re: Postgres Permissions Article
Previous:From: Adrian KlaverDate: 2017-03-28 20:27:03
Subject: Re: Equivalent function not found for ERROR_PROCEDURE(),ERROR_LINE(),ERROR_MESSAGE().

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group