SEO

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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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





--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general