— 字符串连接函数
SELECT
‘Hello’ || ‘ ‘ || ‘World’ AS concat_op,
CONCAT(‘Hello’, ‘ ‘, ‘World’) AS concat_func,
CONCAT_WS(‘-‘, ‘A’, ‘B’, ‘C’, ‘D’) AS concat_ws_func,
CONCAT_WS(‘,’, ‘风哥1号’, NULL, ‘风哥2号’, NULL, ‘王五’) AS concat_ws_skip_null;

执行结果：

concat_op | concat_func | concat_ws_func | concat_ws_skip_null
—————+—————+—————-+———————
Hello World | Hello World | A-B-C-D | 风哥1号,风哥2号,王五
(1 row)

— 字符串截取函数
SELECT
SUBSTRING(‘PostgreSQL’ FROM 1 FOR 4) AS substring_1,
SUBSTRING(‘PostgreSQL’ FROM 5) AS substring_2,
SUBSTRING(‘PostgreSQL’ FROM ‘gre’) AS substring_regex,
LEFT(‘PostgreSQL’, 4) AS left_func,
RIGHT(‘PostgreSQL’, 3) AS right_func,
SPLIT_PART(‘A,B,C,D’, ‘,’, 2) AS split_part;

执行结果：

substring_1 | substring_2 | substring_regex | left_func | right_func | split_part
————-+————-+—————–+———–+————+————
Post | greSQL | gre | Post | SQL | B
(1 row)

— 大小写转换函数
SELECT
UPPER(‘hello world’) AS upper_result,
LOWER(‘HELLO WORLD’) AS lower_result,
INITCAP(‘hello world’) AS initcap_result,
LENGTH(‘PostgreSQL’) AS length_result,
BIT_LENGTH(‘A’) AS bit_length,
OCTET_LENGTH(‘测试’) AS octet_length;

执行结果：

upper_result | lower_result | initcap_result | length_result | bit_length | octet_length
————–+————–+—————-+—————+————+————–
HELLO WORLD | hello world | Hello World | 10 | 8 | 6
(1 row)

— 填充与修剪函数
SELECT
LPAD(‘123’, 10, ‘0’) AS lpad_result,
RPAD(‘123’, 10, ‘*’) AS rpad_result,
TRIM(‘ hello ‘) AS trim_result,
LTRIM(‘ hello ‘) AS ltrim_result,
RTRIM(‘ hello ‘) AS rtrim_result,
TRIM(BOTH ‘x’ FROM ‘xxxhelloxxx’) AS trim_both,
TRIM(LEADING ‘x’ FROM ‘xxxhello’) AS trim_leading;

执行结果：

lpad_result | rpad_result | trim_result | ltrim_result | rtrim_result | trim_both | trim_leading
————-+————-+————-+————–+————–+———–+————–
0000000123 | 123******* | hello | hello | hello | hello | hello
(1 row)

— 替换函数
SELECT
REPLACE(‘Hello World’, ‘World’, ‘PostgreSQL’) AS replace_result,
TRANSLATE(‘12345’, ‘123’, ‘ABC’) AS translate_result,
OVERLAY(‘PostgreSQL’ PLACING ‘SQL’ FROM 8) AS overlay_result,
OVERLAY(‘PostgreSQL’ PLACING ‘SQL’ FROM 5 FOR 4) AS overlay_result2,
REGEXP_REPLACE(‘Hello 123 World 456’, ‘\d+’, ‘NUM’, ‘g’) AS regexp_replace;

执行结果：

风哥提示：

replace_result | translate_result | overlay_result | overlay_result2 | regexp_replace
———————–+——————+—————-+—————–+——————-
Hello PostgreSQL | ABC45 | PostgreSQL | PostSQLeSQL | Hello NUM World NUM
(1 row)

— 位置与匹配函数
SELECT
POSITION(‘gre’ IN ‘PostgreSQL’) AS position_result,
STRPOS(‘PostgreSQL’, ‘gre’) AS strpos_result,
‘PostgreSQL’ LIKE ‘Post%’ AS like_result,
‘PostgreSQL’ ILIKE ‘post%’ AS ilike_result,
‘PostgreSQL’ ~ ‘^Post’ AS regex_result,
‘PostgreSQL’ ~* ‘post’ AS regex_case_insensitive;

