A view needs at least one column whereas a table doesn't...

Started by Gurjeet Singhabout 19 years ago2 messagesgeneral
Jump to latest
#1Gurjeet Singh
singh.gurjeet@gmail.com

The first SQL command throws an error whereas the second one runs fine:

edb=# create schema s1 authorization u1
edb-# create table t1() create view v1 as select * from t1;
ERROR: view must have at least one column
edb=#
edb=# create schema s1 authorization u1
edb-# create table t1( c1 int ) create view v1 as select * from t1;
CREATE SCHEMA
edb=#

I know the ERROR seems obvious, but then if we allow zero-column tables,
then why not zero-column views?

Or should the question be, why do we allow zero-column tables? (is
inheritance the only reason?)

Can someone provide some explanation for this behaviour?

Best regards,

--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
17°29'34.37"N 78°30'59.76"E

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Gurjeet Singh (#1)
Re: A view needs at least one column whereas a table doesn't...

"Gurjeet Singh" <singh.gurjeet@gmail.com> writes:

I know the ERROR seems obvious, but then if we allow zero-column tables,
then why not zero-column views?
Or should the question be, why do we allow zero-column tables?

Neither one is legal per the SQL spec. We allow zero-column tables
because otherwise there are unpleasant corner cases for ALTER TABLE DROP
COLUMN, ie, you couldn't drop the last remaining column before adding
another. But since we don't have ALTER VIEW DROP COLUMN, there's no
comparable argument for violating the spec for views.

regards, tom lane