dml

王江南2025年12月9日小于 1 分钟

update

UPDATE t_info_teacher
SET info_teacher_id = workspace_id || '_' || job_no;

alter

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

ALTER TABLE [IF EXISTS] table_name
RENAME TO new_table_name;

sql	comment
SHOW time zone	查看数据库时区

多行转一行

string_agg(distinct plate_no, ',')

jsonb

with t as (
SELECT
  cast(detail_json->>'i' as jsonb) as data
FROM etl_job_log
WHERE project_name = '树人'
  AND job_name = 't_access_depart'
  and to_char(run_time, 'yyyy-MM-dd') = to_char(now(), 'yyyy-MM-dd')
  limit 1
) 
SELECT elem
FROM t,
LATERAL jsonb_array_elements(data) AS elem;