Re: SELECT INTO question

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(dot)klaver(at)aklaver(dot)com