maintaining sequences
Hey guys,
In SQL Server, if you need to move a table with an identity column (a default sequence) with its data over to another database, you can create the table, turn off the identity column, bulk copy the data into the table, then turn the identity column back on. Is there a convenient way to do this in postgreSQL with a column that has a default sequence value?
Or I guess I'm asking if I can add a default sequence into a column after I've created the table and added data to it?
Roy.
On Thu, 13 Sep 2001 roypgsqlgen@xemaps.com wrote:
Or I guess I'm asking if I can add a default sequence into a column
after I've created the table and added data to it?
The easiest way to do this is just create your table as normal, do
your data load (it will ignore the sequence if you're manually setting
the identity columns) and then drop and recreate the sequence with
the starting value you want.
See, easy. ^_^
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas@townnews.com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
You can just reset the next-available value with "select setval
('sequencename', newvalue);"
----- Original Message -----
From: "Shaun Thomas" <sthomas@townnews.com>
To: <roypgsqlgen@xemaps.com>
Cc: <pgsql-general@postgresql.org>
Sent: Thursday, September 13, 2001 2:49 PM
Subject: Re: [GENERAL] maintaining sequences
Show quoted text
On Thu, 13 Sep 2001 roypgsqlgen@xemaps.com wrote:
Or I guess I'm asking if I can add a default sequence into a column
after I've created the table and added data to it?The easiest way to do this is just create your table as normal, do
your data load (it will ignore the sequence if you're manually setting
the identity columns) and then drop and recreate the sequence with
the starting value you want.See, easy. ^_^
-- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. Thomas INN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830 | | Email: sthomas@townnews.com AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Oh, great! So I'm going to do this:
select setval('sequencename', ( select max(someid) from sometable ) );
Thanx!
Roy.
Show quoted text
-----Original Message-----
From: Jeff Eckermann [mailto:jeckermann@verio.net]
Sent: Thursday, September 13, 2001 5:45 PM
To: roypgsqlgen@xemaps.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] maintaining sequencesYou can just reset the next-available value with "select setval
('sequencename', newvalue);"
Import Notes
Resolved by subject fallback