Version Information

show server_version;

Insert Operations

Bulk Insert from SELECT

Insert records from one table into another using SELECT:

INSERT INTO target_table
(uuid_column, relation_id, relation_name)
SELECT 
    replace(cast(uuid_generate_v4() AS VARCHAR), '-', ''),
    id, 
    name
FROM source_table;

Auto-Increment Primary Key

1. Create a sequence:

CREATE SEQUENCE test_seq_id
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

2. Set the column default to the sequence's next value:

ALTER TABLE test_table ALTER COLUMN id SET DEFAULT nextval('test_seq_id');

Query Operations

Converting Columns to Rows

Emulate GROUP_CONCAT functionality:

SELECT 
  array_to_string(array_agg(DISTINCT code), ',') AS grouped_codes,
  string_agg(code, ',') AS all_codes
FROM code_gxs
GROUP BY code_lev2;

Null Value Sorting

By default, NULL values are treated as largest value. In descending order, NULLs appear first. Use NULLS LAST to place NULL values at the end. Similarly, use NULLS FIRST in asecnding order when needed.

SELECT * 
FROM test_table
ORDER BY column_a DESC NULLS LAST,
         column_b ASC NULLS FIRST;

Functions

Agrgegate Functions

SELECT zjhm, array_to_string(array_agg(DISTINCT asjbh), ',')
FROM table1
GROUP BY zjhm;

Window Functions

Calculate total leads, valid leads, and invalid leads. Lead validity is determined by the most recent feedback.

SELECT
    SUM(CASE WHEN b.sfyx = '0' THEN 1 ELSE 0 END) AS invalid_count,
    SUM(CASE WHEN b.sfyx = '1' THEN 1 ELSE 0 END) AS valid_count,
    COUNT(*) AS total_count
FROM leads a
LEFT JOIN (
    SELECT lead_id, sfyx,
    ROW_NUMBER() OVER (PARTITION BY lead_id ORDER BY feedback_time DESC) AS row_num
    FROM lead_feedback
) b ON a.lead_number = b.lead_id
WHERE b.row_num = 1 OR b.row_num IS NULL;

Date and Time

Current Timestamp

SELECT NOW();
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_DATE;
SELECT CURRENT_TIME;

Convert to String

SELECT TO_CHAR(timestamp_column, 'yyyy-mm-dd hh24:mi:ss');

Note: hh defaults to 12-hour format. Use hh12 or hh24 for explicit formatting.

Interval Arithmetic

-- Subtract 1 second from current time
SELECT TO_CHAR(NOW() - INTERVAL '1 second', 'yyyy-mm-dd');

-- Subtract 1 minute
SELECT TO_CHAR(NOW() - INTERVAL '1 minute', 'yyyy-mm-dd');

-- Subtract 1 hour
SELECT TO_CHAR(NOW() - INTERVAL '1 hour', 'yyyy-mm-dd');

-- Yesterday
SELECT TO_CHAR(NOW() - INTERVAL '1 day', 'yyyy-mm-dd');

-- Last week
SELECT TO_CHAR(NOW() - INTERVAL '1 week', 'yyyy-mm-dd');

-- Last month
SELECT TO_CHAR(NOW() - INTERVAL '1 month', 'yyyy-mm-dd');

-- Last year
SELECT TO_CHAR(NOW() - INTERVAL '1 year', 'yyyy-mm-dd');

date_trunc

-- Start of current year
SELECT date_trunc('year', NOW());

-- Start of current month
SELECT date_trunc('month', NOW());

-- Start of current day
SELECT date_trunc('day', NOW());

-- Start of current hour
SELECT date_trunc('hour', NOW());

-- Start of current minute
SELECT date_trunc('minute', NOW());

-- Start of current second
SELECT date_trunc('second', NOW());

Extract Date Parts

SELECT date_part('year', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('month', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('day', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('hour', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('minute', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('second', TIMESTAMP '2001-02-16 20:38:40');
SELECT date_part('week', TIMESTAMP '2001-02-16 20:38:40');

-- Works with various timestamp inputs
SELECT date_part('year', CURRENT_TIMESTAMP);
SELECT date_part('year', NOW());
SELECT date_part('year', TIMESTAMP '2022-02-28');
SELECT date_part('year', '2022-02-28'::timestamp);
SELECT date_part('year', timestamp_column)
FROM your_table;

Arithmetic with Date Components

SELECT COUNT(1) 
FROM tb_st_asj 
WHERE record_date BETWEEN 
    TO_TIMESTAMP(CONCAT('2021-10-01', ' 00:00:00'), 'yyyy-MM-dd hh24:mi:ss') + INTERVAL '-1 year'
    AND TO_TIMESTAMP(CONCAT('2021-10-31', ' 23:59:59'), 'yyyy-MM-dd hh24:mi:ss') + INTERVAL '-1 year'
    AND status_flag = '0';

-- Subtracting one month from Nov 30 returns Oct 30 (not Oct 31)
SELECT TO_TIMESTAMP(
    CONCAT('2021-11-30', ' 23:59:59'), 
    'yyyy-MM-dd hh24:mi:ss'
) + INTERVAL '-1 month';

Get Last Second of Previous Month

SELECT date_trunc('month', 
    TO_TIMESTAMP('2021-09-30 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
) + INTERVAL '-1 second';

Calculate Days in a Specific Month

SELECT date_part('days', 
    date_trunc('month', 
        TO_TIMESTAMP(CONCAT('2021-09-25', ' 00:00:00'), 'yyyy-MM-dd hh24:mi:ss')
    ) + INTERVAL '1 month' - INTERVAL '1 day'
);

String Operations

Convert String to Number

SELECT TO_NUMBER('1', '9');

Substring Extraction

SELECT SUBSTRING(identity_number, 17, 1);

Mathematical Operations

Modulo

-- 9 modulo 2 equals 1
SELECT MOD(9, 2);

Null Handling

COALESCE for Null Values

Return a default value when NULL is encountered:

SELECT lead_id, COALESCE(COUNT(*), 0)
FROM lead_feedback;