PostgreSQL 中建立名为 user 的表

CREATE TABLE IF NOT EXISTS "user" (
  id SERIAL,
  name varchar(20) DEFAULT NULL,
  password varchar(20) DEFAULT NULL,
  status integer DEFAULT NULL,
  PRIMARY KEY (id)
);

注意必须要加双引号

因为这个 "user" 会与 PostgreSQL 内置的用户表 user 混淆。

比如

dbtest=# select * from user;
 current_user
--------------
 postgres
(1 row)

试试看 select * from "user";

dbtest=# select * from "user";
 id | name | password | status
----+------+----------+--------
(0 rows)


因此，最好不要起 user 这个名字，我们将它改为 test_user

dbtest=# alter table "user" rename to test_user;
ALTER TABLE

但是原来的 "user" 表的 id sequence 还在.

dbtest=\d

public | user_id_seq            | sequence | postgres

如何删除 user_id_seq ? 或者将它改名?

dbtest=# alter table user_id_seq rename to test_user_id_seq;
ALTER TABLE

仅仅改名字还不行，
dbtest=# select * from test_user_id_seq;
 sequence_name | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
---------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 user_id_seq   |          1 |           1 |            1 | 9223372036854775807 |         1 |           1 |       0 | f         | f
(1 row)

要将其中的 sequence_name 的值也要改.

dbtest=# update test_user_id_seq set sequence_name='test_user_id_seq' where sequence_name='user_id_seq';
ERROR:  cannot change sequence "test_user_id_seq"

事实上不使用 update 这个命令, 而是 alter

ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
    [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
    [ RESTART [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name SET SCHEMA new_schema

dbtest=# drop table test_user_id_seq;
ERROR:  "test_user_id_seq" is not a table
HINT:  Use DROP SEQUENCE to remove a sequence.

索性先删除此序列吧
dbtest=$ drop sequence test_user_id_seq CASCADE;
dbtest=# drop table test_user cascade;
NOTICE:  drop cascades to constraint useid on table info
DROP TABLE
dbtest=# drop table info;
DROP TABLE