PostgreSQL 基础教程

插入数据

假设users表只有first_name,last_name和email这三个列

insert into users
values ('John', 'Doe', 'john@doe.com');

你也可以指定需要插入的列，但前提是其他列可以为空

insert into users (first_name)
values ('John');

为列插入json数据

insert into users (preferences)
values ('{ "beta": true }');

如果插入行会违反唯一约束，则可以使用Postgres'on conflict子句指定发生这种情况时要执行的操作

-- 如果已经记录了这个webhook，那么什么都不做
insert into stripe_webhooks (event_id)
values ('evt_123')
on conflict do nothing;

您还可以在 Postgres 中执行upserts（更新或插入）

-- 假设email列是唯一索引
insert into users (email, name)
values ('john@doe.com', 'Jane Doe')
on conflict (email) do update set name = excluded.name; -- excluded.name指的是'Jane Doe'

更新数据

-- 更新所有行
update users set updated_at = now();

-- 更新指定行
update users set updated_at = now() where id = 1;

删除数据

delete from users where id = 1;

创建表

以下是创建users表的示例：

create table users
(
    id          serial primary key,         -- 自增长ID
    name        character varying,          -- 字符串列，为指定长度
    preferences jsonb,                      -- JSON列
    created_at  timestamp without time zone -- 始终以utc格式存储
);

为列指定非空约束和默认值

create table users
(
    id     serial primary key,
    name   character varying not null,
    active boolean default true
);

创建临时表，这些表将在会话期间保持不变，会话结束之后将会被删除

-- 创建一个名为`scratch_users`的临时表，只有一个`id`列
create temporary table scratch_users
(
    id integer
);

-- 或者根据select的输出创建临时表
create temp table active_users
as
select *
from users
where active is true;

删除表

drop table funky_users;

重命名表

alter table events rename to events_backup;

清空表

truncate my_table

如果你有一个ID自增列，并且想重新启动它的序列（即重新启动 ID 1）

truncate my_table restart identity

复制表

create table dupe_users as (select *
                            from users);

只创建表结构不添加数据

create table dupe_users as (select *
                            from users) with no data;

添加列

向 users 表中添加 created_at 时间戳列的示例

alter table users
    add column created_at timestamp without time zone;

添加非空约束的字符串（varchar）列，表中无数据时可执行

alter table users
    add column bio character varying not null;

添加具有默认值的布尔列

alter table users
    add column active boolean default true;

删除列

alter table users
    drop column created_at;

重命名列

alter table users
    rename column registered_at to created_at;

为列添加默认值

-- Example: 订单的默认总计为0
alter table orders
    alter column total_cents set default 0;

-- Example: 默认Items可用
alter table items
    alter column available set default true;

列中删除默认值

假设orders.total_cents有一个默认值，这将删除未来插入的默认值

alter table orders
    alter column total_cents drop default;

为列添加一个非空约束

alter table users
    alter column email set not null;

删除列中的非空约束

alter table users
    alter column email drop not null;

创建索引

在拥有大量数据时，创建正确的索引对于高性能查询至关重要。

create index concurrently 'index_created_at_on_users' on users using btree (created_at);

为多列创建索引

create index concurrently 'index_user_id_and_time_on_events' on events using btree (user_id, time);

防止数据重复的唯一索引

create unique index concurrently 'index_stripe_event_id_on_stripe_events' on stripe_events using btree (stripe_event_id);

创建满足特定条件行的索引

create index concurrently 'index_active_users' on users using btree (created_at) where active is true;

还可以拥有唯一的部分索引，例如，假设每个用户只能拥有一张有效的信用卡

-- 这将阻止用户拥有多个激活的银行卡
create unique index concurrently 'index_active_credit_cards' on credit_cards using btree(user_id) where active is true;

删除索引

drop index index_created_at_on_users;

创建视图

create or replace view enriched_users as (
    select *
    from users
             inner join enrichments on enrichments.user_id = users.id
);

删除视图

drop view enriched_users;

按照时间进行分组

如果你想按分、时、日、周等进行分组，需要使用PostgreSQL函数date_trunc

select date_trunc('minute', created_at), -- or hour, day, week, month, year
       count(1)
from users
group by 1

截断时间戳

select date_trunc('second', now()) -- or minute, hour, day, month

将 UTC 转换为本地时区

如果您有一个没有时区列的时间戳，并且您将时间戳存储为UTC，则需要告诉 PostgreSQL`，然后告诉它将其转换为您的本地时区。

select created_at at time zone 'utc' at time zone 'america/los_angeles'
from users;

为了更简洁，您还可以使用时区的缩写：

select created_at at time zone 'utc' at time zone 'pst'
from users;

要查看 PostgreSQL 支持的时区列表：

select *
from pg_timezone_names;

类型转换

-- 文本转换诶布尔值
select 'true'::boolean;

-- 浮点数转换为整型
select 1.0::integer;

-- 整型转换为浮点数
select '3.33'::float;
select 10 / 3.0;
-- 整型和浮点数进行计算将返回浮点数

-- 文本转换为整型
select '1'::integer;

-- 文本转换诶时间戳
select '2018-01-01 09:00:00'::timestamp;

-- 文本转换为日期
select '2018-01-01'::date;

-- Cast text to interval
select '1 minute'::interval;
select '1 hour'::interval;
select '1 day'::interval;
select '1 week'::interval;
select '1 month'::interval;

从 CSV 中导入数据

-- Assuming you have already created an imported_users table
-- Assuming your CSV has no headers
\copy imported_users from 'imported_users.csv' csv;

-- If your CSV does have headers, they need to match the columns in your table
\copy imported_users from 'imported_users.csv' csv header;

-- If you want to only import certain columns
\copy imported_users (id, email) from 'imported_users.csv' csv header;

Coalesce

select
  day,
  tickets
from stats;
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |   null
 2018-01-03 |     3

使用coalesce函数，该函数返回它传递的第一个非null参数

select
  day,
  coalesce(tickets, 0)
from stats;
    day     | tickets
------------+-------
 2018-01-01 |     1
 2018-01-02 |     0
 2018-01-03 |     3

Case 语句

select
  case
    when precipitation = 0 then 'none'
    when precipitation <= 5 then 'little'
    when precipitation > 5 then 'lots'
    else 'unknown'
  end as amount_of_rain
from weather_data;

列使用 filter 进行计数

select
  count(1), -- Count all users
  count(1) filter (where gender = 'male'), -- Count male users
  count(1) filter (where beta is true) -- Count beta users
  count(1) filter (where active is true and beta is false) -- Count active non-beta users
from users

创建用户及授权

create database DB_NAME;
create user DB_USER with encrypted password 'USER_PASSWORD';
grant all privileges on database DB_NAME to DB_USER;

\du
alter role <user-name> superuser;