mysql create table -> psql

[expect]

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

[Ian Barwick]

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
ba*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Scott Cain]

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. ca**@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

[scott.marlowe]

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
);
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Franco Bruno Borghesi]

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:

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/Xd5c21dVnhLsBV0RAsDEAJoDdObeF/s/a2F7JEh6PPNFAZUZbgCbBFeT
kV1KUSx31RTHSjqXLJkdkC8=
=WNDr
-----END PGP SIGNATURE-----

[expect]

On Tue, 09 Sep 2003 09:36:08 -0400
Scott Cain <ca**@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. ca**@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

[Dennis Gearon]

expect wrote:

On Tue, 09 Sep 2003 09:36:08 -0400
Scott Cain <ca**@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. ca**@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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[expect]

On Tue, 09 Sep 2003 09:36:08 -0400
Scott Cain <ca**@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. ca**@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

[Dennis Gearon]

expect wrote:

On Tue, 09 Sep 2003 09:36:08 -0400
Scott Cain <ca**@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. ca**@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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match