一、安装

使用 docker 安装：

$ docker pull postgres:alpine

二、启动

起 docker 容器：

$ docker run --name some-postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres:alpine

默认用户：postgres

默认密码：mysecretpassword

或者通过 psql，验证不成功：

$ docker run -it --rm --network some-network postgres:alpine psql -h some-postgres -U postgres:alpine

报错：

docker: Error response from daemon: network some-network not found.

使用容器连接：

$ docker run --rm -t -i --link some-postgres:pg postgres:alpine bash

在客户端 docker run 中直接使用 -link remote_name:local_alias 使容器连接到另一个容器端口。比如上面的例子就是使容器 some-postgres 连接到另一个容器 pg。

然后，通过 psql：

$ psql -h $PG_PORT_5432_TCP_ADDR -p $PG_PORT_5432_TCP_PORT -d postgres -U postgres --password

注意用户要是之前的默认用户 postgres

然后会出现输入密码，输入之前设置的默认密码：mysecretpassword

以下界面表示成功连接到 PostgreSQL：

psql (13.3)
Type "help" for help.

postgres=#

三、PSQL 操作数据库

psql命令

Object Type	All Privileges	Default `PUBLIC` Privileges	psql Command
`DATABASE`	`CTc`	`Tc`	`\l`
`DOMAIN`	`U`	`U`	`\dD+`
`FUNCTION` or `PROCEDURE`	`X`	`X`	`\df+`
`FOREIGN DATA WRAPPER`	`U`	none	`\dew+`
`FOREIGN SERVER`	`U`	none	`\des+`
`LANGUAGE`	`U`	`U`	`\dL+`
`LARGE OBJECT`	`rw`	none
`SCHEMA`	`UC`	none	`\dn+`
`SEQUENCE`	`rwU`	none	`\dp`
`TABLE` (and table-like objects)	`arwdDxt`	none	`\dp`
Table column	`arwx`	none	`\dp`
`TABLESPACE`	`C`	none	`\db+`
`TYPE`	`U`	`U`	`\dT+`

中文补充

操作	命令
查看所有数据库	`# \l`
切换数据库	`# \c dbname`
查看当前数据库中的所有表	`# \d`
查看指定表中的字段	`# \d dbtable`
查看表信息	`# \d+ dbtable`
查看所有用户	`# \du`
查看表结构	`# \dn`
查看索引	`# \di`

创建表

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

删除表

DROP TABLE my_first_table;

设置默认值

如果不设置默认值，默认为 null。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric DEFAULT 9.99
);

创建一个已生成的列

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

限制

check
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY

修改表

增加列

ALTER TABLE products ADD COLUMN description text;

默认值为 null。

移除列

ALTER TABLE products DROP COLUMN description;

增加限制

表限制

ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

非空限制

不能以表限制的形式写，需要：

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

移除限制

所有限制类型（除 not null）

ALTER TABLE products DROP CONSTRAINT some_name;

not null

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

改变列的默认值

不会影响已存在的值，只会影响默认值和未来插入的值

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

移除任何默认值

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

改变列的数据类型

只有列中的每一个已存在数据可以转变为新的数据类型，才会成功

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

重命名列

ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

ALTER TABLE products RENAME TO items;

更新表里的某一个值

update items set name = 'book';

插入数据

INSERT INTO tablename(xxx, xxx) VALUES('xxx', 'xxx');

四、function

函数可以重载，根据参数不同匹配同名不同函数。

求某一列值为 'xxx' 的所有和：

代码1（有错）：

create function sum_price1() returns numeric(10,2)
as 'select sum(price) from items where name='book';'
language sql;

应该是单引号里面不能再使用单引号。

代码2（有错）：

create function sum_price2() returns numeric(10,2) as $$
begin
return select sum(price) from items where name='book';
end; $$
language plpgsql;

报错：

ERROR:  syntax error at or near "select"
LINE 3: return select sum(price) from items where name='book';

代码3（成功）：

create or replace function sum_price(g text)
returns numeric(10,2) as $$
select sum(price) from items where name=g;
$$
language sql;

传参命名不能与表里面的字段相同（比如 sum_price(g text) 里的 g，数据库字段不能有 g），否则不能根据设置的条件查询相应数据。

已创建的 function，不能使用 replace 修改其之前的传入参数，会报错：ERROR: cannot change name of input parameter "xxx" 。

调用自定义函数

select * from sum_price('book');

五、注意

除 sql 语句外的命令大小写敏感。
sql 语句要加上分号结束。
如果表名是敏感词，如 user 等，查询表时要在 user 上添加双引号。
当一行命令没输完（指没有写 ; ），则 postgres=# 会变为 postgres-#，之后输入的命令语句和前一行都属于同一个命令。
语法查询：\h，详细了解各个参数的含义和使用，再搭配官方文档和网上的快餐式教程，了解其他人没有用到的参数。
命令行支持联想，即 Tab 键可自动补全。

PostgreSQL学习记录