Re: mysql create table -> psql

On Tue, 9 Sep 2003, Richard Huxton wrote:

> On Tuesday 09 September 2003 07:10, expect wrote:
> > Hello,
> >
> > Trying to get this MySql create table command to work, no luck.
> >
> > create sequence serial;
> >
> > CREATE TABLE outbound (
> > source char(100) default '',
> > destination char(100) default '',
> > sport int4 default 0 NOT NULL,
> > dport int4 NOT NULL default 0,
> > time timestamp NOT NULL default '0000-00-00 00:00:00',
> > id int8 default nextval('serial') not null,
> > constraint id PRIMARY (id)
> > );
>
> In addition to everything Ian says, you probably want varchar() not char() for
> the source and destination. The char type is space-padded to the length of
> the field (MySQL strips them somehow, but can't remember how off the top of
> my head - anyway, varchar is the standard variable-length text type).

One more addition, you can use bigserial to define a serial with a big
int. Also, you might as well drop the explicit casting of int4, since int
= int4 right now, and in some future may be int8 when even calculator
watches are 64 bit. Might as well have your DDL ready to take advantage
of it.

Also, you can move your pk def into the single field defining the pkey
here (id).

also note, if you didn't have a pk defined on your serial and wanted it to
be ensured to be unique, you would need to add a unique keyword there as
well, since autounique indexes on serials went away around V7.3 of pgsql.

Finally, note that timestamp is a SQL spec type that does NOT do in
postgresql what it does in MySQL (i.e. get an auto inserted timestamp on
insert / update) so even the default now() isn't quite what you'd expect.
If you need that field to always get updated to the latest time when
the row is updated you'll have to write a trigger. It's a cardinal
example in the docs, I believe.

CREATE TABLE outbound (
source varchar(100) default '',
destination varchar(100) default '',
sport int default 0 NOT NULL,
dport int NOT NULL default 0,
time timestamp NOT NULL default now(),
id bigserial not null primary key
);