执行结果：

position_result | strpos_result | like_result | ilike_result | regex_result | regex_case_insensitive
—————–+—————+————-+————–+————–+————————
5 | 5 | t | t | t | t
(1 row)

— 基本数学函数
SELECT
ABS(-123.45) AS abs_result,
ROUND(123.456, 2) AS round_result,
CEIL(123.45) AS ceil_result,
FLOOR(123.45) AS floor_result,
TRUNC(123.456, 2) AS trunc_result,
SIGN(-123) AS sign_result;

执行结果：

abs_result | round_result | ceil_result | floor_result | trunc_result | sign_result
————+————–+————-+————–+————–+————-
123.45 | 123.46 | 124 | 123 | 123.45 | -1
(1 row)

— 幂运算与开方
SELECT
POWER(2, 10) AS power_result,
SQRT(16) AS sqrt_result,
CBRT(27) AS cbrt_result,
EXP(1) AS exp_result,
LN(10) AS ln_result,
LOG(100) AS log_result,
LOG(2, 8) AS log2_result;

执行结果：

学习交流加群风哥QQ113257174

power_result | sqrt_result | cbrt_result | exp_result | ln_result | log_result | log2_result
————–+————-+————-+———————+——————+————+————-
1024 | 4 | 3 | 2.7182818284590452 | 2.302585092994046| 2 | 3
(1 row)

— 三角函数
SELECT
SIN(RADIANS(30)) AS sin_30,
COS(RADIANS(60)) AS cos_60,
TAN(RADIANS(45)) AS tan_45,
ASIN(0.5) AS asin_result,
ACOS(0.5) AS acos_result,
ATAN(1) AS atan_result,
DEGREES(PI()/4) AS degrees_result;

执行结果：

from oracle:www.itpux.com

sin_30 | cos_60 | tan_45 | asin_result | acos_result | atan_result | degrees_result
——————+——————+——————+——————-+——————-+——————-+—————-
0.499999999999999 | 0.50000000000000 | 0.99999999999999 | 0.523598775598299 | 1.047197551196597 | 0.785398163397448 | 45
(1 row)

— 随机数函数
SELECT
RANDOM() AS random_0_1,
FLOOR(RANDOM() * 100) AS random_0_99,
FLOOR(RANDOM() * 100) + 1 AS random_1_100,
FLOOR(RANDOM() * 50) + 50 AS random_50_100;

执行结果：

random_0_1 | random_0_99 | random_1_100 | random_50_100
——————-+————-+————–+—————
0.84729387569427 | 47 | 82 | 67
(1 row)

— 当前日期时间函数
SELECT
CURRENT_DATE AS today,
CURRENT_TIME AS current_time,
CURRENT_TIMESTAMP AS current_ts,
LOCALTIMESTAMP AS local_ts,
NOW() AS now_func,
CLOCK_TIMESTAMP() AS clock_ts,
STATEMENT_TIMESTAMP() AS stmt_ts,
TRANSACTION_TIMESTAMP() AS tx_ts;

执行结果：

today | current_time | current_ts | local_ts | now_func | clock_ts | stmt_ts | tx_ts
————-+—————-+————————-+———————–+—————————-+————————-+————————+————————
2026-04-04 | 14:30:25.123 | 2026-04-04 14:30:25.123 | 2026-04-04 14:30:25 | 2026-04-04 14:30:25.123 | 2026-04-04 14:30:25.456 | 2026-04-04 14:30:25.123| 2026-04-04 14:30:25.123
(1 row)

— 日期提取与截断
SELECT
EXTRACT(YEAR FROM CURRENT_DATE) AS year,
EXTRACT(MONTH FROM CURRENT_DATE) AS month,
EXTRACT(DAY FROM CURRENT_DATE) AS day,
EXTRACT(DOW FROM CURRENT_DATE) AS day_of_week,
EXTRACT(DOY FROM CURRENT_DATE) AS day_of_year,
EXTRACT(WEEK FROM CURRENT_DATE) AS week_of_year,
DATE_TRUNC(‘month’, CURRENT_DATE) AS month_start,
DATE_TRUNC(‘year’, CURRENT_DATE) AS year_start;

执行结果：

