如何将这2个查询从Mysql转换为Postgresql？ ：

How can I translate this 2 queries in postgresql ? :

如何在postgresql中翻译这2个查询? :

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 个解决方案

#1

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...

CURRENT_TIMESTAMP是ANSI标准,适用于Oracle,MySQL,Postgres,SQL Server ......

Here's the CREATE table statement converted:

这是转换的CREATE表语句:

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:

我找不到关于Postgres允许每个表的字符集的任何内容,只是在创建数据库时使用UNICODE关键字设置UTF8支持:

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

Postgre, like Oracle, uses sequences for AUTO_INCREMENT behavior:

像Oracle一样,Postgre使用序列进行AUTO_INCREMENT行为:

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语句中调用NEXTVAL([您的序列名称])来填充主键:

INSERT INTO example (id) VALUES (NEXTVAL(example_seq))

#2

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.

请注意,serial完成int(11)NOT NULL auto_increment,并使用该整数而不是int。您的查询似乎很好。