RE: SELECT INTO question

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