Hello,
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.
Example:
postgres=# create temporary table t
(key varchar primary key, plates varchar);
postgres=# insert into t values ('000000','AA888BB CC777DD GG333JJ'),
('111111','ZZ888KK');
INSERT 0 2
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,
Giuseppe
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
