Skip site navigation (1) Skip section navigation (2)

Re: SQL scripts - sequences

From: Ian Turner <vectro(at)pipeline(dot)com>
To: Adam Lang <aalang(at)rutgersinsurance(dot)com>
Cc: PGSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: SQL scripts - sequences
Date: 2000-08-30 02:37:28
Message-ID: Pine.LNX.4.21.0008291930190.909-100000@crafter.house (view raw or flat)
Thread:
Lists: pgsql-general
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> The problem I am encountering is with having a primary key labeled as
> serial.  Do I have to drop the sequence also to be able to recreate the
> table?  Omitting the sequence drop did not seem to allow the creation of the
> table with a serial, but when I added a drop sequence, the table creates
> fine in the script.  Am I assuming correctly?

> If that is true, what would be the solution?  Create the sequence manually,
> don't drop the sequence in the script, and have the create table script not
> as a SERIAL, but have it set to a default value of the sequence?

You should do it the same way as PG_DUMP. For example, for

CREATE TABLE a (b serial, c integer);

I get this PG_DUMP output:

CREATE SEQUENCE "a_b_seq" start 1 increment 1 maxvalue 2147483647 minvalue 
1 cache 1 ;
CREATE TABLE "a" (
        "b" int4 DEFAULT nextval('a_b_seq'::text) NOT NULL,
        "c" int4
);
CREATE UNIQUE INDEX "a_b_key" on "a" using btree ( "b" "int4_ops" );

Alternatively, after inserting a few rows:

INSERT INTO a (c) VALUES (10);
INSERT INTO a (c) VALUES (100);
INSERT INTO a (c) VALUES (1000);
INSERT INTO a (c) VALUES (10000);

I get:

CREATE SEQUENCE "a_b_seq" start 4 increment 1 maxvalue 2147483647 minvalue
1  cache 1 ;
SELECT nextval ('"a_b_seq"');
CREATE TABLE "a" (
        "b" int4 DEFAULT nextval('a_b_seq'::text) NOT NULL,
        "c" int4
);
COPY "a" FROM stdin;
1       10
2       100
3       1000
4       10000
\.

So you see. All you do is create the sequence manually, with an optionally
higher initval, and set the 'serial' variable to be merely an integer with
a default value.

Ian Turner
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5rHNrfn9ub9ZE1xoRAv2IAJ9gAalZxF7Bv7ZmzOD+XPxEyNLKsgCfbH/k
9WmweHNHbig1sF2Ylnb1OnA=
=BjAZ
-----END PGP SIGNATURE-----


In response to

pgsql-general by date

Next:From: Tom LaneDate: 2000-08-30 04:06:48
Subject: Re: Table Alias
Previous:From: Lamar OwenDate: 2000-08-30 02:20:43
Subject: Re: 7.1 Release Date

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group