Re: sequence in schema -- broken default

>>CREATE DATABASE d;
>>\c d
>>
>>CREATE SCHEMA one;
>>SET search_path TO one;
>>
>>CREATE SEQUENCE foo_seq;
>>CREATE TABLE foo(
>> i integer
>> DEFAULT nextval('foo_seq')
>>);
>>
>>
>>SET search_path TO public;
>>
>>INSERT INTO foo VALUES (DEFAULT);
>
>>
>>The problem is that the DEFAULT nextval(... needs to qualify
>>the sequence with the schema, but I am not sure how to
>>determine the schema in my definition file.
>
>
>I am not sure I exactly understand the above paragraph, but from
>yourexample
>you are trying to insert into public.foo which does not exist. The value
>would be
>one.foo .
>
>insert into one.foo values();

Gah. Sorry.

That should have been ...

# set search_path to public;
SET
# INSERT INTO one.foo VALUES (DEFAULT);
ERROR: relation "foo_seq" does not exist

So, as you can see, since I specified the default as
nextval('foo_seq')
it does not find the sequence in my search_path.

It needs to be
nextval('one.foo_seq')
but I do not want to hard-code the name of the schema
in my database definition file if I can avoid it.

By the way... should the way you wrote it work?

# INSERT INTO one.foo VALUES ();
ERROR: syntax error at or near ")" at character 29

Seems like maybe it should work with the default, but I don't know.

# select version();
version
---------------------------------------------------------------------
PostgreSQL 7.4.1 on i386-portbld-freebsd4.9, compiled by GCC 2.95.4

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail