mysql create table -> psql - PostgreSQL Database

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

Nov 11 '05 #1

Subscribe Reply

6763

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

Nov 11 '05 #2

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

Nov 11 '05 #3

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

Nov 11 '05 #4

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

Nov 11 '05 #5

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/Xd5c21dVnhLsBV0 RAsDEAJoDdObeF/s/a2F7JEh6PPNFAZU ZbgCbBFeT
kV1KUSx31RTHSjq XLJkdkC8=
=WNDr
-----END PGP SIGNATURE-----

Nov 11 '05 #6

expect

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

Nov 11 '05 #7

Dennis Gearon

expect wrote:

On Tue, 09 Sep 2003 09:36:08 -0400
Scott Cain <ca**@cshl.or g> 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 '',
destinatio n 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.theaimsgro up.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

Nov 11 '05 #8

expect

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

Nov 11 '05 #9

Dennis Gearon

expect wrote:

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

Nov 11 '05 #10

Similar topics

6460

MySQL Multi Table Delete

by: Gordon | last post by:

I have 2 tables t and t1. In this case, t1 is a copy of t. I want to delete rows from t1 based on criteria on the t table and a relationship between t ad t1 (in this case the id column). In the results below, I would think that the delete should have deleted row 1 {1 5 me) and not row 3 (1 5 they) when I run this statement delete t1 from t, t1 where t.id = t1.id and t.id=1 and t.name = 'me'; Any ideas on why row 2 is deleted?

MySQL Database

3556

Is it fundamental limitation of MySQL?

by: jacob nikom | last post by:

I would like to create data model for a group of stores. All stores in this group are very similar to each other, so it is natural to allocate one MySQL database per store. Each database is going to have very similar set of tables with content related to a particular store. Let's suppose each store has multiple departments which are also very similar to each other. In this case I would like to model the departments

MySQL Database

1485

REFERENCES question in mySQL 4.1

by: Ed Smith | last post by:

I have two questions about REFERENCES: 1. It appears that mySQL treats REFERENCES associated with an attribute differently than FOREIGN KEY (<blah>) REFERENCES... Specifically, the first form does not appear to work, while the later does. Here's a simple example (slightly edited): mysql> CREATE TABLE person (id char(5) primary key); Query OK, 0 rows affected (0.04 sec)

MySQL Database

3948

MySQL tips that I have come across

by: Mike Chirico | last post by:

Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest version of this document can be found at: http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download

MySQL Database

2829

Non-stop queries coming from ??? XP, MySQL 5.0.15

by: Good Man | last post by:

Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work. Despite entering the required lines to "my.ini" (the new my.cnf) through notepad AND MySQL Administrator, the cache does not work. So, today I took a peek at the 'Health' tab in MySQL Administrator.

MySQL Database

2526

Mysql

by: paulq182 | last post by:

PLEASE HELP ME WITH MY CODE?? import java.sql.*; import java.io.*; class min_filmdb_rel_mysql { public static void main (String args ) throws SQLException, IOException {

Java

38518

MySQL Basic Tutorial

by: Atli | last post by:

This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get through this without much trouble. Programming knowledge is not required. Index What is SQL? Why MySQL? Installing MySQL. Using the MySQL command line interface

MySQL Database

221

367701

Uploading files into a MySQL database using PHP

by: Atli | last post by:

You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application needs to store entire files, the preferred method is to save the file onto the server’s file-system, and store the physical location of the file in your database. This is generally considered to be the easiest and fastest way to store files. ...

PHP

9582

MySQL to MS SQL Conversion

by: ssnaik84 | last post by:

Hi Guys, Last year I got a chance to work with R&D team, which was working on DB scripts conversion.. Though there is migration tool available, it converts only tables and constraints.. Rest of things (stored procedures, functions).. we have to manually edit. That time, we face some interesting challenges.. I failed to document all of them, but whatever I can share with u.. I will try.. :) ...

MySQL Database

10111

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9948

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9902

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9765

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8770

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6603

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5215

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

3446

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2738

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General