views:

97

answers:

2

How can I translate this 2 queries in postgresql ? :

CREATE TABLE  `example` ( 
  `id` int(10) unsigned NOT NULL auto_increment,    
  `from` varchar(255) NOT NULL default '0',    
  `message` text NOT NULL,    
  `lastactivity` timestamp NULL default '0000-00-00 00:00:00',    
  `read` int(10) unsigned NOT NULL,    
  PRIMARY KEY  (`id`),    
  KEY `from` (`from`)    
) DEFAULT CHARSET=utf8;

Query:

SELECT *
FROM table_1
LEFT OUTER JOIN table_2 ON ( table_1.id = table_2.id ) 
WHERE (table_1.lastactivity > NOW()-100);
+2  A: 

Use:

   SELECT *
     FROM table_1 t1
LEFT JOIN table_2 t2 ON t2.id = t1.id
    WHERE t1.lastactivity > CURRENT_TIMESTAMP - INTERVAL '100 days'

CURRENT_TIMESTAMP is ANSI standard, and works on Oracle, MySQL, Postgres, SQL Server...

Here's the CREATE table statement converted:

CREATE TABLE example (
  id INTEGER PRIMARY KEY, 
  "from" VARCHAR(255) NOT NULL DEFAULT '0',
  message text NOT NULL,
  lastactivity timestamp DEFAULT NULL,
  read INTEGER NOT NULL
)

I can't find anything about Postgres allowing character sets per table, only that you would set UTF8 support using the UNICODE keyword when creating the database:

CREATE DATABASE your_db WITH  ENCODING 'UNICODE';
$ createdb -E UNICODE your_db --CLI version

Postgre, like Oracle, uses sequences for AUTO_INCREMENT behavior:

CREATE SEQUENCE example_seq START 1;

Then, you need to call NEXTVAL([your sequence name]) in the insert statement to populate the primary key:

INSERT INTO example (id) VALUES (NEXTVAL(example_seq))
OMG Ponies
I upvoted, but I'm pretty sure the 'serial' data type works well enough for simple id needs, doesn't it?
colinmarc
@colinmarc: I'm not familiar with the serial data type.
OMG Ponies
http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL serial allows you to use DEFAULT or not specify and it will auto-increment. In my tables I always use "id" serial PRIMARY KEY.
colinmarc
DEFAULT '0000-00-00 00:00:00' is not going to work, it's not a valid timestamp. DEFAULT NULL is allowed.
Frank Heikens
Also, now() is preferred to CURRENT_TIMESTAMP in PostgreSQL according to http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
colinmarc
I get this error: ERROR: syntax error at or near "from"LINE 3: from VARCHAR(255) NOT NULL DEFAULT '0',
xRobot
The word "from" cannot be a field name
pcent
@pcent: from is the column name, a reserved keyword...
OMG Ponies
'from' is the name of the column - the problem, I think, is that it should be enclosed in quotes because it's also a keyword
colinmarc
@colinmarc: From your link, all I got was that NOW() is the equivalent to CURRENT_TIMESTAMP, not the preferred syntax (which your link is for 8.1 when 8.4 is current).
OMG Ponies
You should use double quotes to declare from as a field name
pcent
FROM is a registered keyword - need to use double quotes to escape it: http://www.postgresql.org/docs/8.1/interactive/sql-keywords-appendix.html
OMG Ponies
I agree that the serial type should be used for the ID. Especially if he's used to MySQL autoincrement.
rfusca
"now() is a traditional PostgreSQL equivalent to transaction_timestamp()" - from http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT, the 8.4 documentation.
colinmarc
@colinmarc: "traditional" != preferred, as you claimed.
OMG Ponies
indeed in the "date/time functions" table, it says that now()/transaction_timestamp() is equivalent to current_timestamp- I'd usually use current_timestamp because it's a reasonably well-known standard.
araqnid
and charset default ?
xRobot
@xRobot: postgresql will store all text values using the character set that is defined when you create the database (and shown when you do \l in psql)
araqnid
+1  A: 

I'll just post my own answer. Here's your CREATE:

CREATE TABLE "example" ( 
  "id" serial PRIMARY KEY,    
  "from" varchar(255) NOT NULL DEFAULT '0',    
  "message" text NOT NULL,    
  "lastactivity" timestamp NULL DEFAULT NULL,    
  "read" integer NOT NULL
);

Note that serial accomplishes int(11) NOT NULL auto_increment, and that integer is used instead of int. Your query seems fine.

colinmarc