PostgreSQL Cheatsheet

select array_to_string(array[1,2,3], ',');
# 123

select cast(id as varchar) from student;

select to_char(write_date, 'yyyy-MM-dd hh24:MI:ss') from student

select
concat('student:id=', cast(s.id as varchar), 'name:',s.name, 'class:',ci.name)
from student as s

select substring('abcd',1,2); 
# ab

select
ROW_NUMBER() OVER (ORDER BY id desc) AS sequence_number,
id,name
from
student

select array_agg(name order by name asc) from student

SELECT array(SELECT "name" FROM student);

SELECT ceil(35.7)
#36

SELECT floor(35.7)
#35

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

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

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

select date_part('hour',now()),date_part('minute',now()),date_part('month',now());
date_part | date_part | date_part 
-- -----------+-----------+-----------
--         11 |        10 |        9