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.Elphick@lfix.co.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