Re: SELECT INTO question - Mailing 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