PostgreSQL 操作

命令行登陆

两步：

sudo -i -u postgres
psql

一步：

sudo -u postgres psql

使用 pgcli，登录方法也是类似

sudo -i -u pgadmin
pgcli

查看 Postgres 版本信息

命令：

psql --version

登陆数据库后，还可以使用查询：

SELECT version();

创建新角色（role）

初始时，只有一个角色：postgres

切换成 postgres 用户后，就可以用下面的命令创建新用户：

postgres@VM-0-9-ubuntu:~$ createuser --interactive

同样，在其他用户下，可以直接使用命令：

sudo -u postgres createuser --interactive

修改密码

登录数据库，修改密码

ALTER USER davide WITH PASSWORD 'hu8jmn3';

创建数据库

在Linux命令行下！使用命令创建数据库：

postgres@VM-0-9-ubuntu:~$ createdb mydb

PostgreSQL 默认是角色可以访问同名数据库！

Linux user

a Linux user with the same name as your Postgres role and database.
这三者同名：Linux 账户、Prostgres 角色、Postgres 数据库

创建 Linux 账户：

sudo adduser postg1

创建角色（用postgres角色登陆）：

createuser postg1

创建数据库（用postgres角色登陆）：

createdb postg1

这样，使用 postg1 用户登陆，就可以使用 psql 命令来访问数据库。

连接数据库

方法一：

psql -d postgres

方法二，启动 psql 后切换：

\c dbname

查看当前连接数据库的信息

使用命令：

postg1=> \conninfo
You are connected to database "postg1" as user "postg1" via socket in "/var/run/postgresql" at port "5432".``

数据库 URL

数据库 url：

postgres://{db_username}:{db_password}@{host}:{port}/{db_name}

例如:

postgres://olawalequest@127.0.0.1:5432/reflection_db

显示全部表

\dt

显示表结构 \d, \d+

\d:

postg1=> \d playg
                                   Table "public.playg"
 Column |         Type          | Collation | Nullable |              Default              
--------+-----------------------+-----------+----------+-----------------------------------
 id     | integer               |           | not null | nextval('playg_id_seq'::regclass)
 color  | character varying(25) |           | not null | 

\d+ 显示更多内容：

postg1=> \d+ playg
                                                   Table "public.playg"
 Column |         Type          | Collation | Nullable |              Default              | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id     | integer               |           | not null | nextval('playg_id_seq'::regclass) | plain    |              | 
 color  | character varying(25) |           | not null |                                   | extended |              | 

还可以使用 SQL 标准语句：

postg1=> select column_name, data_type, character_maximum_length from information_schema.columns where table_name = 'playg';
 column_name |     data_type     | character_maximum_length 
-------------+-------------------+--------------------------
 id          | integer           |                         
 color       | character varying |                       25
(2 rows)

修改用户密码

登陆：

sudo -u user_name psql db_name

修改密码：

ALTER USER user_name WITH PASSWORD 'new_password';

另外一个修改密码的方法：

ALTER ROLE username WITH PASSWORD 'password';

删除表的一栏

ALTER TABLE table_name 
DROP COLUMN column_name;

增加表的一栏

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

给表栏增加注释

comment on column TABLE.userid is 'The user ID';

修改栏的默认值

ALTER TABLE ONLY users ALTER COLUMN lang SET DEFAULT 'en_GB';

ONLY 的解释：

“If ONLY is specified before the table name, only that table is altered. If ONLY is not specified, the table and all its descendant tables (if any) are altered” postgresql.org/docs/9.3/static/sql-altertable.html

删除栏的默认值

ALTER TABLE <table> ALTER COLUMN <column> DROP DEFAULT;

时间值默认当前时间

ALTER TABLE mytable ADD COLUMN created_at TIMESTAMP;
ALTER TABLE mytable ALTER COLUMN created_at SET DEFAULT now();

显示注释

SELECT c.table_schema,c.table_name,c.column_name,pgd.description
FROM pg_catalog.pg_statio_all_tables as st
  inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
  inner join information_schema.columns c on (pgd.objsubid=c.ordinal_position
    and  c.table_schema=st.schemaname and c.table_name=st.relname);

输出结果：

table_schema | table_name | column_name |     description
--------------+------------+-------------+----------------------
 public       | events     | uid         | id from wechat_users
 public       | events     | status      | 1.start 2.end
(1 row)

https://stackoverflow.com/a/4946306/3054511