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@xxxxxxxxxxx
