Postgres Pro
Downloads
Home   >   mailing lists

Thread: MySQL to Postgres question

MySQL to Postgres question

From
"Edward Blake"
Date:
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?

Re: MySQL to Postgres question

From
"Joshua D. Drake"
Date:
-----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-----

Re: MySQL to Postgres question

From
Andreas 'ads' Scherbaum
Date:
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

Re: MySQL to Postgres question

From
Rodrigo Gonzalez
Date:
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);
Attachment

Re: MySQL to Postgres question

From
Tom Lane
Date:
"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

Re: MySQL to Postgres question

From
Justin
Date:

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

Re: MySQL to Postgres question

From
"Joshua D. Drake"
Date:
-----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-----

Re: MySQL to Postgres question

From
Justin
Date:


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

Re: MySQL to Postgres question

From
"Joshua D. Drake"
Date:
-----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-----

Re: MySQL to Postgres question

From
Justin
Date:


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.

Re: MySQL to Postgres question

From
Tom Lane
Date:
"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

Re: MySQL to Postgres question

From
"Adam Rich"
Date:
> > 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);

Re: MySQL to Postgres question

From
Paul Boddie
Date:
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