Re: count(*) of zero rows returns 1

On Mon, Jan 14, 2013 at 11:03 PM, Alvaro Herrera
<alvherre(at)2ndquadrant(dot)com>wrote:

> Gurjeet Singh escribió:
>
> > Interesting to note that SELECT * FROM table_with_zero_cols does not
> > complain of anything.
> >
> > postgres=# select * from test1;
> > --
> > (0 rows)
> >
> > This I believe result of the fact that we allow user to drop all columns
> of
> > a table.
> >
> > On a side note, Postgres allows me to do this (which I don't think is a
> bug
> > or useless): I inserted some rows into a table, and then dropped the
> > columns. The resulting table has no columns, but live rows.
> >
> > postgres=# select * from test_0_col_table ;
> > --
> > (200000 rows)
>
> Yeah.
>
> alvherre=# create table foo ();
> CREATE TABLE
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo default values;
> INSERT 0 1
> alvherre=# insert into foo select * from foo;
> INSERT 0 4
> alvherre=# insert into foo select * from foo;
> INSERT 0 8
> alvherre=# insert into foo select * from foo;
> INSERT 0 16
> alvherre=# insert into foo select * from foo;
> INSERT 0 32
> alvherre=# insert into foo select * from foo;
> INSERT 0 64
> alvherre=# select count(*) from foo;
> count
> -------
> 128
> (1 fila)
>
> alvherre=# select * from foo;
> --
> (128 filas)
>
> If you examine the ctid system column you can even see that those empty
> rows consume some storage space.

I was trying to build a case and propose that we issue a TRUNCATE on the
table after the last column is dropped. But then realized that the rows may
have become invisible, but they can be brought back to visibility by simply
adding a new column. These rows with get the new column's DEFAULT value
(NULL by default), and then the result of a SELECT * will show all the rows
again.

--
Gurjeet Singh

http://gurjeet.singh.im/