Postgres Pro
Downloads
Home   >   mailing lists

Thread: SELECT INTO question

SELECT INTO question

From
Kevin Brannen
Date:

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?

 

Thanks,

Kevin

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

Re: SELECT INTO question

From
Adrian Klaver
Date:
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

Re: SELECT INTO question

From
Tom Lane
Date:
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 7/25/19 12:23 PM, Kevin Brannen wrote:
>> We're trying to understand what happened with a SELECT INTO. The problem 
>> can be see with this example:
>> 
>> nms=# select into t2 from t1;
>> SELECT 5
>> # select * from t2;
>> --
>> (5 rows)

> 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.

Right, you selected no columns from t1, so t2 has no columns (and yet
five rows).  Worth noting here is that psql is a bit squirrely about
displaying zero-column results --- it drops the column-names header
line, and it doesn't emit a blank-line-per-row as one might expect.
Perhaps somebody ought to fix that, but it's such a corner case that
no one has bothered yet.

            regards, tom lane

RE: SELECT INTO question

From
Kevin Brannen
Date:

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, July 25, 2019 2:47 PM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Kevin Brannen <KBrannen@efji.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: SELECT INTO question

 

Adrian Klaver <adrian.klaver@aklaver.com> writes:

> On 7/25/19 12:23 PM, Kevin Brannen wrote:

>> We're trying to understand what happened with a SELECT INTO. The

>> problem can be see with this example:

>>

>> nms=# select into t2 from t1;

>> SELECT 5

>> # select * from t2;

>> --

>> (5 rows)

 

> 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.

 

Right, you selected no columns from t1, so t2 has no columns (and yet five rows).  Worth noting here is that psql is a bit squirrely about displaying zero-column results --- it drops the column-names header line, and it doesn't emit a blank-line-per-row as one might expect.

Perhaps somebody ought to fix that, but it's such a corner case that no one has bothered yet.

 

 

 

Hmm, I don't particularly like that answer as I'd have preferred a "syntax error", but I do understand it.

 

Thanks for the answer, Adrian; and thanks for the expansion, Tom.

 

Kevin

 

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.