Re: MySQL to Postgres question

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