Re: SELECT INTO question - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: SELECT INTO question |
Date | |
Msg-id | c781b965-5e01-8072-377a-40b24001f64c@aklaver.com Whole thread Raw |
In response to | SELECT INTO question (Kevin Brannen <KBrannen@efji.com>) |
Responses |
Re: SELECT INTO question
|
List | pgsql-general |
On 7/25/19 12:23 PM, Kevin Brannen wrote: > Hi, > > We’re trying to understand what happened with a SELECT INTO. The problem > can be see with this example: > > # create table t1 (id int, v int); > > CREATE TABLE > > # insert into t1 (select x, x from generate_series(1, 5) as g(x)); > > INSERT 0 5 > > # select * from t1; > > id | v > > ----+---- > > 1 | 1 > > 2 | 2 > > 3 | 3 > > 4 | 4 > > 5 | 5 > > (5 rows) > > nms=# select into t2 from t1; > > SELECT 5 > > # select * from t2; > > -- > > (5 rows) > > # select * into t3 from t1; > > SELECT 5 > > # select * from t3; > > id | v > > ----+---- > > 1 | 1 > > 2 | 2 > > 3 | 3 > > 4 | 4 > > 5 | 5 > > (5 rows) > > As you can see on the first select into, the result in t2 is … missing, > no “data” at all, unlike t3 which was the expected answer. Upon closer > inspection, it was realized that the “expression” in the statement was > left out (oops!), but instead of getting a syntax error, it worked. > > So why did it work and why was nothing stored? > > The only answer I’ve been able to come up with is that the expression > was evaluated as a “null expression” for each row, so it gave us 5 null > rows. A small part of my brain understands that, but most of my brain > goes “what?!” > > I’ve noticed that I can also do: > > # select from t1; > > -- > > (5 rows) > > That also doesn’t make sense and yet it does in a weird way. I suspect > the answer revolves around some corner case in the SQL Standard. > > So, what’s going on here? https://www.postgresql.org/docs/11/sql-select.html Compatibility "Omitted FROM Clauses PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the results of simple expressions: SELECT 2+2; ?column? ---------- 4 Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. ... Empty SELECT Lists The list of output expressions after SELECT can be empty, producing a zero-column result table. This is not valid syntax according to the SQL standard. PostgreSQL allows it to be consistent with allowing zero-column tables. However, an empty list is not allowed when DISTINCT is used. " So: test=# \d t2 Table "public.t2" Column | Type | Collation | Nullable | Default --------+------+-----------+----------+--------- > > Thanks, > > Kevin > -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: