Postgresql简记
星期三, 9月 11, 2024 | 2分钟阅读 | 更新于 星期五, 2月 14, 2025
学习pg时遇到的一些问题以及技巧
1、null无法与常用的数进行比较
select id from table where ifnull(score,0)!=100 #使用ifnull将其转换为0
2、去重
distinct是通过查询的结果来去除重复记录（不考虑用户的查询条件），因此会出现全表扫描，那么在大数据量的场景下，会消耗更多的内存和IO资源，而group by是根据用户的查询条件过滤后的结果集再去操作，因此消耗更少的内存和IO资源。
3、mysql的左连接
MySQL中的left join是一种连接方式，它以左表为基准，返回左表中所有的行，同时返回右表中与左表匹配的行。如果右表中没有匹配的行，则用NULL填充。
select unique_id, name from Employees left join EmployeeUNI on Employees.id = EmployeeUNI.id;
4、PG不支持datediff
如下的语句mysql可以执行，但pg不支持
select a.id from weather a,weather b where a.temperature > b.temperature and datediff(a.recordDate,b.recordDate)=1;
pg可以使用date_part实现相同的功能
select a.id from weather a,weather b where a.temperature > b.temperature and DATE_PART('day',a.recordDate) - DATE_PART('day',b.recordDate)=1;
要解决跨月问题，你可以使用 DATE_SUB 或 INTERVAL 来直接比较日期，而不是仅仅比较天数
SELECT a.id
FROM weather a
JOIN weather b
ON a.recordDate = b.recordDate + INTERVAL '1 day'
WHERE a.temperature > b.temperature;
5、PG和MYSQL的if语句
mysql
if(条件,成立结果，不成立结果);
pg
case when activity_type='end' then timestamp else -timestamp end
6、join后要衔接on
select name,bonus from Employee left join Bonus on Employee.empId = Bonus.empId where ifnull(Bonus.bonus,0)<1000;
7、PG升级数据库后处理
警告: database “MyTestDB” has a collation version mismatch 描述: The database was created using collation version 2.39, but the operating system provides version 2.40. 提示: Rebuild all objects in this database that use the default collation and run ALTER DATABASE “MyTestDB” REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
sudo -u postgres psql -U postgres -d MyTestDB
REINDEX DATABASE "MyTestDB";
ALTER DATABASE "MyTestDB" REFRESH COLLATION VERSION;
8、PG实现字符大小转换使用拼接的方法
| user_id | name |
|---|---|
| 1 | aLice |
| 2 | bOb |
查询获取如下的表格，可以使用concat
| user_id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
select user_id ,concat(upper(left(name,1)),lower(right(name,length(name)-1))) as name from Users;
9、PG的正则表达式
pg也是使用 % 代替所有值使用_作为一个字母的通配符
select patient_id,patient_name,conditions from Patients where conditions like 'DIAB1%' or conditions like '% DIAB1%'
10、select特性
select可以实现搜索到空表返回null,因此可以使用双重select实现直接添加结果为null 如
select num from MyNumbers group by num having count(num) = 1 order by num desc limit 1
将会返回一个空值但不会被表示为null
select(select num from MyNumbers group by num having count(num) = 1 order by num desc limit 1) as num;
控制将会返回一个null
11、is用法
1、is可以判断值是否相等，特别用在字符串或null值上
select name from Customer where referee_id != 2 or referee_id is null;
2、is not可以实现相反的效果
select name from Customer where referee_id != 2 or referee_id is not null;
12、Limit
1、pg的limit严格遵守sql规范，基本语法如下：
LIMIT 和 OFFSET 子句用于限制返回结果的行数和指定从哪一行开始。正确的语法应该是将 LIMIT 和 OFFSET 分开，如下所示：
SELECT * FROM test_a_description ORDER BY age LIMIT 2 OFFSET 1;
LIMIT 2 表示返回结果的行数为 2，OFFSET 1 表示从结果集的第二行开始返回结果。
MYSQL则是limit m,n表示：从第m+1条开始，取n条数据；