日常 Postgres 数据库点滴记录

本文最后更新于：4 年前

Desc:Postgres数据库日常使用，插入更新，复制表，一些使用习惯。

由于经常接触数据库，但又停留在比较浅的层次，暂且没有时间也没必要去系统了解，这篇博客权当记录一些自己常用到的pg数据库语句，持续更新~

语句

插入更新

1
2
3
4
5
INSERT INTO the_table (id, column_1, column_2) 
VALUES (1, 'A', 'X'), (2, 'B', 'Y'), (3, 'C', 'Z')
ON CONFLICT (id) DO UPDATE 
  SET column_1 = excluded.column_1, 
  column_2 = excluded.column_2;

复制表

我们首先创建了一张表user

1
2
3
4
5
6
7
CREATE  TABLE IF NOT EXISTS  "user" (
  id SERIAL PRIMARY KEY,
  name  TEXT UNIQUE,
  age INTEGER,
  sex BOOLEAN,
  create_at TIMESTAMP DEFAULT  CURRENT_TIMESTAMP
)

我们查看其DDL

1
2
3
4
5
6
7
8
9
10
11
12
create table "user"
(
	id serial not null
		constraint user_pkey
			primary key,
	name text
		constraint user_name_key
			unique,
	age integer,
	sex boolean,
	create_at timestamp default now()
);

我们插入一些数据

1
INSERT INTO "user" (name, age, sex) VALUES ('xiaoming',12,TRUE),('xiaohua',18,FALSE );

我们利用CREATE TABLE AS复制。

1
CREATE TABLE user2 AS SELECT * FROM "user";

查看表数据

1
2
3
select  * FROM user2;
-- 1	xiaoming	12	true	2018-05-17 09:48:51.823616
-- 2	xiaohua		18	false	2018-05-17 09:48:51.823616

查看表DDL

1
2
3
4
5
6
7
8
create table user2
(
	id integer,
	name text,
	age integer,
	sex boolean,
	create_at timestamp
);

接着，我们用CREATE TABLE LIKE复制。

1
CREATE TABLE  user3 (LIKE "user" including constraints including indexes including comments including defaults);

我们查看表数据

1
2
select  * FROM user3;
--

查看DDL

1
2
3
4
5
6
7
8
9
10
11
12
create table user3
(
	id integer default nextval('user_id_seq'::regclass) not null
		constraint user3_pkey
			primary key,
	name text
		constraint user3_name_key
			unique,
	age integer,
	sex boolean,
	create_at timestamp default now()
);

从这个操作中你知道CREATE TABLE AS 与CREATE TABLE LIKE的区别了么？

CREATE TABLE AS

CREATE TABLE AS复制出来的表，所有约束、注释和序列都没有被拷贝，只是简单的字段拷贝,同时数据也能拷贝过来了。

CREATE TABLE LIKE

CREATE TABLE LIKE 复制的表，里面没有数据，但是你可以指定复制标的约束，索引，注释，序列。其中

  1. including constraints ：复制约束
  2. including indexes ：复制索引
  3. including comments：复制注释
  4. including defaults：复制序列

我们向user3中插入一些数据：

1
2
3
4
5
6
INSERT INTO "user3" (name, age, sex) VALUES ('xiaoxiao',12,TRUE),('dada',18,FALSE );
SELECT  * FROM user3;
-- 3	xiaoming	12	true	2018-05-17 10:06:06.637708
-- 4	xiaohua	18	false	2018-05-17 10:06:06.637708
-- 6	xiaoxiao	12	true	2018-05-17 10:06:21.236574
-- 7	dada	18	false	2018-05-17 10:06:21.236574

嗯，数据id从3开始的，这就意味着序列是接着“user”继续的。

查看表大小

查看一个表
1
select pg_size_pretty(pg_relation_size('table_name'));
查出所有表并按大小排序
1
2
3
4
5
6
7
8
SELECT 
table_schema || '.' || table_name 
AS table_full_name, pg_size_pretty(pg_total_relation_size('"' ||table_schema || '"."' || table_name || '"')) AS size
FROM 
information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')
DESC limit 20
查出所有表按大小排序并分离data与index
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

语法

比较操作符

操作符描述
<小于
>大于
<=小于等于
>=大于等于
=等于
<> or !=不等于

注意

!=操作符在分析器阶段被转换成<>。不能把!=和<>操作符实现为做不同的事。

比较操作符可以用于所有可以比较的数据类型。所有比较操作符都是双目操作符，它们返回boolean类型；类似于1 < 2 < 3的表达式是非法的（因为没有<操作符可以比较一个布尔值和3）。

习惯

  • 虽然SELECT *对于即席查询很有用，但我们普遍认为在生产代码中这是很糟糕的风格，因为给表增加一个列就改变了结果。
  • 在一些数据库系统里，包括老版本的PostgreSQL，DISTINCT的实现自动对行进行排序，因此ORDER BY是多余的。但是这一点并不是 SQL 标准的要求，并且目前的PostgreSQL并不保证DISTINCT会导致行被排序。

参考