PGSQL常见命令行与函数实例详解

# 基本连接（使用当前系统用户）
psql
# 连接指定数据库
psql -d mydatabase
# 连接指定用户和数据库
psql -u username -d mydatabase
# 连接远程数据库
psql -h hostname -p 5432 -u username -d mydatabase
# 连接并执行sql文件
psql -d mydatabase -f script.sql
# 连接并执行单条命令
psql -d mydatabase -c "select version();"

-- 列出所有数据库
\l
\l+
-- 连接/切换数据库
\c database_name
\connect database_name
-- 创建数据库
create database newdb;
-- 删除数据库
drop database olddb;

-- 列出当前数据库所有表
\dt
\dt+
-- 描述表结构
\d table_name
\d+ table_name
-- 列出所有索引
\di
\di+
-- 列出所有序列
\ds
\ds+

-- 列出所有模式
\dn
\dn+
-- 设置搜索路径
set search_path to schema1, schema2;
-- 查看当前搜索路径
show search_path;

-- 列出所有用户/角色
\du
\du+
-- 查看当前用户
select current_user;
-- 查看用户权限
\dp table_name
\z table_name

-- 查看服务器版本
select version();
-- 查看连接信息
\conninfo
-- 查看当前数据库
select current_database();
-- 查看当前模式
select current_schema();

-- 扩展显示（行转列）
\x on
\x auto
\x off
-- 设置输出格式
\a (对齐切换)
\f (字段分隔符)
-- 计时开关
\timing on
\timing off
-- 输出到文件
\o filename.txt
\o

-- 绝对值
select abs(-15); -- 15
-- 四舍五入
select round(3.14159, 2); -- 3.14
select round(123.456); -- 123
-- 取整函数
select ceil(3.14); -- 4 (向上取整)
select floor(3.14); -- 3 (向下取整)
select trunc(3.14159, 2); -- 3.14 (截断)
-- 幂运算
select power(2, 3); -- 8
select sqrt(25); -- 5 (平方根)
select cbrt(27); -- 3 (立方根)
-- 对数函数
select ln(10); -- 自然对数
select log(100); -- 以10为底对数

select pi(); -- 3.14159265358979
select sin(pi()/2); -- 1
select cos(0); -- 1
select tan(pi()/4); -- 1
select degrees(pi()); -- 180 (弧度转角度)
select radians(180); -- 3.14159 (角度转弧度)

select random(); -- 0-1之间的随机数
select setseed(0.5); -- 设置随机种子
-- 生成指定范围随机整数
select floor(random() * 100)::int; -- 0-99随机整数
select (random() * (max-min) + min)::int; -- min-max随机整数

-- 长度和位置
select length('hello'); -- 5
select position('l' in 'hello'); -- 3
select strpos('hello', 'l'); -- 3
-- 大小写转换
select upper('hello'); -- hello
select lower('hello'); -- hello
select initcap('hello world'); -- hello world
-- 修剪函数
select trim('  hello  '); -- 'hello'
select ltrim('  hello'); -- 'hello'
select rtrim('hello  '); -- 'hello'
select trim(both 'x' from 'xxhelloxx'); -- 'hello'
-- 填充函数
select lpad('hi', 5, '*'); -- '***hi'
select rpad('hi', 5, '*'); -- 'hi***'

-- 子字符串
select substring('hello world' from 2 for 5); -- 'ello '
select substr('hello world', 2, 5); -- 'ello '
-- 字符串替换
select replace('hello world', 'world', 'postgresql'); -- 'hello postgresql'
select translate('12345', '123', 'abc'); -- 'abc45'
-- 字符串分割
select split_part('a,b,c,d', ',', 2); -- 'b'
select string_to_array('a,b,c', ','); -- {a,b,c}
select array_to_string(array['a','b','c'], ','); -- 'a,b,c'

-- 字符串连接
select concat('hello', ' ', 'world'); -- 'hello world'
select 'hello' || ' ' || 'world'; -- 'hello world'
-- 格式化输出
select format('hello %s, your balance is %s', 'john', 100.50);
-- 'hello john, your balance is 100.50'
-- 重复字符串
select repeat('*', 5); -- '*****'

-- 获取当前时间
select current_date; -- 当前日期
select current_time; -- 当前时间
select current_timestamp; -- 当前时间戳
select now(); -- 当前时间戳（同current_timestamp）
select localtimestamp; -- 本地时间戳
-- 带精度的当前时间
select current_time(2); -- 当前时间（2位小数秒）
select current_timestamp(3); -- 当前时间戳（3位小数秒）

-- extract函数
select extract(year from current_timestamp); -- 年份
select extract(month from current_timestamp); -- 月份
select extract(day from current_timestamp); -- 日期
select extract(hour from current_timestamp); -- 小时
select extract(minute from current_timestamp); -- 分钟
select extract(second from current_timestamp); -- 秒数
select extract(dow from current_timestamp); -- 星期几（0-6，周日=0）
select extract(doy from current_timestamp); -- 一年中的第几天
-- date_part函数（同extract）
select date_part('year', current_timestamp);

