Re: SQL scripts - sequences

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