PostgreSQL 速查表

2023-09-22 约 2267 字预计阅读 5 分钟

整理一份私人 PgSQL 速查表。

数据库操作

查看所有数据库

1
2
3
4
5
6
7
8
9
\l

或者

\l+

或者

SELECT datname FROM pg_catalog.pg_database;

列出所有的模式

1
2
3
4
5
6
7
8
9
\dn

或者

\dn+

或者

SELECT schema_name FROM information_schema.schemata;

切换数据库

1
\c <database_name>

创建数据库

1
2
3
4
5
6
7
8
SELECT rolname FROM pg_roles;

CREATE DATABASE <database_name>
WITH OWNER <role_name>
ENCODING 'UTF8'
TABLESPACE 'pg_default'
LC_COLLATE='en_US.UTF-8'
LC_CTYPE='en_US.UTF-8';

重命名数据库

1
2
3
4
5
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='old_database_name';

ALTER DATABASE <old_database_name> RENAME TO <new_database_name>;

删除数据库

1
DROP DATABASE <database_name>;

查看数据库创建语句

1
SHOW CREATE DATABASE <database_name>;

查看当前数据库

1
SELECT current_database();

重命名数据库

1
ALTER DATABASE <old_name> RENAME TO <new_name>;

删除当前数据库

1
DROP DATABASE IF EXISTS <database_name>;

数据表操作

创建表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  username CHARACTER VARYING NOT NULL DEFAULT '',
  preferences JSONB DEFAULT NULL,
  actived BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT ('now'::text)::timestamp(0) WITH TIME ZONE
);
COMMENT ON TABLE users IS '用户表';
COMMENT ON COLUMN users.id IS '主键';
COMMENT ON COLUMN users.username IS '账户名称';
COMMENT ON COLUMN users.preferences IS '偏好信息';
COMMENT ON COLUMN users.actived IS '是否激活';
COMMENT ON COLUMN users.created_at IS '创建日期时间';

删除表

1
DROP TABLE IF EXISTS <table_name> CASCADE;

重命名表

1
2
3
4
5
ALTER TABLE <old_table_name> RENAME <new_table_name>;

或者

ALTER TABLE <old_table_name> RENAME TO <new_table_name>;

截断表

此操作会清空数据表中的所有数据，请谨慎执行。

1
2
3
4
5
6
TRUNCATE TABLE <table_name>;

-- The TABLE keyword is actually optional
TRUNCATE <table_name>;

TRUNCATE <table_name> RESTART IDENTITY;

列出表模式

1
2
3
4
5
6
\d <table_name>
\d+ <table_name>

SELECT column_name,data_type,character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name='<table_name>';

列出所有表

1
2
3
\dt *.*.

SELECT * FROM pg_catalog.pg_tables;

删除表

1
DELETE TABLE <table_name>;

复制表

1
2
3
4
5
6
7
8
9
-- 复制表结构的同时复制数据
CREATE TABLE <new_table_name> AS SELECT * FROM <src_table_name>;

-- 先复制表结构，再复制表数据
CREATE TABLE <new_table_name> (LIKE <src_table_name>);
INSERT INTO <new_table_name> SELECT * FROM <src_table_name>;

-- The `with no data` here means structure only, no actual rows
CREATE TABLE <new_table_name> AS (SELECT * FROM <src_table_name>) WITH NO DATA;

列出所有数据表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
\dt

或者

\dt+

或者

SELECT table_schema,table_name
FROM information_schema.tables
ORDER BY table_schema,table_name;

列出表描述

1
2
3
4
5
6
7
8
9
\dt <table_name>;

或者

\dt+ <table_name>;

或者

\d <table_name>;

列出创建表语句

1
pg_dump -st <table_name> <database_name>

添加记录

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
INSERT INTO <table_name> (username,email) VALUES ('Tony','tony@gmail.com');

INSERT INTO <table_name> (username,email)
VALUES
  ('John','john@gmail.com'),
  ('Acme','acme@gmail.com');

INSERT INTO <table_name_1> (uname,email)
SELECT username,email
FROM <table_name_2>
WHERE id<1000;

更新记录

1
2
3
4
5
6
7
UPDATE <table_name> SET stocks=100;

UPDATE <table_name> SET stocks=100,available=true;

UPDATE <table_name> SET stocks=100,available=true WHERE id=1;

UPDATE <table_name> SET stocks=50,available=true WHERE category='Computer';

