PostgreSQL基础知识
1. PostgreSQL日常命令
# 常用SQL
查看当前用户: SELECT current_user;
查看所有用户: SELECT * FROM pg_user;
查看所有数据库: SELECT * FROM pg_database;
修改库的所有者: ALTER DATABASE mydb OWNER TO newowner;
修改表的所有者: ALTER TABLE tablename OWNER TO newowner;
如何连接到PostgreSQL数据库: psql -U <username> -d <database name> -h <host> -p <port>
设置自增主键序列从当前最大值开始: select setval('your_id_seq', (select max(id) from your_table))
# 常用命令
\c mydatabase myuser 以myuser用户连接到mydatabase
\l[+] [PATTERN] list databases
\dt[S+] [PATTERN] list tables
\d[S+] NAME describe table, view, sequence, or index
\dp [PATTERN] list table, view, and sequence access privileges
\df[anptw][S+] [FUNCPTRN [TYPEPTRN ...]] list [only agg/normal/procedure/trigger/window] functions
\x [on|off|auto] toggle expanded output (currently off)
\password [USERNAME] securely change the password for a user
# 基础知识
PostgreSQL的系统库: postgres, template0和template1
2. PostgreSQL小tips
- 保存JSON字符串时, 总是倾向于使用jsonb而不是json类型
In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys.
https://www.postgresql.org/docs/current/datatype-json.html - 标准url一般是: jdbc:postgresql://127.0.0.1:5432/rob?TimeZone=Asia/Shanghai&stringtype=unspecified
