日常Postgres数据库点滴记录

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 ASCREATE 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会导致行被排序。

参考

Razeen wechat
关注微信公众好,推送最新博客哦~
谢谢老板