删除记录

1
DELETE FROM <table_name> WHERE id=1;

查询记录

1
SELECT * FROM <table_name> WHERE id<10;

创建视图

1
2
3
4
5
CREATE OR REPLACE VIEW <view_name> AS (
  SELECT *
  FROM <table_name> AS a
  INNER JOIN <table_name> AS b ON a.id=b.id
);

删除视图

1
DROP VIEW <view_name>;

表结构管理

添加列

1
2
3
4
5
6
7
ALTER TABLE <table_name> ADD COLUMN updated_at TIMESTAMP DEFAULT ('now'::text)::timestamp(0) WITH TIME ZONE;

ALTER TABLE <table_name> ADD COLUMN deleted_at TIMESTAMP WITHOUT TIME ZONE;

ALTER TABLE <table_name> ADD COLUMN truename CHARACTER VARYING NOT NULL DEFAULT '';

ALTER TABLE <table_name> ADD COLUMN actived BOOLEAN DEFAULT TRUE;

移除列

1
2
3
4
ALTER TABLE <table_name> DROP COLUMN <column_name>;

-- The COLUMN keyword is actually optional
ALTER TABLE <table_name> DROP <column_name>;

重命名列

1
ALTER TABLE <table_name> RENAME COLUMN <old_column_name> TO <new_column_name>;

设置列默认值

1
2
3
4
5
-- Example: <table_name> have a default stock of 0
ALTER TABLE <table_name> ALTER COLUMN stocks SET DEFAULT 0;

-- Example: <table_name> are available by default (removed optional COLUMN keyword)
ALTER TABLE <table_name> ALTER available SET DEFAULT true;

移除列默认值

1
2
3
4
5
--Example: <table_name> have a default stock of 0
ALTER TABLE <table_name> ALTER COLUMN stocks DROP DEFAULT;

--Example: <table_name> are available by default (removed optional COLUMN keyword)
ALTER TABLE <table_name> ALTER available DROP DEFAULT;

NOT NULL 约束

添加 NOT NULL 约束：

1
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET NOT NULL;

移除 NOT NULL 约束：

1
ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP NOT NULL;

DEFAULT 约束

添加 DEFAULT 约束：

1
ALTER TABLE <table_name> ALTER COLUMN <column_name> SET DEFAULT <default_value>;

移除 DEFAULT 约束：

1
ALTER TABLE <table_name> ALTER COLUMN <column_name> DROP DEFAULT;

添加索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE INDEX CONCURRENTLY idx_username ON <table_name> USING BTREE (username);

CREATE INDEX CONCURRENTLY idx_user_org_id ON <table_name> USING BTREE (user_id, org_id);

CREATE UNIQUE INDEX CONCURRENTLY udx_email ON <table_name> USING BTREE (email);

CREATE INDEX CONCURRENTLY idx_nickname ON <table_name> USING BTREE (nickname(20));

CREATE INDEX CONCURRENTLY idx_reverse_truename ON <table_name> USING BTREE (truename DESC);

CREATE INDEX CONCURRENTLY idx_total_amount ON <table_name> USING BTREE ((price * stocks));

CREATE INDEX CONCURRENTLY "idx_actived" ON <table_name> USING BTREE(created_at) WHERE actived IS TRUE;

移除索引

移除主键索引：

1
2
\d <table_name>
ALTER TABLE <table_name> DROP CONSTRAINT <xxx_pkey>;

移除其它索引：

1
DROP INDEX IF EXISTS <idx_name>;

更改主键顺序

1
ALTER SEQUENCE <table_name>.<column_name> RESTART WITH 1000;

其它操作

数组比较

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SELECT
array[1,2,3] = array[1,2,4] as compare1, -- arrays are equal
array[1,2,3] <> array[1,2,4] as compare2; -- arrays are not equal

SELECT
array[1,2,5] >= array[1,2,4] as compare1,
array[1,2,5] <= array[1,2,4,5] as compare2;

-- This reads as array['a', 'b', 'c'] contains array['a', 'b', 'b', 'a']
SELECT array['a', 'b', 'c'] @> array['a', 'b', 'b', 'a'] as contains;

-- this reads as array[1, 1, 4] is contained by array[4, 3, 2, 1]
SELECT array[1, 1, 4] <@ array[4, 3, 2, 1] as is_contained_by;

