Thread: mysql create table -> psql
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)
);
I get a parse error:
ERROR: parser: parse error at or near "(" at character 279
On Tuesday 09 September 2003 08: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)
> );
usually you would change the last two lines to:
...
id SERIAL,
PRIMARY KEY (id)
...
You don't need to create a sequence in most cases,
although I'm guessing you want to use int8 if you're storing firewall logs:
create sequence outbound_id_seq;
and primary key definition as:
...
id int8 default nextval('serial') not null,
PRIMARY KEY (id)
...
You will need to do something about the timestamp
default of zero, this is a MySQL-ism and won't work in PostgreSQL.
Probably dropping the NOT NULL constraint and DEFAULT altogether would
be best; if the timestamp should default to the current time,
use DEFAULT NOW().
Ian Barwick
barwick@gmx.net
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).
--
Richard Huxton
Archonet Ltd
You might want to take a look at a Perl module, SQL::Translator, which translates schema elements (table creates, indexes, views) from one DB's SQL to another. I don't know if the S::T on CPAN is up to date, so you might want to try their website to get the CVS version: http://sqlfairy.sourceforge.net/ Scott On Tue, 2003-09-09 at 02: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) > ); > > > I get a parse error: > ERROR: parser: parse error at or near "(" at character 279 > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- ------------------------------------------------------------------------ Scott Cain, Ph. D. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory
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
);
the problem is
constraint id PRIMARY (id), it should be constraint id PRIMARY KEY (id).
Besides that, you will have problems with '0000-00-00'... month starts at 1, day starts at 1, and I don't know about year 0. Maybe this field 'time' should be NULLable, or maybe its default value should be '0001-01-01 00:00:00'.
On Tue, 2003-09-09 at 03:10, expect wrote:
constraint id PRIMARY (id), it should be constraint id PRIMARY KEY (id).
Besides that, you will have problems with '0000-00-00'... month starts at 1, day starts at 1, and I don't know about year 0. Maybe this field 'time' should be NULLable, or maybe its default value should be '0001-01-01 00:00:00'.
On Tue, 2003-09-09 at 03: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)
);
I get a parse error:
ERROR: parser: parse error at or near "(" at character 279
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friendAttachment
On Tue, 09 Sep 2003 09:36:08 -0400 Scott Cain <cain@cshl.org> wrote: > You might want to take a look at a Perl module, SQL::Translator, which > translates schema elements (table creates, indexes, views) from one DB's > SQL to another. I don't know if the S::T on CPAN is up to date, so you > might want to try their website to get the CVS version: > > http://sqlfairy.sourceforge.net/ Funny name, thanks. It's amazing how I didn't find this via google.com. Is it just me or is google less effective than it once was? Sure seems like it. I think they're going to have to modify their model. > > Scott > > > On Tue, 2003-09-09 at 02: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) > > ); > > > > > > I get a parse error: > > ERROR: parser: parse error at or near "(" at character 279 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > -- > ------------------------------------------------------------------------ > Scott Cain, Ph. D. cain@cshl.org > GMOD Coordinator (http://www.gmod.org/) 216-392-3087 > Cold Spring Harbor Laboratory > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > >
expect wrote: >On Tue, 09 Sep 2003 09:36:08 -0400 >Scott Cain <cain@cshl.org> wrote: > > > >>You might want to take a look at a Perl module, SQL::Translator, which >>translates schema elements (table creates, indexes, views) from one DB's >>SQL to another. I don't know if the S::T on CPAN is up to date, so you >>might want to try their website to get the CVS version: >> >>http://sqlfairy.sourceforge.net/ >> >> > >Funny name, thanks. It's amazing how I didn't find this via google.com. >Is it just me or is google less effective than it once was? Sure seems like it. >I think they're going to have to modify their model. > > > >>Scott >> >> >>On Tue, 2003-09-09 at 02: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) >>>); >>> >>> >>>I get a parse error: >>>ERROR: parser: parse error at or near "(" at character 279 >>> >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 8: explain analyze is your friend >>> >>> >>-- >>------------------------------------------------------------------------ >>Scott Cain, Ph. D. cain@cshl.org >>GMOD Coordinator (http://www.gmod.org/) 216-392-3087 >>Cold Spring Harbor Laboratory >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 7: don't forget to increase your free space map settings >> >> >> >> > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > > I think google's reach into technical sites is diminishing. We generate SOOOOOOOOOOOO much traffic on all the open source AND closed source discussion groups, it's staggering. Jeesh, it would be intereseting to know how many words, characters, etc were in each of those domains. I be the folks at marc.theaimsgroup.com could give us an idea about thiers plus add in sourceforge, and it'd be a major part of it. Well, then there's the stuff on usenet that's not on listserves.