Chon · 翀

初始化

$ initdb --locale=C --encoding=UTF-8 --username=postgres /opt/homebrew/var/postgresql@14

$ psql --host=localhost --port=5432 --username=postgres --no-password

创建用户

CREATE USER "user" WITH PASSWORD 'pass';

变更用户

ALTER USER "user" WITH PASSWORD 'pass';

创建数据库

CREATE DATABASE "database" ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' OWNER "user" TEMPLATE template0;

授权数据库

GRANT ALL PRIVILEGES ON DATABASE "database" TO "user";

SQL

获取自增值

SELECT CURRVAL(PG_GET_SERIAL_SEQUENCE('table', 'id'));

修改自增值

SELECT SETVAL(PG_GET_SERIAL_SEQUENCE('table', 'id'), (SELECT MAX("id") from "table"));