Re: SELECT INTO question - Mailing list pgsql-general

From Tom Lane
Subject Re: SELECT INTO question
Date
Msg-id 31985.1564084046@sss.pgh.pa.us
Whole thread Raw
In response to Re: SELECT INTO question  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses RE: SELECT INTO question
List pgsql-general
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



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: postgres 9.5 DB corruption
Next
From: Kevin Brannen
Date:
Subject: RE: SELECT INTO question