PostgreSQL 操作

矫情吗；* 2022-03-18 01:58 354阅读 0赞

### 命令行登陆 ###

两步：

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:

\\d+ 显示更多内容：

还可以使用 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);

输出结果：

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