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

autoupdate sequences after copy

P: n/a
CSN
Is there a way to have p/k sequences get automatically
set to max(id)+1 after COPY's like the following?

copy table1 (id,name) from stdin;
1 abc
2 def
3 fhi
\.
CSN

__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Thursday 09 October 2003 08:10, CSN wrote:
Is there a way to have p/k sequences get automatically
set to max(id)+1 after COPY's like the following?

copy table1 (id,name) from stdin;
1 abc
2 def
3 fhi
\.


Not really - if you don't use the sequence it keeps its value. If you look at
pg_dump it issues an explicit setval() after a copy.

I'm not sure you can even work around it with a BEFORE trigger to check and
update the sequence, the nextval() call will probably be processed before the
trigger gets called (haven't checked). In any case, performance would be a
bit poor.

Is there any reason why you're supplying your own id values when you already
have a sequence?

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #2

P: n/a
I created a schema and piped it to psql but got an error message:

~/hacks/psql $ cat create.sql | psql test
ERROR: table "country" does not exist
ERROR: parser: parse error at or near ")" at character 91
ERROR: table "customer" does not exist
NOTICE: CREATE TABLE will create implicit sequence
'customer_customer_id_seq' for SERIAL column 'customer.customer_id'

But I don't know exactly where character 91 is... a line number
(including the comments in the file) would be more useful to me. I can
sort of triangualate where the problem with the parenthesis is by noting
where the "DROP TABLE" stmts failed, but that leaves two parenthetic
expressions to examine for correctness. Here is the relevant part of the
file that I piped to psql:

/*================================================= =========================*/
/* Project Filename: C:\Program Files\Datanamic\DeZign for Databases
V3\sample models\licenses.dez*/
/* Project
Name: */
/*
Author: */
/* DBMS: PostgreSQL
7 */
/*
Copyright: */
/* Generated on: 10/7/2003 5:24:50
PM */
/*================================================= =========================*/

/*================================================= =========================*/
/*
Tables */
/*================================================= =========================*/

DROP TABLE country;
CREATE TABLE country (
country_id VARCHAR(3) PRIMARY KEY,

country VARCHAR(80),
);

DROP TABLE customer;
CREATE TABLE customer (
customer_id SERIAL PRIMARY KEY,
country_id VARCHAR(3) REFERENCES country(country_id),

name VARCHAR(100) NOT NULL,
companyname VARCHAR(100) NOT NULL,
address1 VARCHAR(100) NOT NULL,

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #3

P: n/a
On Thursday 09 October 2003 13:15, Terrence Brannon wrote:
I created a schema and piped it to psql but got an error message:

~/hacks/psql $ cat create.sql | psql test
ERROR: table "country" does not exist
ERROR: parser: parse error at or near ")" at character 91
ERROR: table "customer" does not exist
NOTICE: CREATE TABLE will create implicit sequence
'customer_customer_id_seq' for SERIAL column 'customer.customer_id'

But I don't know exactly where character 91 is... a line number
(including the comments in the file) would be more useful to me.


Try psql -f create.sql, or in psql \i create.sql - both should give you line
numbers.

Also, try not to post a new question by replying to an existing one. It can
make it difficult for people to notice your question.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #4

P: n/a
> CREATE TABLE country (
country_id VARCHAR(3) PRIMARY KEY,

country VARCHAR(80),
);


You have a trailing comma after VARCHAR(80) just before the closing
bracket.

As for suggestions, you could try to collapse your whole file into a
single line and check it out in a text editor by going to column number
91.

HTH

---------------
Francois

Home page: http://www.monpetitcoin.com/

"Would Descartes have programmed in Pascal?" - Umberto Eco
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.