473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

Nov 22 '05 #1
2 2736
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Anders Ljusberg | last post by:
Hi! I have a problem.. I'm trying to get the XML from a dataset to conform to an XSD, but it keeps rearranging some elements. Try this schema: <xs:schema id="test"...
7
by: Andrei Ivanov | last post by:
This happend again, but now, postgresql tells me where it happens: pg_dump: SQL command failed pg_dump: Error message from server: ERROR: did not find '}' at end of input node pg_dump: The...
3
by: Hans-Dieter Franz | last post by:
Hello, I have a problem with a Java user-defined stored procedure and the "alter sequence" statement. I use DB2 8.1.2 and had the same problem with 8.1.0, but not with 7.*. I get the following...
5
by: Xela | last post by:
Hi! I would like, without writing a procedure which require the configuration of an additionnal C-compiler and which is not portable (I am wring à java application targeted on DB2). And I would...
15
by: Robby Russell | last post by:
I am trying to track down a method of determining what a sequence name is for a SERIAL is in postgresql. For example, CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT); \d foo...
3
by: kevin | last post by:
Is that even possible? I am creating a web service in .NET to expose some already created .NET programs to other groups. One group is writing the client in PERL, and thus wishes the wsdl schema...
7
by: urban.widmark | last post by:
Hello We are having some problems with triggers, sequences and union all in V8 on code that worked fine in V7. Was wondering if someone else has seen this and/or knows what to do. A trigger...
2
by: shumaker | last post by:
I designed a schema in VS C# 2005 Express and am wondering why it chooses to place a sequence tag around the elements of a table. And additionally, if I move the elements around within the table,...
3
by: Daniel Wilson | last post by:
I am trying to read data from MS SQL Server and turn it into an XML message to send to a remote server, as follows. sfSchemaFileDiag.FilterIndex = 2 If sfSchemaFileDiag.ShowDialog =...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.