Thread: autoupdate sequences after copy
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
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
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,
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
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 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> > Is there any reason why you're supplying your own id values when you already have a sequence? I'm importing a lot of data and tables (from mysql) and want to keep the ID's the same. CSN __________________________________ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com
I just run a script to update them after importing data. Something like this... (not a real script...)
while read tablename
do
echo "select setval('${tablename}_${tablename}_seq', \
(select max(${tablename}id) from $tablename))" | psql database
done
< tablenames.txt
Of course, this assumes you allowed the default sequence names to be created via SERIAL and that you created the
primarykeys as <tablename>id. You might need a text file with table, key, and sequence names, but this is likely
easierthan issuing a bunch of psql commands by hand.
Ian Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
iharding@tpchd.org
Phone: (253) 798-3549
Pager: (253) 754-0002
>>> CSN <cool_screen_name90001@yahoo.com> 10/09/03 12:10PM >>>
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
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> Is there any reason why you're supplying your own id
values when you already have a sequence?
I'm importing a lot of data and tables (from mysql)
and want to keep the ID's the same.
CSN
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
On Thu, Oct 09, 2003 at 12:10:44PM -0700, CSN wrote:
> 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.
The problem is when you do
pg_dump -s -t table database > schema
pg_dump -a -t table database > data
edit data
psql -f schema otherdatabase
psql -f data otherdatabase
you then need to quickly
select setval('col_id_seq',(select max(id) from col));
for each sequence.. and not forget..
Cheers,
Patrick
> I just run a script to update them after importing data. Something
> like this... (not a real script...)
>
> while read tablename
> do
> echo "select setval('${tablename}_${tablename}_seq', \
> (select max(${tablename}id) from $tablename))" | psql database
> done
> < tablenames.txt
>
> Of course, this assumes you allowed the default sequence names to be
> created via SERIAL and that you created the primary keys as
> <tablename>id. You might need a text file with table, key, and
> sequence names, but this is likely easier than issuing a bunch of psql
> commands by hand.
>
You can get a list of you sequences with
CREATE VIEW public.sequences AS
SELECT
nspname,
pg_get_userbyid(c.relowner) AS sequenceowner,
c.relname AS sequencename
FROM (pg_class c JOIN pg_namespace t2 ON ((t2.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char")
ORDER BY nspname, pg_get_userbyid(c.relowner), c.relname;
Maybe you use the output of this view to create a table in which you add
columns for the table name and column name, and then go through the kind
of iteration suggested above.
~Berend Tober
> 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