year | month | day | day_of_week | day_of_year | week_of_year | month_start | year_start
——+——-+—–+————-+————-+————–+————-+————
2026 | 4 | 4 | 6 | 94 | 14 | 2026-04-01 | 2026-01-01
(1 row)

— 日期计算
SELECT
CURRENT_DATE + 1 AS tomorrow,
CURRENT_DATE – 1 AS yesterday,
CURRENT_DATE + INTERVAL ‘1 week’ AS next_week,
CURRENT_DATE – INTERVAL ‘1 month’ AS last_month,
CURRENT_DATE + INTERVAL ‘1 year’ AS next_year,
AGE(CURRENT_DATE, ‘1990-01-15’) AS age_result,
AGE(‘1990-01-15’) AS age_from_now;

执行结果：

更多视频教程www.fgedu.net.cn

tomorrow | yesterday | next_week | last_month | next_year | age_result | age_from_now
————+————+————-+————+————-+—————————-+————————–
2026-04-05 | 2026-04-03 | 2026-04-11 | 2026-03-04 | 2027-04-04 | 36 years 2 mons 19 days | 36 years 2 mons 19 days
(1 row)

— 日期格式化
SELECT
TO_CHAR(CURRENT_DATE, ‘YYYY-MM-DD’) AS format_1,
TO_CHAR(CURRENT_DATE, ‘YYYY年MM月DD日’) AS format_2,
TO_CHAR(CURRENT_TIMESTAMP, ‘YYYY-MM-DD HH24:MI:SS’) AS format_3,
TO_CHAR(CURRENT_TIMESTAMP, ‘DY, DD Mon YYYY HH12:MI:SS AM’) AS format_4;

执行结果：

format_1 | format_2 | format_3 | format_4
————-+——————+———————–+——————————–
2026-04-04 | 2026年04月04日 | 2026-04-04 14:30:25 | Sat, 04 Apr 2026 02:30:25 PM
(1 row)

— CASE表达式
SELECT
product_name,
price,
CASE
WHEN price < 100 THEN '低价' WHEN price < 500 THEN '中价' WHEN price < 1000 THEN '高价' ELSE '昂贵' END AS price_level FROM (VALUES ('产品A', 50.00), ('产品B', 200.00), ('产品C', 800.00), ('产品D', 2000.00) ) AS products(product_name, price);

执行结果：

product_name | price | price_level
————–+——–+————-
产品A | 50.00 | 低价
产品B | 200.00 | 中价
产品C | 800.00 | 高价
产品D |2000.00 | 昂贵
(4 rows)

— COALESCE和NULLIF函数
SELECT
COALESCE(NULL, ‘default’) AS coalesce_1,
COALESCE(NULL, NULL, ‘third’) AS coalesce_2,
COALESCE(‘value’, ‘default’) AS coalesce_3,
NULLIF(‘value’, ‘value’) AS nullif_1,
NULLIF(‘value’, ‘other’) AS nullif_2,
NULLIF(0, 0) AS nullif_zero;

执行结果：

coalesce_1 | coalesce_2 | coalesce_3 | nullif_1 | nullif_2 | nullif_zero
————+————+————+———-+———-+————-
default | third | value | | value |
(1 row)

— GREATEST和LEAST函数
SELECT
GREATEST(10, 20, 30, 5, 15) AS greatest_result,
LEAST(10, 20, 30, 5, 15) AS least_result,
GREATEST(‘B’, ‘A’, ‘C’) AS greatest_str,
LEAST(‘B’, ‘A’, ‘C’) AS least_str;

执行结果：

greatest_result | least_result | greatest_str | least_str
—————–+————–+————–+———–
30 | 5 | C | A
(1 row)

— NULL处理函数
SELECT
NULLIF(10, 10) AS nullif_same,
NULLIF(10, 20) AS nullif_diff,
COALESCE(NULL, NULL, ‘default’) AS coalesce_default,
CASE WHEN NULL IS NULL THEN ‘NULL值’ ELSE ‘非NULL’ END AS is_null_check,
CASE WHEN NULL IS NOT NULL THEN ‘非NULL’ ELSE ‘NULL值’ END AS is_not_null_check;

执行结果：

nullif_same | nullif_diff | coalesce_default | is_null_check | is_not_null_check
————-+————-+——————+—————+——————-
| 10 | default | NULL值 | NULL值
(1 row)