SELECT
array[1, 2] && array[2, 3] as overlap1,
array[1, 2] && array[3, 4] as overlap2;

连接字符串

1
2
3
4
5
6
7
SELECT 'Join these ' || 'strings with a number ' || 23;

SELECT first_name||' '||last_name as customer_name FROM customer LIMIT 5;

SELECT 'Null with ||' || 'will make ' || 'everything disappear' || NULL;

SELECT CONCAT('Concat() handles', NULL, ' nulls better', NULL);

创建数组

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE contacts (
	first_name VARCHAR,
	last_name VARCHAR,
	phone_numbers VARCHAR[]
);

CREATE TABLE player_scores (
	player_number INTEGER,
	round_scores INTEGER[]
);

1
2
3
4
CREATE TABLE student_scores (
	student_number INTEGER,
	test_scores DECIMAL[][]
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
CREATE TABLE contacts (
	first_name VARCHAR,
	last_name VARCHAR,
	phone_numbers VARCHAR ARRAY
);

CREATE TABLE player_scores (
	player_number INTEGER,
	round_scores INTEGER ARRAY[10]
);

插入数据到数组

1
2
3
4
5
6
7
8
9
INSERT INTO contacts (first_name, last_name, phone_numbers)
VALUES ('John', 'Doe', ARRAY ['999-876-5432','999-123-4567']);

INSERT INTO player_scores (player_number, round_scores)
VALUES (10001, ARRAY [95, 92, 96, 97, 98] );

-- multi-dimension arrays must have same array lengths for the inner dimensions
INSERT INTO student_scores (student_number, test_scores)
VALUES (20001, ARRAY [[1, 95], [2, 94], [3, 98]]);

1
2
3
4
5
6
7
8
INSERT INTO contacts (first_name, last_name, phone_numbers)
VALUES ('Bob', 'Parr', '{"555-INC-RDBL"}');

INSERT INTO player_scores (player_number, round_scores)
VALUES (10002, '{91, 92, 93, 95, 99}' );

INSERT INTO student_scores (student_number, test_scores)
VALUES (20002, '{{1, 96}, {2, 93}, {4, 97}}');

更新数组数据

覆盖数组中元素

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |   round_scores
---------------+------------------
         10001 | {95,92,96,97,98}
         10002 | {91,92,93,95,99}
(2 rows)

-- overwrite all scores for a player
UPDATE player_scores SET round_scores='{92,93,94,96,98}' WHERE player_number=10002;

-- change only the score for the second round for player 10001
UPDATE player_scores SET round_scores[2]=94 WHERE player_number=10001;

test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |   round_scores
---------------+------------------
         10001 | {95,94,96,97,98}
         10002 | {92,93,94,96,98}

元素追加到数组

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
test=# UPDATE player_scores SET round_scores=array_prepend(0, round_scores);
UPDATE 2
test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |    round_scores
---------------+--------------------
         10001 | {0,95,94,96,97,98}
         10002 | {0,92,93,94,96,98}
(2 rows)

test=# UPDATE player_scores SET round_scores=array_append(round_scores, 100);
UPDATE 2
test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |      round_scores
---------------+------------------------
         10001 | {0,95,94,96,97,98,100}
         10002 | {0,92,93,94,96,98,100}
(2 rows)

连接多个数组

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
test=# SELECT array_cat('{1, 2}', ARRAY[3, 4]) AS concatenated_arrays;
 concatenated_arrays
---------------------
 {1,2,3,4}
(1 row)

test=# SELECT 1 || array[2, 3, 4] AS element_prepend;
 element_prepend
-----------------
 {1,2,3,4}
(1 row)

test=# SELECT array[1, 2, 3] || 4 AS element_append;
 element_append
----------------
 {1,2,3,4}
(1 row)

test=# SELECT array['a', 'b', 'c'] || array['d', 'e', 'f'] AS concat_array;
 concat_array
---------------
 {a,b,c,d,e,f}
(1 row)

test=# SELECT array[1, 2] || array[[4, 5],[6, 7]] AS concat_2d_array;
   concat_2d_array
---------------------
 {{1,2},{4,5},{6,7}}
(1 row)

移除数组中元素

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |      round_scores
---------------+------------------------
         10001 | {0,95,94,96,97,98,100}
         10002 | {0,92,93,94,96,98,100}
(2 rows)

test=# SELECT array_remove(round_scores,94) AS removed_94 from player_scores;
     removed_94
---------------------
 {0,92,93,96,98,100}
 {0,95,96,97,98,100}
(2 rows)

test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |      round_scores
---------------+------------------------
         10001 | {0,95,94,96,97,98,100}
         10002 | {0,92,93,94,96,98,100}
(2 rows)

test=# SELECT array_remove(ARRAY[1,2,3,2,5], 2) AS removed_2s;
 removed_2s
------------
 {1,3,5}
(1 row)

替换数组中元素

1
2
3
4
5
test=# SELECT array_replace(ARRAY[1,2,3,2,5], 2, 10) AS two_becomes_ten;
 two_becomes_ten
-----------------
 {1,10,3,10,5}
(1 row)

元素填充到数组

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |      round_scores
---------------+------------------------
         10001 | {0,95,94,96,97,98,100}
         10002 | {0,92,93,94,96,98,100}
(2 rows)

test=# INSERT INTO player_scores (player_number, round_scores) VALUES
        (10003, array_fill(95,array[5]));
INSERT 0 1

test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |      round_scores
---------------+------------------------
         10001 | {0,95,94,96,97,98,100}
         10002 | {0,92,93,94,96,98,100}
         10003 | {95,95,95,95,95}
(3 rows)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
test=# INSERT INTO player_scores (player_number, round_scores) values
        (10004, array_fill(90,array[5],array[3]));
INSERT 0 1
test=#
test=# SELECT * FROM player_scores ORDER BY player_number;
 player_number |      round_scores
---------------+------------------------
         10001 | {0,95,94,96,97,98,100}
         10002 | {0,92,93,94,96,98,100}s
         10003 | {95,95,95,95,95}
         10004 | [3:7]={90,90,90,90,90}
(4 rows)

test=# SELECT
  round_scores[1],
  round_scores[2],
  round_scores[3]
FROM player_scores
WHERE player_number IN (10003, 10004);
 round_scores | round_scores | round_scores
--------------+--------------+--------------
           95 |           95 |           95
              |              |           90
(2 rows)

查询数组数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
test=# SELECT first_name,last_name,phone_numbers FROM contacts;
 first_name | last_name |        phone_numbers
------------+-----------+-----------------------------
 John       | Doe       | {999-876-5432,999-123-4567}
 Bob        | Parr      | {555-INC-RDBL}
(2 rows)

test=# SELECT player_number,round_scores[1] FROM player_scores;
 player_number | round_scores
---------------+--------------
         10002 |            0
         10001 |            0
         10003 |           95
         10004 |
(4 rows)

test=# SELECT * FROM player_scores WHERE round_scores[1]>=95;
 player_number |   round_scores
---------------+------------------
         10003 | {95,95,95,95,95}
(1 row)

test=# SELECT * FROM player_scores WHERE 95<any(round_scores);
 player_number |      round_scores
---------------+------------------------
         10002 | {0,92,93,94,96,98,100}
         10001 | {0,95,94,96,97,98,100}
(2 rows)

test=# SELECT * FROM player_scores WHERE 92<=all(round_scores);
 player_number |   round_scores
---------------+------------------
         10003 | {95,95,95,95,95}
(1 row)

test=# SELECT first_name,last_name,UNNEST(phone_numbers) FROM contacts;
 first_name | last_name |    unnest
------------+-----------+--------------
 John       | Doe       | 999-876-5432
 John       | Doe       | 999-123-4567
 Bob        | Parr      | 555-INC-RDBL
(3 rows)

目录

PostgreSQL 速查表

数据库操作

查看所有数据库

列出所有的模式

切换数据库

创建数据库

重命名数据库

删除数据库

查看数据库创建语句

查看当前数据库

重命名数据库

删除当前数据库

数据表操作

创建表

删除表

重命名表

截断表

列出表模式

列出所有表

删除表

复制表

列出所有数据表

列出表描述

列出创建表语句

添加记录

更新记录

删除记录

查询记录

创建视图

删除视图

表结构管理

添加列

移除列

重命名列

设置列默认值

移除列默认值

NOT NULL 约束

DEFAULT 约束

添加索引

移除索引

更改主键顺序

其它操作

数组比较

连接字符串

创建数组

插入数据到数组

更新数组数据

覆盖数组中元素

元素追加到数组

连接多个数组

移除数组中元素

替换数组中元素

元素填充到数组

查询数组数据