INSERTINTO 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
CREATETABLE IF NOTEXISTS "user" ( id SERIAL PRIMARY KEY, name TEXT UNIQUE, age INTEGER, sex BOOLEAN, create_at TIMESTAMPDEFAULTCURRENT_TIMESTAMP )
我们查看其DDL
1 2 3 4 5 6 7 8 9 10 11 12
createtable "user" ( id serial notnull constraint user_pkey primary key, name text constraint user_name_key unique, age integer, sex boolean, create_at timestampdefault now() );
createtable user2 ( id integer, name text, age integer, sex boolean, create_at timestamp );
接着,我们用CREATE TABLE LIKE复制。
1
CREATETABLE 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
createtable user3 ( id integerdefault nextval('user_id_seq'::regclass) notnull constraint user3_pkey primary key, name text constraint user3_name_key unique, age integer, sex boolean, create_at timestampdefault now() );
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 ORDERBY 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 ORDERBY total_size DESC ) AS pretty_sizes