— 类型转换函数
SELECT
TO_NUMBER(‘1,234.56’, ‘9,999.99’) AS to_number_result,
TO_DATE(‘2026-04-04’, ‘YYYY-MM-DD’) AS to_date_result,
TO_TIMESTAMP(‘2026-04-04 14:30:00’, ‘YYYY-MM-DD HH24:MI:SS’) AS to_timestamp_result,
CAST(‘123’ AS INTEGER) AS cast_int,
‘456’::INTEGER AS operator_int;

执行结果：

更多学习教程公众号风哥教程itpux_com

to_number_result | to_date_result | to_timestamp_result | cast_int | operator_int
——————+—————-+——————————+———-+————–
1234.56 | 2026-04-04 | 2026-04-04 14:30:00+08 | 123 | 456
(1 row)

— 系统信息函数
SELECT
CURRENT_DATABASE() AS current_db,
CURRENT_USER AS current_fgedu,
SESSION_USER AS session_fgedu,
CURRENT_SCHEMA AS current_schema,
VERSION() AS pg_version,
PG_BACKEND_PID() AS backend_pid;

执行结果：

current_db | current_fgedu | session_fgedu | current_schema | pg_version | backend_pid
————+————–+————–+—————-+—————————————————–+————-
pgsql | pgsql | pgsql | public | PostgreSQL 18.0 on x86_64-pc-linux-gnu, compiled…| 12345
(1 row)

— 创建用户数据表
CREATE TABLE fgedu_fgedu_data (
fgedu_id SERIAL PRIMARY KEY,
raw_name TEXT,
raw_phone TEXT,
raw_email TEXT,
birth_date TEXT,
salary TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入测试数据
INSERT INTO fgedu_fgedu_data (raw_name, raw_phone, raw_email, birth_date, salary) VALUES
(‘ 风哥1号 ‘, ‘138-0013-8000’, ‘ZHANGSAN@FGEDU.NET’, ‘1990-01-15’, ‘10,000.00’),
(‘风哥2号’, ‘139 0013 9000’, ‘lisi@fgedu.net.cn’, ‘1985-06-20’, ‘15,000.50’),
(‘ 王五 ‘, ‘13700137000’, ‘WANGWU@TEST.COM’, ‘1995-12-01’, ‘8,500.00’),
(‘赵六’, ‘150-0013-8001’, ‘ZHAOLIU@FGEDU.NET’, ‘1988-03-10’, ‘12,000.00’);

— 数据清洗处理
SELECT
fgedu_id,
TRIM(raw_name) AS clean_name,
UPPER(LEFT(TRIM(raw_name), 1)) AS name_initial,
REPLACE(REPLACE(raw_phone, ‘-‘, ”), ‘ ‘, ”) AS clean_phone,
LOWER(raw_email) AS clean_email,
birth_date::DATE AS birth_date_clean,
TO_NUMBER(salary, ‘99,999.99’) AS salary_clean,
EXTRACT(YEAR FROM AGE(birth_date::DATE)) AS age,
CASE
WHEN TO_NUMBER(salary, ‘99,999.99’) < 10000 THEN '初级' WHEN TO_NUMBER(salary, '99,999.99') < 15000 THEN '中级' ELSE '高级' END AS salary_level FROM fgedu_fgedu_data;

执行结果：

fgedu_id | clean_name | name_initial | clean_phone | clean_email | birth_date_clean | salary_clean | age | salary_level
———+————+————–+————-+———————-+——————+————–+—–+————–
1 | 风哥1号 | 张 | 13800138000 | zhangsan@fgedu.net | 1990-01-15 | 10000.00 | 36 | 中级
2 | 风哥2号 | 李 | 13900139000 | lisi@fgedu.net.cn | 1985-06-20 | 15000.50 | 40 | 高级
3 | 王五 | 王 | 13700137000 | wangwu@test.com | 1995-12-01 | 8500.00 | 30 | 初级
4 | 赵六 | 赵 | 15000138001 | zhaoliu@fgedu.net | 1988-03-10 | 12000.00 | 38 | 中级
(4 rows)

— 删除测试表
DROP TABLE IF EXISTS fgedu_fgedu_data;

执行结果：

DROP TABLE