Thread: create table within a schema

Hello

I have the following code:

create schema test;

create type test.my_type as enum ( 'a', 'b' );

create table test.my_table_1 (
    id     bigserial not null,
    type   test.my_type not null,
    length bigint,
    primary key ( id )
);


create sequence test.my_sequence_id;


create table test.my_table_2 (
id     bigint not null default public.nextval ( test.my_sequence_id ),
type   test.my_type not null,
length bigint,
primary key ( id )
);

This last create operation fails with the message

ERROR:  missing FROM-clause entry for table "test"

Could someone explain me that error ?  What are the difference between the table my_table_1 and the table my_table_2  ?
The documentation show that the table creation must be followed by a statement like
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
but the table creation has been rejected.

Thanks for your reply

Vincent De Groote

On Fri, May 6, 2011 at 12:10 PM, Vincent De Groote
<vdg.encelade@gmail.com> wrote:
> Hello
>
> I have the following code:
>
> create schema test;
>
> create type test.my_type as enum ( 'a', 'b' );
>
> create table test.my_table_1 (
>     id     bigserial not null,
>     type   test.my_type not null,
>     length bigint,
>     primary key ( id )
> );
>
>
> create sequence test.my_sequence_id;
>
>
> create table test.my_table_2 (
> id     bigint not null default public.nextval ( test.my_sequence_id ),
> type   test.my_type not null,
> length bigint,
> primary key ( id )
> );
>
> This last create operation fails with the message
>
> ERROR:  missing FROM-clause entry for table "test"
>
> Could someone explain me that error ?  What are the difference between the
> table my_table_1 and the table my_table_2  ?
> The documentation show that the table creation must be followed by a
> statement like
> ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
> but the table creation has been rejected.

Quick guess: Put single quotes around the name of the sequence

create table test.my_table_2 (
id     bigint not null default public.nextval ('test.my_sequence_id'),
type   test.my_type not null,
length bigint,
primary key ( id )
);

Otherwise it tries to evaluate "test.my_sequence_id" as if it were a
scalar subquery, which does not have a FROM clause, so fails.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services