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

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

语句

插入更新

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

CREATE  TABLE IF NOT EXISTS  "user" (
  id SERIAL PRIMARY KEY,
  name  TEXT UNIQUE,
  age INTEGER,
  sex BOOLEAN,
  create_at TIMESTAMP DEFAULT  CURRENT_TIMESTAMP
)

我们查看其DDL

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()
);

我们插入一些数据

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

我们利用CREATE TABLE AS复制。

CREATE TABLE user2 AS SELECT * FROM "user";

查看表数据

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

create table user2
(
	id integer,
	name text,
	age integer,
	sex boolean,
	create_at timestamp
);

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

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

我们查看表数据

select  * FROM user3;
-- 

查看DDL

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中插入一些数据

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”继续的。

查看表大小

查看一个表
select pg_size_pretty(pg_relation_size('table_name'));
查出所有表并按大小排序
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
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会导致行被排序。

参考