Thread: sequence in schema -- broken default
I must have a fundamental misunderstanding about using schema.
Before using schema, I usually have a file that has my database
definition, and I can play that file back in to a new database to
create a testing area or to create my production setup.
I think I want to use schema the same way.
My problem is using a sequence for a default value. I know that
if I use a serial instead, all of this will go away, but my design
already uses separate sequences, so I am hoping I will not need
to change it...
This illustrates the problem:
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.
Any hints?
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
> 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();
Sincerely,
Joshua D. Drake
>
> Any hints?
>
> _________________________________________________________________
> Add photos to your e-mail with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
>>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
> > > > By the way... should the way you wrote it work? > No. I did not complete the syntax. > # 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. > No, the reason the below works is version() is a function where values () is not. I don't think you are going to have any choice but to hardcode the sequence value unless you want to bounce in between search paths based on who is connecting. > > # 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
>>By the way... should the way you wrote it work? >> >No. I did not complete the syntax. > > >># 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. >> >No, the reason the below works is version() is a function where values >() is not. Yes. I was just including that in case something had changed in recent versions. I guess it just seemed strange to me that this works ... # create table x (a int, b int); CREATE TABLE # insert into x values(5); INSERT 18518 1 but this does not ... # insert into x values(); ERROR: syntax error at or near ")" at character 22 >I don't think you are going to have any choice but to hardcode the >sequence value >unless you want to bounce in between search paths based on who is >connecting. All I really want is some way to ensure that the DEFAULT is hooked up to the right sequence. I guess maybe it could be considered a feature that you can code the nextval as a relative name and have the value pulled from different sequences depending on your search_path, but I think a more useful pattern is to always pull from the same sequence. Of course, I guess that is why there is a serial type ;o) Thanks for your time. _________________________________________________________________ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail
> Yes. I was just including that in case something had changed in
> recent versions. I guess it just seemed strange to me that this
> works ...
>
> # create table x (a int, b int);
> CREATE TABLE
> # insert into x values(5);
> INSERT 18518 1
>
> but this does not ...
>
> # insert into x values();
> ERROR: syntax error at or near ")" at character 22
>
Actually if you think about it, it makes sense. At least from the
example you provide.
insert into x values(5) is a integer insertion, and it just inserts into
the first column but
the second example insert into x values() specifies a blank value. A
blank is a string
(versus a NULL which is nothing) and the parser barfs on it with an
integer. Although
you will get a different error, insert into x values ('') will also fail.
Sincerely,
Joshua Drake
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Mensaje citado por "Joshua D. Drake" <jd@commandprompt.com>:
>
> > # 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.
> >
> No, the reason the below works is version() is a function where values
> () is not.
> I don't think you are going to have any choice but to hardcode the
> sequence value
How about:
INSERT INTO one.foo VALUES (DEFAULT)
???
It seemed to work here.
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-------------------------------------------------------
Martín Marqués | Programador, DBA
Centro de Telemática | Administrador
Universidad Nacional
del Litoral
-------------------------------------------------------
Mensaje citado por "Joshua D. Drake" <jd@commandprompt.com>:
> > # insert into x values();
> > ERROR: syntax error at or near ")" at character 22
> >
> Actually if you think about it, it makes sense. At least from the
> example you provide.
> insert into x values(5) is a integer insertion, and it just inserts into
> the first column but
> the second example insert into x values() specifies a blank value. A
> blank is a string
> (versus a NULL which is nothing) and the parser barfs on it with an
> integer. Although
> you will get a different error, insert into x values ('') will also fail.
I feel as if the error message is saying that there's an error near ")" because
there is a missing value (no value al all), not because he entered a string.
--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-------------------------------------------------------
Martín Marqués | Programador, DBA
Centro de Telemática | Administrador
Universidad Nacional
del Litoral
-------------------------------------------------------
>>>
>>>
>>Actually if you think about it, it makes sense. At least from the
>>example you provide.
>>insert into x values(5) is a integer insertion, and it just inserts into
>>the first column but
>>the second example insert into x values() specifies a blank value. A
>>blank is a string
>>(versus a NULL which is nothing) and the parser barfs on it with an
>>integer. Although
>>you will get a different error, insert into x values ('') will also fail.
>>
>>
>
>I feel as if the error message is saying that there's an error near ")" because
>there is a missing value (no value al all), not because he entered a string.
>
>
>
That is actually what I meant.
J
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
On Fri, 23 Jan 2004, Joshua D. Drake wrote:
>
> > 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();
I've a feeling that's what was meant in the original posting and that having
done that the nextval on the default sequence fails because the sequence is
not in the search_path.
I seem to remember something like turning up sometime last year for me. I
don't have a 7.4 or HEAD install to check against at the moment.
--
Nigel J. Andrews