Re: relation vs table...

On Thu, 2003-10-09 at 13:50, Terrence Brannon wrote:
> I don't know what Postgres considers a relation and had no intention of
> creating one when piping my schema to it... I always DROP TABLE before
> CREATE TABLE, so here are the ERRORS emitted when building the database:
>
> 3:ERROR: table "country" does not exist
> 6:ERROR: table "customer" does not exist
> 11:ERROR: table "product" does not exist
> 15:ERROR: table "license" does not exist
> 19:ERROR: table "enduser" does not exist
> 24:ERROR: table "orders" does not exist
> 29:ERROR: table "enduser_license" does not exist
> 33:ERROR: table "item" does not exist

All these are errors when you DROP a table that does not yet exist.

> 37:ERROR: Relation "product_version" does not exist

This is your attempt to have a foreign key reference to product_version
from table "item", when product_version does not yet exist.

> 38:ERROR: table "product_version" does not exist
> 43:ERROR: table "ordered_item" does not exist

and again, you are dropping these tables before they exist.

> but each and every one of these was created via CREATE TABLE

but only _after_ you try to drop them (and your attempt to create "item"
will have failed because of the bad foreign key reference).

> I don't understand why Postgres thinks I am creating a relation _and_ I
> don't know what it considers a relation to be. Create stmts follow:

A table is a relation but a relation is not necessarily a table, A view
is a relation. However, the error message for your bad foreign key is
misleading because a foreign key must reference a table, not a relation:

junk=# create view aaa as select * from xxx union select * from zzz;
CREATE VIEW
junk=# create table bbb (id serial, aid integer references aaa(id));
NOTICE: CREATE TABLE will create implicit sequence "bbb_id_seq" for
"serial" column "bbb.id"
ERROR: referenced relation "aaa" is not a table

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Every good gift and every perfect gift is from above,
and cometh down from the Father of lights, with whom
is no variableness, neither shadow of turning."
James 1:17