PostgreSQL 语法特性

发表于 | 分类于 |

批量插入

  • 方式一 

    1
    INSERT INTO table_name SELECT...FROM source_table

  • 方式二 

    1
    INSERT INTO VALUES (),(),…()

比一条SQL插入一条数据的方式能减少和数据库的交互，减少数据库WAL（Write-Ahead Logging）日志的生成，提升插入效率

  • 方式三
    1
    copy aa from '/home/digoal/aa.csv' with (delimiter U&'\0009');

U&’\0009’ TAB作为分隔符

RETURNING关键字

UPDATE|INSERT|DELETE 支持RETURNING返回更改过的数据

UPSERT语法

1
2
3
insert into table_name values(...) on conflict (conflict_target) 
DO NOTHING    --什么也不做
DO UPDATE SET -- 更新

conflict_target字段必须为主键或者唯一键

数据抽样

  • BERNOULLI抽样方式
    更好的随机性,但比SYSTEM抽样方式性能低.
  • SYSTEM抽样
    随机抽取表上数据块的数据.

    聚合函数

  • string_agg(expression, delimiter)
    函数返回的类型和输入参数类型一致
  • array_agg(expression)
    返回数组类型

    窗口函数

Function Return Type Description Function
row_number() bigint number of the current row within its partition, counting from 1 row_number()
rank() bigint rank of the current row with gaps; same as row_number of its first peer rank()
dense_rank() bigint rank of the current row without gaps; this function counts peer groups dense_rank()
percent_rank() double precision relative rank of the current row: (rank - 1) / (total rows - 1) percent_rank()
cume_dist() double precision relative rank of the current row: (number of rows preceding or peer with current row) / (total rows) cume_dist()
ntile(num_buckets integer) integer integer ranging from 1 to the argument value, dividing the partition as equally as possible ntile(num_buckets integer)
lag(value any [, offsetinteger [, default any ]]) same type as value returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null lag(value any [, offsetinteger [, default any ]])
lead(value any [, offsetinteger [, default any ]]) same type as value returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Bothoffset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null lead(value any [, offsetinteger [, default any ]])
first_value(value any) same type as value returns value evaluated at the row that is the first row of the window frame first_value(value any)
last_value(value any) same type as value returns value evaluated at the row that is the last row of the window frame last_value(value any)
nth_value(value any, nthinteger) same type as value returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row nth_value(value any, nthinteger)