Thread: SQL scripts - sequences
I'm building a database and making my table creates in a text file so that, if I want to recreate my database, I can just rerun the scripts. At the top of the script are the statements to drop the tables. Next is the area which creates the tables. 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 correct, what if I go a step farther and have data dumped from the table before it is dropped. Won't my sequence be screwed up if I drop it and then reimport the data? Would the sequence technically be back at 1 and if I have 100 records, will it try to insert duplicate data? 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? Adam Lang Systems Engineer Rutgers Casualty Insurance Company
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? Yes and yes. If that is correct, what if I go a step farther and have data dumped from the table before it is dropped. Won't my sequence be screwed up if I drop it and then reimport the data? Would the sequence technically be back at 1 and if I have 100 records, will it try to insert duplicate data? 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? My solution in a similar situation is to have a bunch of scripts to drop/create the tables/functions/views/triggers/... needed. Use pg_dump to dump just the data (not the schema) but include the -c flag (I use -a -c -D) so that sequences are dropped and recreated with the right values. To reload: run pg_dump -a -c -D, run your scripts (which drop and recreate the data structures, then run your pg_dump output through psql to reload the data. Sequences and tables will agree fine. Cheers, Brook
Oh wow (after reading over pg_dump and trying it). I didn't know about that. That's very nice. Is it safe and accurate? Should I be able to feel mostly secure about using that to dump my database definitions? Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Brook Milligan" <brook@biology.nmsu.edu> To: <aalang@rutgersinsurance.com> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, August 29, 2000 12:28 PM Subject: Re: [GENERAL] SQL scripts - sequences > My solution in a similar situation is to have a bunch of scripts to > drop/create the tables/functions/views/triggers/... needed. Use > pg_dump to dump just the data (not the schema) but include the -c flag > (I use -a -c -D) so that sequences are dropped and recreated with the > right values. To reload: run pg_dump -a -c -D, run your scripts > (which drop and recreate the data structures, then run your pg_dump > output through psql to reload the data. Sequences and tables will > agree fine. > > Cheers, > Brook
Also, is pg_dump good for backing up a database or should I use another means? (I did read the part about partial indices and large objects not supported.) Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Brook Milligan" <brook@biology.nmsu.edu> To: <aalang@rutgersinsurance.com> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, August 29, 2000 12:28 PM Subject: Re: [GENERAL] SQL scripts - sequences > My solution in a similar situation is to have a bunch of scripts to > drop/create the tables/functions/views/triggers/... needed. Use > pg_dump to dump just the data (not the schema) but include the -c flag > (I use -a -c -D) so that sequences are dropped and recreated with the > right values. To reload: run pg_dump -a -c -D, run your scripts > (which drop and recreate the data structures, then run your pg_dump > output through psql to reload the data. Sequences and tables will > agree fine. > > Cheers, > Brook
Is it safe and accurate? Should I be able to feel mostly secure about using that to dump my database definitions? As safe and accurate as it gets without having a set of scripts that loaded the data in the first place (barring you writing something better, of course). The main catch is that all the metadata about relations doesn't seem to be preserved within the database and so can't be dumped with pg_dump (correct me if I'm wrong). Consequently, I have relied on keeping scripts for the setup of relations and using pg_dump for the data. In the script below, I get this error after each references statement: NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented You're using an old release. Upgrade. Nope, I'm running 6.5. Besides upgrading to 7 (I tried once and it was a pain in the ass. I'm running RedHat6.2 and I only had about a million dependencies to update also... one which I never got working...), what can I do? Technically I really don't need the references, just a nice addition. You really do want references for maintaining data integrity. Can't you upgrade PostgreSQL without upgrading the OS? If you use pg_dump it shouldn't be that bad. (Or use the NetBSD pkgsrc system which pretty much automates the installation except for loading the relations/data. :) There are even some hooks for using the pkgsrc system with Linux, I think. Cheers, Brook
I gave it a go, but it came back with a list of dependencies it needed (RPM). I went to upgrade those and they came with a bunch of dependencies, etc, etc. Eventually I got stuck at the point where I had to upgrade the libc.6.so (or something like that) and it wouldn't take. I have all the patience in the world when it comes to coding, but when it comes to installing programs, I loose it and go back to what was working. Hence, I'm currently using 6.5. :) Trashing the system and reinstalling is no big deal. I've only been using PHP and postgreSQL for about two weeks now. So... I only need to back up a couple applications. Nothing too traumatic for me to replace. I just wanted to get productive and didn't want to waste too much more time trying to make a go of 7.0. Adam Lang Systems Engineer Rutgers Casualty Insurance Company ----- Original Message ----- From: "Brook Milligan" <brook@biology.nmsu.edu> To: <aalang@rutgersinsurance.com> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, August 29, 2000 2:51 PM Subject: Re: [GENERAL] SQL scripts - sequences > You really do want references for maintaining data integrity. Can't > you upgrade PostgreSQL without upgrading the OS? If you use pg_dump > it shouldn't be that bad. (Or use the NetBSD pkgsrc system which > pretty much automates the installation except for loading the > relations/data. :) There are even some hooks for using the pkgsrc > system with Linux, I think. > > Cheers, > Brook
-----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-----