-- 日期加减
select current_date + interval '1 day'; -- 明天
select current_date - interval '1 week'; -- 一周前
select current_timestamp + interval '2 hours 30 minutes'; -- 2小时30分钟后
-- 年龄计算
select age('2000-01-01'::date); -- 从2000-01-01到现在的年龄
select age('2000-01-01'::date, '2020-01-01'::date); -- 两个日期之间的年龄
-- 日期截断
select date_trunc('hour', current_timestamp); -- 截断到小时
select date_trunc('day', current_timestamp); -- 截断到天
select date_trunc('month', current_timestamp); -- 截断到月

-- to_char格式化
select to_char(current_timestamp, 'yyyy-mm-dd hh24:mi:ss'); -- '2023-01-01 14:30:00'
select to_char(current_timestamp, 'day, month dd, yyyy'); -- 'sunday, january 01, 2023'
select to_char(123.45, '999d99'); -- '123.45'
-- to_date和to_timestamp转换
select to_date('20230101', 'yyyymmdd'); -- 2023-01-01
select to_timestamp('20230101 143000', 'yyyymmdd hh24miss'); -- 2023-01-01 14:30:00

-- case表达式
select 
    name,
    salary,
    case 
        when salary > 100000 then 'high'
        when salary > 50000 then 'medium'
        else 'low'
    end as salary_level
from employees;
-- 简单case
select 
    product_type,
    case product_type
        when 'a' then 'type a'
        when 'b' then 'type b'
        else 'other'
    end as type_description
from products;

-- coalesce（返回第一个非空值）
select coalesce(null, null, 'default'); -- 'default'
select coalesce(email, 'n/a') from users; -- 如果email为null则返回'n/a'
-- nullif（相等返回null）
select nullif(5, 5); -- null
select nullif(5, 0); -- 5
-- greatest和least
select greatest(1, 5, 3); -- 5
select least(1, 5, 3); -- 1

-- 计数
select count(*) from table; -- 总行数
select count(distinct column) from table; -- 不重复计数
-- 求和与平均
select sum(salary) from employees;
select avg(salary) from employees;
-- 最大最小值
select max(salary) from employees;
select min(salary) from employees;
-- 统计信息
select stddev(salary) from employees; -- 标准差
select variance(salary) from employees; -- 方差

-- 分组统计
select 
    department,
    count(*) as emp_count,
    avg(salary) as avg_salary,
    sum(salary) as total_salary
from employees 
group by department;
-- 字符串聚合
select 
    department,
    string_agg(name, ', ') as employees
from employees 
group by department;
-- 数组聚合
select 
    department,
    array_agg(name) as employees
from employees 
group by department;
-- json聚合
select 
    department,
    json_agg(json_build_object('name', name, 'salary', salary)) as employees
from employees 
group by department;

-- 行号、排名、密集排名
select 
    name,
    salary,
    row_number() over (order by salary desc) as row_num,
    rank() over (order by salary desc) as rank,
    dense_rank() over (order by salary desc) as dense_rank
from employees;
-- 分区排名
select 
    department,
    name,
    salary,
    rank() over (partition by department order by salary desc) as dept_rank
from employees;

-- lag和lead（访问前后行）
select 
    date,
    sales,
    lag(sales) over (order by date) as prev_sales,
    lead(sales) over (order by date) as next_sales
from sales_data;
-- first_value和last_value
select 
    date,
    sales,
    first_value(sales) over (order by date) as first_sales,
    last_value(sales) over (order by date) as last_sales
from sales_data;

-- 累计计算
select 
    date,
    sales,
    sum(sales) over (order by date) as cumulative_sum,
    avg(sales) over (order by date) as cumulative_avg
from sales_data;
-- 移动平均
select 
    date,
    sales,
    avg(sales) over (order by date rows between 2 preceding and current row) as moving_avg_3
from sales_data;

-- 创建json
select json_build_object('name', 'john', 'age', 30);
select json_build_array(1, 2, 3);
-- 解析json
select json_extract_path('{"a": {"b": 1}}', 'a', 'b'); -- 1
select '{"name": "john"}'::json->>'name'; -- 'john'
select '{"ages": [10, 20, 30]}'::json->'ages'->>1; -- '20'

-- 行转json
select row_to_json(employees) from employees where id = 1;
-- json聚合
select 
    department,
    json_agg(json_build_object('name', name, 'salary', salary)) as employees
from employees 
group by department;

# 备份单个数据库
pg_dump -u username -d mydatabase -f backup.sql
# 备份所有数据库
pg_dumpall -u username -f alldb_backup.sql
# 压缩备份
pg_dump -u username -d mydatabase | gzip > backup.sql.gz
# 恢复数据库
psql -u username -d mydatabase -f backup.sql
# 恢复压缩备份
gunzip -c backup.sql.gz | psql -u username -d mydatabase

-- 查看查询计划
explain select * from large_table where id = 1000;
explain analyze select * from large_table where id = 1000;
-- 查看表大小和索引
select 
    schemaname,
    tablename,
    tableowner,
    tablesize,
    indexsize
from pg_tables 
where schemaname = 'public';
-- 查看长查询
select 
    pid, 
    now() - pg_stat_activity.query_start as duration, 
    query 
from pg_stat_activity 
where state = 'active' 
order by duration desc;

-- 查看锁信息
select 
    locktype, 
    relation::regclass, 
    mode, 
    granted 
from pg_locks 
where relation = 'mytable'::regclass;
-- 查看连接数
select 
    datname, 
    count(*) as connections 
from pg_stat_activity 
group by datname;
-- 查看表统计信息
select 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
from pg_stat_user_tables;