Thread: 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
identitycolumn 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


> 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.

> -----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 sequences
>
> You can just reset the next-available value with "select setval
> ('sequencename', newvalue);"