Postgres Pro
Downloads
Home   >   mailing lists

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

Allowing empty target list in SELECT (1b4f7f93b4693858cb983af3cd557f6097dab67b)

From
Amit Langote
Date:

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

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

From
Amit Langote
Date:
On Fri, May 2, 2014 at 12:57 PM, Amit Langote <amitlangote09@gmail.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

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

From
Rajeev rastogi
Date:
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
storedas 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
ancestorin class hierarchy.
 


Thanks and Regards,
Kumar Rajeev Rastogi

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

From
David Rowley
Date:

On Fri, May 2, 2014 at 3:57 PM, Amit Langote <amitlangote09@gmail.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

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

From
Albe Laurenz
Date:
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

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

From
Amit Langote
Date:
On Fri, May 2, 2014 at 4:14 PM, David Rowley <dgrowleyml@gmail.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