MySQL to Postgres question

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 21 Mar 2008 12:15:05 -0400
"Edward Blake" <comedian.watchman@gmail.com> wrote:

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?

http://www.postgresql.org/docs/8.3/static/sql-createtable.html

I have no idea what KEY means in MySQL. Is it supposed to create an
INDEX? If so, you will need to create the indexes (not including the
PRIMARY KEY) after you create the table.

And just a quick editor view, you spelled description incorrectly in
product_descrition and your product_id is your primary key so you don't
have to set it NOT NULL.

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)

iD8DBQFH4+GiATb/zqfZUUQRAqnnAJ424OFzGg23QFyKEy+MuiAVii02MQCfZL6Z
grPtt4bz9bwTcQYBgiuPTQM=
=U4S5
-----END PGP SIGNATURE-----

Hello,

On Fri, 21 Mar 2008 12:15:05 -0400 Edward Blake wrote:

9 KEY class_id (class_id),
10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)

this should create an index, or?
You want to do this later, after table creation.

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group

Edward Blake escribió:

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?

CREATE TABLE products (
product_id serial not null,
product_name varchar(255) not null,
product_description varchar(255) not null,
class_id integer not null,
subclass_id integer not null,
department_id integer not null,
PRIMARY KEY (product_id)
);

CREATE INDEX idx_prod_class_id ON products (class_id);
CREATE INDEX idx_prod_subclass_id ON products (subclass_id);
CREATE INDEX idx_prod_department_id ON products (department_id);

"Edward Blake" <comedian.watchman@gmail.com> writes:

When I try and rewrite it as a Postgres statement (below), it fails at line
9.
0 SET CONSTRAINTS ALL DEFERRED;

I don't think that does the same thing as mysql's foreign_key_checks = 0.

2 product_id serial[11] not null,

This is trying to create an array, it is not at all the same as integer(11).
All of your other uses of square brackets are wrong too. The varchars
will be okay with (255) but you should just drop the (11)'s --- use
either plain integer or bigint depending on what range you need.

9 KEY class_id (class_id),

PG doesn't have this type of clause within CREATE TABLE. To create
a non-unique index you need a separate CREATE INDEX statement, eg

CREATE INDEX products_class_id ON products(class_id);

regards, tom lane

On 21 Mar, 17:15, comedian.watch...@gmail.com ("Edward Blake") wrote:

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

Isn't KEY a MySQL shorthand for creating an index within the table
declaration. Why not create the index afterwards using CREATE INDEX
instead?

10 KEY subclass_id (subclass_id),
11 KEY department_id (department_id)
12 );

Any ideas?

Yes, just decouple the index declarations from the table declaration.
There are benefits to doing this, too, such as being able to populate
tables more rapidly before the indexes are added - a technique which
appears to be useful for certain kinds of applications.

Paul

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 21 Mar 2008 12:38:49 -0500
Justin <justin@emproshunts.com> wrote:

Any ideas?

Another way to do auto increment fields is create your own sequences.

I would not suggest that.

Sincerely,

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)

iD8DBQFH4+aZATb/zqfZUUQRAqKVAJ97RECRp6mQuDehzzI1sFmtzTg0zwCgh3yu
NrnoKXNupj6sfkjIu6wG8zw=
=lMPe
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 21 Mar 2008 12:47:38 -0500
Justin <justin@emproshunts.com> wrote:

Why????

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
a column after the fact, versus using the serial or bigserial
psuedo-types.

Sincerely,

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)

iD8DBQFH4+khATb/zqfZUUQRAp+yAKCrIGYWojKFhuyFy3biKQKgxJQ1kwCfRbB2
oF5G5DKbyHWN62wlXRDKmUQ=
=bUNa
-----END PGP SIGNATURE-----

"Joshua D. Drake" <jd@commandprompt.com> writes:

Why????

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
a column after the fact, versus using the serial or bigserial
psuedo-types.

As of (I think) 8.2, you can use ALTER SEQUENCE OWNED BY to manage
the dependency. In earlier releases it's true that you couldn't
exactly duplicate what SERIAL did (at least not without manual
catalog hacking), but now it truly is just a macro for things you
can do with SQL commands.

regards, tom lane

Edward Blake wrote:

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?

Another way to do auto increment fields is create your own sequences.

Also according to what i have read from the postgresql documents there
is no performance difference between varchar and text.

create sequence my_auto_increment
INCREMENT 1
START 1
CACHE 1;

CREATE TABLE products (
product_id integer primary key default
nextval(('my_auto_increment'::text)::regclass),
product_name text not null,
product_descrition text not null,
class_id integer not null,
subclass_id integer not null,
department_id integer not null);

Joshua D. Drake wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 21 Mar 2008 12:38:49 -0500
Justin <justin@emproshunts.com> wrote:

Any ideas?

Another way to do auto increment fields is create your own sequences.

I would not suggest that.

Why????

Joshua D. Drake 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
a column after the fact, versus using the serial or bigserial
psuedo-types.

Sincerely,

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

You still get an error if creating a table that specifies a sequence
that does not exist yet. I like to control the name of the sequence,
plus the starting values or change the incrementing values.

I might have read something wrong but using serial tells PostgreSQL to
automatic transforms.

CREATE TABLE /|tablename|/ (
/|colname|/ SERIAL

to

CREATE SEQUENCE /|tablename|/_/|colname|/_seq;
CREATE TABLE /|tablename|/ (
/|colname|/ integer NOT NULL DEFAULT nextval('/|tablename|/_/|colname|/_seq'));

I copied this from the help files.

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

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)

mydb=# create table foo(id serial, bar varchar);

NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
CREATE TABLE

Then, pg_dump produces:

-bash-3.00$ pg_dump -s --table=foo mydb

CREATE TABLE foo (
id integer NOT NULL,
bar character varying
);

CREATE SEQUENCE foo_id_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER SEQUENCE foo_id_seq OWNED BY foo.id;
ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq'::regclass);

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

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.