Thread: serial and namespace

Hi,

I created a schema *inoue* and tried the following.
 # create table inoue.t1 (id serial primary key, dt text); NOTICE:  CREATE TABLE will create implicit sequence
't1_id_seq'for SERIAL column 't1.id' NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 't1_pkey' for table
't1'CREATE # insert into inoue.t1 (dt) values ('abc'); ERROR:  Relation "t1_id_seq" does not exist
 

regards,
Hiroshi Inoue

Hiroshi Inoue <Inoue@tpf.co.jp> writes:
>   # create table inoue.t1 (id serial primary key, dt text);
>   NOTICE:  CREATE TABLE will create implicit sequence 't1_id_seq'
>   for SERIAL column 't1.id'
>   NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>   't1_pkey' for table 't1'
>   CREATE
>   # insert into inoue.t1 (dt) values ('abc');
>   ERROR:  Relation "t1_id_seq" does not exist

Okay, I fixed SERIAL column creation so that you get a default like
this:

regression=# \d t1                               Table "t1"Column |  Type   |                       Modifiers
--------+---------+-------------------------------------------------------id     | integer | not null default
nextval('"inoue"."t1_id_seq"'::text)dt    | text    |
 
Indexes: t1_pkey primary key btree (id)

I'm not entirely thrilled with this solution, because it forecloses the
possibility of dumping the table definition and then reloading it into
a different schema.  We haven't yet talked much about how pg_dump should
behave with schemas --- but I think it will be important for pg_dump to
be able to choose whether to qualify object names with a schema name or
not in its dump output.  The above approach makes it harder to do so.
        regards, tom lane