Category Archives: PostgreSQL

Return format json

select a.f_name, array_to_json(array(select d_name
from hid_department
where d_code_induk=a.f_code AND d_name ILIKE ‘%Cari%’)) as department
from hid_faculty a
where f_code=’xxxxx’ AND f_name ILIKE ‘%Cari%’;

Return format string

select f_name, array_to_string(array(select d_name
from hid_department
where d_code_induk=’xxxxx’),’|’) as department
from hid_faculty
where f_code=’xxxxx’;

Tambah tahun, bulan, hari dalam tarikh

Tambah 5 tahun : select now() + INTERVAL ‘5’ year;

Tambah 5 bulan : select now() + INTERVAL ‘5’ month;

Tambah 5 hari : select now() + INTERVAL ‘5’ day;

Tambah tahun, bulan & hari : select now() + INTERVAL ‘5’ year + INTERVAL ‘5’ month + INTERVAL ‘5’ day;


Create auto-increment field

CREATE TABLE user_roles (
“ur_id” int4 serial NOT NULL,
“ur_ud_icno” varchar(25) COLLATE “default”,
“ur_rrr_id” int2,
“ud_id” int4,
“ur_status” int2 DEFAULT 1)

Alter column with change type

ALTER TABLE web_ip_dateupdate ALTER COLUMN ip_filedyear TYPE date USING (to_date(ip_filedyear,’YYYY-MM-DD’)::date);

ALTER TABLE web_ip_dateupdate ALTER COLUMN ip_id TYPE integer USING (ip_id::integer);