PostgreSQL的个人使用笔记

发表于 更新于 分类于 阅读次数： 本文字数： 2k 阅读时长 ≈ 2 分钟

作为一个数据库小白，PostgreSQL居然是我第一个使用的数据库而不是MySQL。有的时候有些操作难免忘记，就又要到网上去查询，这里就记录一下我忘记的那些一些用到过的SQL语句吧（虽然有些可能感觉挺蠢的orz。

添加和删除UNIQUE约束

我们现在使用一下语句创建了一张表，用来记录app在某一天的使用情况，

1
2
3
4
5
6
7
create table app_usage_info (
	day date,
	app_name text,
	package_name text,
	usage_time interval,
	usage_count integer
);

然而，创建好之后我发现，我需要插入的数据在特定的一天中，一个app只能插入一组数据，所以我们需要添加UNIQUE约束：

1
alter table app_usage_info add constraint unique_app_one_day unique(day,app_name);

想要删除这一个约束，我们可以使用一下语句：

1
alter table app_usage_info drop constraint unique_app_one_day;

如果我们在建表的时候就添加约束，那么应该这样写

1
2
3
4
5
6
7
8
create table app_usage_info (
	day date,
	app_name text,
	package_name text,
	usage_time interval,
	usage_count integer,
    constaint unique_app_one_day unique(day,app_name)
);

更新操作UPSERT

UPSERT就是说当我们在INSERT的时候，发现与约束冲突，这个时候我们就可以对原有的数据进行更新而不是插入。例如我们对上面的表进行UPSERT，下面的SQL语句中excluded中的内容就是发生冲突的数据，我们使用这个数据对其进行更新即可。

1
2
3
insert into app_usage_info values ('2021-07-16','QQ','com.tencent.mobileqq','03:22:44','20')
on conflict(day,app_name) do update 
set "usage_time"=excluded.usage_time,"usage_count"=excluded.usage_count;

删除某个字段

同样的，我们创建一个表，用来记录app的活动情况，

1
2
3
4
5
6
create table DB_usage_events (
	app_name text,
	package_name text,
	start_time timestamptz,
	end_time, timestamptz
);

然后，我们创建好之后发现我们可以不需要app_name这个字段，于是我们就可以用一下命令删去：

1
alter table DB_usage_events drop column if exists app_name;

设置PostgreSQL时区

在PostgreSQL中的日期/时间类型有时区之差，在使用中发现默认使用的是零时区，所以我们需要切换一下时区：

1
2
3
4
show time zone; -- 显示当前时区
select form now(); -- 显示当前时间
select * from pg_timezone_names; -- 查看支持的时区列表
set time zone 'PRC'; -- 设置成东八区 北京时间 UTC+8

上述方法是session级设置，退出psql只有就不会再生效，我们可以使用用户级配置或者数据库级配置

1
2
3
4
5
-- 用户级配置
alter role rolename set timezone='PRC';
alter role all set timezone='PRC';
-- 数据库级配置
alter database dbname set timezone='PRC';

更加详细的PostgreSQL日期/时间/时区相关可以参考这篇博文PostgreSQL时区、时间不一致、差8小时

Schema 刷新 (Schema Reloading)

在使用RESTful API的时候，如果在数据库中创建了一个函数，那么要想在RESTful API中使用这个函数，那么首先我们需要reload Schema，使用以下命令可以在不中断PostgreSQL Service服务的同时刷新Schema

1
killall -SIGUSR1 postgrest

参考Hardening PostgREST — PostgREST 7.0.1 documentation