Mysql转PostGreSQL常见问题

ID自增问题

Mysql中创建一个自增ID语句如下:

CREATE TABLE user (
    `id` bigint not null auto_increment primary key ,
    `name` varchar(255)
)

PostGreSql对字段的自增语句使用 SERIAL，比如使用postgres数据库使id字段自增

CREATE TABLE user(
    `id` SERIAL PRIMARY KEY ,
    `name` varchar(20)
)

# 查询创建的表结构
sso_db=# \d users
                                   Table "public.users"
 Column |         Type          | Collation | Nullable |              Default
--------+-----------------------+-----------+----------+------------------------------------
 id     | integer               |           | not null | nextval('users_id_seq'::regclass)
 name   | character varying(29) |           |          |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)

以上创建users表相当于执行了以下语句：

CREATE SEQUENCE users_id_seq;

CREATE TABLE users (
    id integer not null DEFAULT nextval('users_id_seq'),
    name varchar(20)
);

ALTER SEQUENCE users_id_seq OWNED BY users.id;

⚠️注意：序列生成器不是事务安全的。并发环境中会有问题。在事务中如果其中一个客户端回滚事务，则该序列值将被丢弃。导致序列值不连续。对表中添加数据的时候，如果要指定id的值，可以使用DEFAULT关键字。如果使用某一个数值则会导致序列在使用到该值的时候重复报错。

以上情况需要手动更新对应sequence的值.ALTER SEQUENCE users_id_seq RESTART WITH 8;

详情示例：

sso_db=# insert into users (id,name) values (DEFAULT,'blog.68hub.com');
INSERT 0 1
sso_db=# select * from users;
 id |      name
----+----------------
  1 | blog.68hub.com
(1 row)

sso_db=# INSERT INTO users (name) values ('blog.happyhack.cn');
INSERT 0 1
sso_db=# select * from users;
 id |       name
----+-------------------
  1 | blog.68hub.com
  2 | blog.happyhack.cn
(2 rows)

sso_db=# insert into users (id,name) values (3,'blog.68hub.com');
INSERT 0 1
sso_db=# INSERT INTO users (name) values ('这个语句会和上一个语句的id冲突');
ERROR:  duplicate key value violates unique constraint "users_pkey"
DETAIL:  Key (id)=(3) already exists.

sso_db=# alter sequence users_id_seq restart with 4;
ALTER SEQUENCE
sso_db=# INSERT INTO users (name) values ('只有手动更新sequence的值之后才可以');
INSERT 0 1
sso_db=# select * from users;
 id |                name
----+------------------------------------
  1 | blog.68hub.com
  2 | blog.happyhack.cn
  3 | blog.68hub.com
  4 | 只有手动更新sequence的值之后才可以
(4 rows)

列出所有的sequence

SELECT c.relname FROM pg_class WHERE c.relkind = 'S';

查询user_id_seq的最后一个值，和递增值

SELECT last_value, increment_by FROM user_id_seq;

得到user_id_seq下一个值

SELECT nextval('user_id_seq');

当执行nextval()之后，可以使用currval()来查看当前值

SELECT currval('user_id_seq');

大小写问题

如果使用驼峰法命名库名、表名、别名。在使用的时候需要加上双引号"，否则会找不到对应参数。建议全局使用小写。

PostGreSQL按日期排序

Mysql中如果需要按一个日期字段排序，ORDER BY updated_at DESC 默认 null 值是在最后的。而PostGreSQL中 null值却在最前面。需要使用 NULLS FIRST | LAST 子句来约束。

SELECT id,user_name,updated_at FROM users WHERE id > ? ORDER BY updated_at DESC NULLS LAST;

分组问题

常见错误: must appear in the GROUP BY clause or be used in an aggregate function

select sum(`score`) as total_score,user_name,user_class from user group by user_name, user_class;

这种SQL语句在Mysql中是没有问题的，但是在PostGreSQL中就会出现报错。解决此问题可以使用子查询加join链接解决.

SELECT m.user_name,m.user_class,n.total_score
FROM (
    SELECT user_name,SUM(score) AS total_score
    FROM users
    GROUP BY user_name
) n 
JOIN users m 
ON m.user_name = n.user_name;

参考

http://www.postgres.cn/docs/12/datatype-numeric.html

Mysql转PostGreSQL常见问题

目录

ID自增问题

大小写问题

PostGreSQL按日期排序

分组问题

参考