PostgreSQL 速查表
- 參考資料
1) array_to_string：陣列轉字串
sql
select array_to_string(array[1,2,3], ',');
# 123
2) cast：型別轉換
sql
select cast(id as varchar) from student;
2’) to_char：型別轉換
sql
select to_char(write_date, 'yyyy-MM-dd hh24:MI:ss') from student
3) Concat：串接字串
sql
select
concat('student:id=', cast(s.id as varchar), 'name:',s.name, 'class:',ci.name)
from student as s
4) substring：取子字串
sql
select substring('abcd',1,2);
# ab
5) row_number()：取列號（自行定義排序）
sql
select
ROW_NUMBER() OVER (ORDER BY id desc) AS sequence_number,
id,name
from
student
6) array_agg：把多筆元素聚合成陣列
sql
select array_agg(name order by name asc) from student
7) array：把查詢結果轉成陣列
sql
SELECT array(SELECT "name" FROM student);
8) ceil(num)：向上取整數
sql
SELECT ceil(35.7)
#36
9) floor(num)：向下取整數
sql
SELECT floor(35.7)
#35
10) round(numeric,int)：四捨五入到小數第 N 位
sql
SELECT round(35.7856, 2);
-- 35.79
-- NOTE: the 2-argument form takes `numeric`, NOT `double precision`
SELECT round(35.7856::numeric, 2); -- cast when the column is float8
11) age(timestamp1,timestamp2)：兩個時間戳之間的間隔
sql
SELECT age(timestamp '2026-09-03', timestamp '1990-01-15');
-- 36 years 7 mons 19 days <- calendar-aware (years/months), not just seconds
SELECT age(timestamp '1990-01-15');
-- same, measured against current_date (one-argument form)
-- For a plain elapsed duration, subtract instead:
SELECT '2026-09-03'::timestamp - '2026-08-30'::timestamp; -- 4 days
12) date_trunc(text,timestamp)
sql
select date_trunc('day',current_timestamp),date_trunc('hour',current_timestamp);
-- date_trunc | date_trunc
-- ------------------------+------------------------
-- 2018-09-16 00:00:00+08 | 2018-09-16 11:00:00+08
13) date_part(text,timestamp)
sql
select date_part('hour',now()),date_part('minute',now()),date_part('month',now());
date_part | date_part | date_part
-- -----------+-----------+-----------
-- 11 | 10 | 9