Thread: MySQL to Postgres question
The table I have in MySQL is similar to below:
0 SET FOREIGN_KEY_CHECKS=0;
1 CREATE TABLE products (
2 product_id integer(11) not null auto_increment,
3 product_name varchar(255) not null,
4 product_descrition varchar(255) not null,
5 class_id integer(11) not null,
6 subclass_id integer(11) not null,
7 department_id integer(11) not null
8 PRIMARY KEY (product_id),
9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );
When I try and rewrite it as a Postgres statement (below), it fails at line 9.
0 SET CONSTRAINTS ALL DEFERRED;
1 CREATE TABLE products (
2 product_id serial[11] not null,
3 product_name varchar[255] not null,
4 product_descrition varchar[255] not null,
5 class_id integer[11] not null,
6 subclass_id integer[11] not null,
7 department_id integer[11] not null
8 PRIMARY KEY (product_id),
9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );
Any ideas?
0 SET FOREIGN_KEY_CHECKS=0;
1 CREATE TABLE products (
2 product_id integer(11) not null auto_increment,
3 product_name varchar(255) not null,
4 product_descrition varchar(255) not null,
5 class_id integer(11) not null,
6 subclass_id integer(11) not null,
7 department_id integer(11) not null
8 PRIMARY KEY (product_id),
9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );
When I try and rewrite it as a Postgres statement (below), it fails at line 9.
0 SET CONSTRAINTS ALL DEFERRED;
1 CREATE TABLE products (
2 product_id serial[11] not null,
3 product_name varchar[255] not null,
4 product_descrition varchar[255] not null,
5 class_id integer[11] not null,
6 subclass_id integer[11] not null,
7 department_id integer[11] not null
8 PRIMARY KEY (product_id),
9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );
Any ideas?
Message-ID: <00e101c88b84$df1bbca0$9d5335e0$@r@sbcglobal.net> On: Fri, 21 Mar 2008 13:53:36 -0500, "Adam Rich" <adam.r@sbcglobal.net> wrote: > > I am not sure about 8.3 but certainly earlier releases of PostgreSQL > > would have specific dependency issues when a sequence was applied to > > a column after the fact, versus using the serial or bigserial > > psuedo-types. > I'd like to point out that using pg_dump does in fact apply sequences > to columns after the fact. (at least in 8.3) Columns lose their > "serial" designation after each backup/restore (and therefore during > version upgrades) Can someone expand upon this observation with respect to tables with surrogate primary keys generated by a sequence? I am not at all clear as to the implications of this statement but it caused me to wonder if the primary key values of such tables could be changed simply by dumping and reloading the database as in an upgrade between versions. Surely this is not the case? -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@Harte-Lyne.ca Harte & Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Sat, 22 Mar 2008 21:01:19 -0400 (EDT) "James B. Byrne" <byrnejb@harte-lyne.ca> wrote: > Message-ID: <00e101c88b84$df1bbca0$9d5335e0$@r@sbcglobal.net> > > I am not at all > clear as to the implications of this statement but it caused me to > wonder if the primary key values of such tables could be changed > simply by dumping and reloading the database as in an upgrade between > versions. Surely this is not the case? Of course not :). It just has to do with serial being a psuedo type and thus the actual declaration is just an integer with a default. This isn't that other database, we don't munge data :P Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ United States PostgreSQL Association: http://www.postgresql.us/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH5a8GATb/zqfZUUQRAn4JAJ9xzhFNq+pE4QqX7P1OVQNhe6thqgCgpVQC M3zEIsj5c+JTo3mU9XOjJqQ= =RPjl -----END PGP SIGNATURE-----
"James B. Byrne" <byrnejb@harte-lyne.ca> writes:
> On: Fri, 21 Mar 2008 13:53:36 -0500, "Adam Rich" <adam.r@sbcglobal.net>
> wrote:
>> I'd like to point out that using pg_dump does in fact apply sequences
>> to columns after the fact. (at least in 8.3) Columns lose their
>> "serial" designation after each backup/restore (and therefore during
>> version upgrades)
> Can someone expand upon this observation with respect to tables with surrogate
> primary keys generated by a sequence?
The short answer is that Adam's statement is wrong, or at least
misleading. pg_dump does restore serial columns to the same state
they were in. (This has little to do with whether the word "serial"
is used in the dump script, which is what he seems to be complaining
about.)
There are some corner cases involving altering either the name of the
serial column or the name of the associated sequence (but not both
to match) that a dump and reload wouldn't exactly reproduce in pre-8.2
versions.
regards, tom lane
> The short answer is that Adam's statement is wrong, or at least > misleading. Sorry Tom, I wasn't trying to do either. Joshua Drake (who I understand to be a reliable source of postgresql information) said that applying a sequence to a column after creation created issues, versus using the serial type which did not. That seemed misleading to me, since it's *exactly* what pg_dump does in 8.3. All I did was point that out, which I'd hardly call "complaining" and definitely not wrong. My point was that there was nothing special about serial in 8.3 Nothing "misleading" about that either.
On Sat, 22 Mar 2008 21:17:10 -0500 "Adam Rich" <adam.r@sbcglobal.net> wrote: > > The short answer is that Adam's statement is wrong, or at least > > misleading. > > Sorry Tom, I wasn't trying to do either. Joshua Drake (who I > understand to be a reliable source of postgresql information) said > that applying a sequence to a column after creation created issues, > versus using the serial type which did not. Right but as Tom reminded me, the behavior I was referring to was pre 8.3 and in (I believe) it was 8.2 we introduced alter sequence options to fix the problem. Sincerely, Joshua D. Drake -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit
Attachment
Adam Rich wrote: > > The short answer is that Adam's statement is wrong, or at least > > misleading. > > Sorry Tom, I wasn't trying to do either. Joshua Drake (who I understand > to be a reliable source of postgresql information) said that applying > a sequence to a column after creation created issues, versus using the > serial type which did not. He is wrong in that you *can* attach the same information that SERIAL does if you use ALTER SEQUENCE .. OWNED BY after the fact. If you don't, then he's right. pg_dump does use the OWNED BY stuff. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.