Re: Allowing empty target list in SELECT (1b4f7f93b4693858cb983af3cd557f6097dab67b)

Hi,

I
​s the following behavior perceived fix-worthy?

-- note the
​'​
​1's
in the output
​s
​

​po​
stgres=# CREATE TABLE test AS SELECT;
SELECT 1

postgres=# insert into test select;
INSERT 0 1​

​My guess why ​this happens is because changes made in the commit in
$SUBJECT only pertain to fixing syntax errors and nothing else.

--
Amit

On Fri, May 2, 2014 at 12:57 PM, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> I
> s the following behavior perceived fix-worthy?
>
>
> -- note the
> '
> 1's
> in the output
> s
>
> po
> stgres=# CREATE TABLE test AS SELECT;
> SELECT 1
>
> postgres=# insert into test select;
> INSERT 0 1
>

Or maybe, it just means 1 'null' row/record and not no row at all?

--
Amit

On 02 May 2014 10:00, Amit Longote Wrote:

> > I
> > s the following behavior perceived fix-worthy?
> >
> >
> > -- note the
> > '
> > 1's
> > in the output
> > s
> >
> > po
> > stgres=# CREATE TABLE test AS SELECT;
> > SELECT 1
> >
> > postgres=# insert into test select;
> > INSERT 0 1
> >
>
> Or maybe, it just means 1 'null' row/record and not no row at all?

It just creates an item pointer and corresponding to that heap tuple header (without data or bitmask for NULL) gets stored as part of this insertion.
So though it does not insert anything (not even NULL) but still it reserve one row position.
So while SELECT, it will not display anything but it will show actual number of rows.

Even below syntax is also allowed:

CREATE TABLE no_column_table();

IMO, this might be useful for dynamic use of table (later column might be added using 'ALTER') or to use as abstract ancestor in class hierarchy.

Thanks and Regards,
Kumar Rajeev Rastogi

On Fri, May 2, 2014 at 3:57 PM, Amit Langote <amitlangote09(at)gmail(dot)com>wrote:

>
> Hi,
>
> I
> ​s the following behavior perceived fix-worthy?
>
>
> -- note the
> ​'​
> ​1's
> in the output
> ​s
> ​
>
> ​po​
> stgres=# CREATE TABLE test AS SELECT;
> SELECT 1
>
> postgres=# insert into test select;
> INSERT 0 1​
>
> ​My guess why ​this happens is because changes made in the commit in
> $SUBJECT only pertain to fixing syntax errors and nothing else.
>
>
Are you proposing that this does not insert a 0 column row?

I don't find the current behaviour wrong. If it didn't insert the row then
the query in the following would return 0 rows.

begin work;
create table nocols ();
insert into nocols select;
insert into nocols select;

create table test (value int);
insert into test values(1);

select * from nocols cross join test; -- give 2 rows with the value 1
rollback;

Why should the above results be any different than if I created the nocols
table with a column then dropped it?
Certainly removing all of the records on the drop of the last column would
be wrong.

Regards

David Rowley

--
> Amit
>

Amit Langote wrote:
>> Is the following behavior perceived fix-worthy?
>>
>>
>> -- note the '1's in the outputs
>>
>> postgres=# CREATE TABLE test AS SELECT;
>> SELECT 1
>>
>> postgres=# insert into test select;
>> INSERT 0 1
>
> Or maybe, it just means 1 'null' row/record and not no row at all?

Right, I'd say you end up with a table with two 0-tuples.

Maybe odd, but it shouldn't be a problem.

Yours,
Laurenz Albe

On Fri, May 2, 2014 at 4:14 PM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
>
> Why should the above results be any different than if I created the nocols
> table with a column then dropped it?
> Certainly removing all of the records on the drop of the last column would
> be wrong.
>

I see, dropping the only column in a table does exhibit a similar behavior.

--
Amit