postgres experience

Submitted by admin on Wed, 07/02/2008 - 14:30

With the current SVN version:

CREATE TABLE triplet (`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,client_address VARCHAR(40),sender VARCHAR(160) NOT NULL,recipient VARCHAR(160) NOT NULL,ip64 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip32 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip16 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip8 NUMERIC(4,0) DEFAULT 0 NOT NULL,count INT NOT NULL DEFAULT '0',uts INT NOT NULL,/* PRIMARY KEY (recipient,sender,ip64,ip32,ip16,ip8)*/)

error while initialising db: DB: create table: : ERROR: syntax error at or near "`" at character 23 triplet SQL: CREATE TABLE triplet (`id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,client_address VARCHAR(40),sender VARCHAR(160) NOT NULL,recipient VARCHAR(160) NOT NULL,ip64 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip32 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip16 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip8 NUMERIC(4,0) DEFAULT 0 NOT NULL,count INT NOT NULL DEFAULT '0',uts INT NOT NULL,/* PRIMARY KEY (recipient,sender,ip64,ip32,ip16,ip8)*/), NO GREYLISTING WILL BE DONE

Forums: 
gps forum

Submitted by admin on Wed, 07/02/2008 - 14:34.

This fixes the table creation:

CREATE TABLE triplet (id SERIAL PRIMARY KEY, client_address VARCHAR(40),sender VARCHAR(160) NOT NULL,recipient VARCHAR(160) NOT NULL,ip64 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip32 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip16 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip8 NUMERIC(4,0) DEFAULT 0 NOT NULL,count INT NOT NULL DEFAULT '0',uts INT NOT NULL);

Submitted by blueadept on Tue, 09/23/2008 - 14:42.

I found the table above did not work in the latest version because it tries to insert into the ID field since the insert statement is dependent on the field order, I moved it like this:-

CREATE TABLE triplet (client_address VARCHAR(40),sender VARCHAR(160) NOT NULL,recipient VARCHAR(160) NOT NULL,ip64 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip32 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip16 NUMERIC(4,0) DEFAULT 0 NOT NULL,ip8 NUMERIC(4,0) DEFAULT 0 NOT NULL,count INT NOT NULL DEFAULT '0',uts INT NOT NULL,id SERIAL PRIMARY KEY);

I also created the index like this, which also didn't get created:-

CREATE INDEX sender_recipient_index ON triplet (sender,recipient);

Hope this helps.