By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,807 Members | 1,995 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,807 IT Pros & Developers. It's quick & easy.

sequence in schema -- broken default

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 22 '05 #2

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.