postgres学习笔记

Z时代
2024-01-10
分类：综合

database

将一个标识符变得受限同时也使它变成大小写敏感的，反之非受限名称总是被转换成小写形式。例如，标识符FOO、foo和"foo"在PostgreSQL中被认为是相同的，而"Foo"和"FOO"则互不相同且也不同于前面三个标识符（PostgreSQL将非受限名字转换为小写形式与SQL标准是不兼容的，SQL标准中要求将非受限名称转换为大写形式。这样根据标准， foo应该和 "FOO"而不是"foo"相同。如果希望写一个可移植的应用，我们应该总是用引号修饰一个特定名字或者从不使用引号修饰）。

查询自动中添加单引号的方法

SQL>select"123""1234"
"123"1234"

查询转义字符需要在字段前加入E

SQL>select E"123foo
1234"
"123
foo
1234"

使用美元来代替单引号

SQL>select $$Dianne"s horse$$
"Dianne"s horse"

位置参数$number

CREATEFUNCTION dept(text) RETURNS test.dept
AS $$ SELECT*FROM test.dept WHERE name = $1 $$
    LANGUAGE SQL;
这里$1引用函数被调用时第一个函数参数的值。

聚集表达式

aggregate_name (expression [ , ... ][ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (ALL expression [ , ... ][ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name (DISTINCT expression [ , ... ][ order_by_clause ] ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( * ) [ FILTER ( WHERE filter_clause ) ]
aggregate_name ( [ expression [ , ... ] ] ) WITHIN GROUP ( order_by_clause ) [ FILTER ( WHERE filter_clause ) ]

大部分聚集函数忽略空输入，这样其中一个或多个表达式得到空值的行将被丢弃。除非另有说明，对于所有内建聚集都是这样。

例如，count(*)得到输入行的总数。count(f1)得到输入行中f1为非空的数量，因为count忽略空值。而count(distinct f1)得到f1的非空可区分值的数量。

在处理多参数聚集函数时，注意ORDER BY出现在所有聚集参数之后。

SELECT string_agg(a, ","ORDERBY a) FROMtable;

如果指定了FILTER，那么只有对filter_clause计算为真的输入行会被交给该聚集函数，其他行会被丢弃。例如：

SELECT
count(*) AS unfiltered,
count(*) FILTER (WHERE i <5) AS filtered
FROM generate_series(1,10) AS s(i);
 unfiltered | filtered
------------+----------
10|4
(1 row)

窗口函数

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ]OVER window_name
function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ]OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ]OVER window_name
function_name ( * ) [ FILTER ( WHERE filter_clause ) ]OVER ( window_definition )

其中window_definition的语法是

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ][ NULLS { FIRST | LAST } ][, ...] ]
[ frame_clause ]

而可选的frame_clause是下列之一

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end

其中frame_start和frame_end可以是下面形式中的一种

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

类型转换

CAST ( expression AS type )
expression::type

CAST语法遵从 SQL，而用::的语法是PostgreSQL的历史用法。

创建function样例

CREATEFUNCTION concat_lower_or_upper(a text, b text, uppercase boolean DEFAULT false)
RETURNStext
AS
$$
SELECTCASE
WHEN $3THENUPPER($1||""|| $2)
ELSELOWER($1||""|| $2)
END;
$$
LANGUAGE SQL IMMUTABLE STRICT;

SELECT concat_lower_or_upper("Hello", "World", true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper("Hello", "World");
 concat_lower_or_upper 
-----------------------
 hello world
(1 row)

使用命名记号

SELECT concat_lower_or_upper(a =>"Hello", b =>"World", uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)
SELECT concat_lower_or_upper(a =>"Hello", uppercase => true, b =>"World");
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

SELECT concat_lower_or_upper("Hello", "World", uppercase => true);
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)


为了向后兼容性，基于 ":=" 的旧语法仍被支持：

SELECT concat_lower_or_upper(a := "Hello", uppercase := true, b := "World");
 concat_lower_or_upper 
-----------------------
 HELLO WORLD
(1 row)

常见数据类型：

用于整数的integer；可以用于分数的numeric；用于字符串的text，用于日期的date，用于一天内时间的time以及可以同时包含日期和时间的timestamp。

一个表能够拥有的列的数据是有限的，根据列的类型，这个限制介于250和1600之间。

插入时默认主键值

CREATETABLE products (
    product_no integerDEFAULT nextval("products_product_no_seq"),
    ...
);
速写
CREATETABLE products (
    product_no SERIAL,
    ...
);

约束：

CREATETABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price >0)
);
给约束起名
CREATETABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price >0)
);
多列约束
CREATETABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price >0),
    discounted_price numeric CHECK (discounted_price >0),
CHECK (price > discounted_price)
);
CREATETABLE products (
    product_no integer,
    name text,
    price numeric,
CHECK (price >0),
    discounted_price numeric,
CHECK (discounted_price >0),
CHECK (price > discounted_price)
);
CREATETABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price >0),
    discounted_price numeric,
CHECK (discounted_price >0AND price > discounted_price)
);
CREATETABLE products (
    product_no integer,
    name text,
    price numeric,
CHECK (price >0),
    discounted_price numeric,
CHECK (discounted_price >0),
CONSTRAINT valid_discount CHECK (price > discounted_price)
);
非空约束
CREATETABLE products (
    product_no integerNOTNULL,
    name textNOTNULL,
    price numeric
);
一列多种约束
CREATETABLE products (
    product_no integerNOTNULL,
    name textNOTNULL,
    price numeric NOTNULLCHECK (price >0)
);
空约束，不存在约束，只是脚本中方便切换非空约束才这么写，没实际意义
CREATETABLE products (
    product_no integerNULL,
    name textNULL,
    price numeric NULL
);

唯一约束
CREATETABLE products (
    product_no integerUNIQUE,
    name text,
    price numeric
);
CREATETABLE products (
    product_no integer,
    name text,
    price numeric,
UNIQUE (product_no)
);
多列组成唯一约束
CREATETABLE example (
    a integer,
    b integer,
    c integer,
UNIQUE (a, c)
);
CREATETABLE products (
    product_no integerCONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);
主键
CREATETABLE products (
    product_no integerUNIQUENOTNULL,
    name text,
    price numeric
);
CREATETABLE products (
    product_no integerPRIMARYKEY,
    name text,
    price numeric
);
多列主键
CREATETABLE example (
    a integer,
    b integer,
    c integer,
PRIMARYKEY (a, c)
);
外键约束和排他约束一般不用

系统隐式定义列

oid

一行的对象标识符（对象ID）。该列只有在表使用WITH OIDS创建时或者default_with_oids配置变量被设置时才存在。该列的类型为oid（与列名一致）

tableoid

包含这一行的表的OID。该列是特别为从继承层次中选择的查询而准备，因为如果没有它将很难知道一行来自于哪个表。tableoid可以与pg_class的oid列进行连接来获得表的名称。

xmin

插入该行版本的事务身份（事务ID）。一个行版本是一个行的一个特别版本，对一个逻辑行的每一次更新都将创建一个新的行版本。

cmin

插入事务中的命令标识符（从0开始）。

xmax

删除事务的身份（事务ID），对于未删除的行版本为0。对于一个可见的行版本，该列值也可能为非零。这通常表示删除事务还没有提交，或者一个删除尝试被回滚。

cmax

删除事务中的命令标识符，或者为0。

ctid

行版本在其表中的物理位置。注意尽管ctid可以被用来非常快速地定位行版本，但是一个行的ctid会在被更新或者被VACUUM FULL移动时改变。因此，ctid不能作为一个长期行标识符。OID或者最好是一个用户定义的序列号才应该被用来标识逻辑行。

OID是32位量，它从一个服务于整个集簇的计数器分配而来。在一个大型的或者历时长久的数据库中，该计数器有可能会出现绕回。因此，不要总是假设OID是唯一的，除非你采取了措施来保证。如果需要在一个表中标识行，推荐使用一个序列生成器。然而，OID也可以被使用，但是是要采取一些额外的预防措施：

如果要将OID用来标识行，应该在OID列上创建一个唯一约束。当这样一个唯一约束（或唯一索引）存在时，系统会注意不生成匹配现有行的OID（当然，这只有在表的航数目少于2³²（40亿）时才成立。并且在实践中表的尺寸最好远比这个值小，否则将会牺牲性能）。

绝不要认为OID在表之间也是唯一的，使用tableoid和行OID的组合来作为数据库范围内的标识符。

当然，问题中的表都必须是用WITH OIDS创建。在PostgreSQL 8.1中，WITHOUT OIDS是默认形式。

事务标识符也是32位量。在一个历时长久的数据库中事务ID同样会绕回。但如果采取适当的维护过程，这不会是一个致命的问题。但是，长期（超过10亿个事务）依赖事务ID的唯一性是不明智的。

命令标识符也是32位量。这对一个事务中包含的SQL命令设置了一个硬极限： 2³²（40亿）。在实践中，该限制并不是问题 — 注意该限制只是针对SQL命令的数目而不是被处理的行数。同样，只有真正修改了数据库内容的命令才会消耗一个命令标识符。

列操作命令

增加一列
ALTERTABLE products ADDCOLUMN description text;
ALTERTABLE products ADDCOLUMN description textCHECK (description <>"");
提示: 增加一个带默认值的列需要更新表中的每一行（来存储新列值）。然而，如果不指定默认值，PostgreSQL可以避免物理更新。因此如果我们准备向列中填充的值大多是非默认值，最好是增加列的时候不指定默认值，增加列后用UPDATE填充正确的数据并且增加所需要的默认值约束。
移除列
ALTERTABLE products DROPCOLUMN description;
移除时忽略外键的约束
ALTERTABLE products DROPCOLUMN description CASCADE;
增加约束
ALTERTABLE products ADDCHECK (name <>"");
ALTERTABLE products ADDCONSTRAINT some_name UNIQUE (product_no);
ALTERTABLE products ADDFOREIGNKEY (product_group_id) REFERENCES product_groups;
ALTERTABLE products ALTERCOLUMN product_no SETNOTNULL;
移除约束，"psql d 表名"来查看系统默认约束名
ALTERTABLE products DROPCONSTRAINT some_name;
ALTERTABLE products ALTERCOLUMN product_no DROPNOTNULL;
设置移除默认值
ALTERTABLE products ALTERCOLUMN price SETDEFAULT7.77;
ALTERTABLE products ALTERCOLUMN price DROPDEFAULT;
修改列数据类型
ALTERTABLE products ALTERCOLUMN price TYPE numeric(10,2);
如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。
重命名列
ALTERTABLE products RENAME COLUMN product_no TO product_number;
重命名表
ALTERTABLE products RENAME TO items;

权限：

SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE以及USAGE。

joe是一个已有用户，accounts是一个已有表，用ALL取代特定权限会把与对象类型相关的所有权限全部授权。可以在授予权限时使用"withgrantoption"来允许接收人将权限转授给其他人。
GRANTUPDATEON accounts TO joe;
撤销权限：
REVOKEALLON accounts FROMPUBLIC;

模式，将模式写入路径中，那么SQL查询可以省略模式名

CREATESCHEMA myschema;

模式.表

在模式中创建表

CREATETABLE myschema.mytable (

...

);

删除模式

DROPSCHEMA myschema;

DROPSCHEMA myschema CASCADE;

创建一个由其他人所拥有的模式

CREATESCHEMA schemaname AUTHORIZATION username;

以pg_开头的模式名被保留用于系统目的，所以不能被用户所创建。

不指定模式，默认放在public模式中，查看默认模式

SHOW search_path;

将模式放到搜索路径中可以不用加表明进行查询

SET search_path TO myschema,public;

SET search_path TO myschema;

公共模式没有什么特别之处，它只是默认存在而已，它也可以被删除。

注意在默认情况下，所有人都拥有在public模式上的CREATE和USAGE权限。这使得用户能够连接到一个给定数据库并在它的public模式中创建对象。如果不希望允许这样，可以撤销该权限：

REVOKECREATEONSCHEMApublicFROMPUBLIC;
第一个"public"是模式，第二个"public"指的是 "每一个用户"。第一种是一个标识符，第二种是一个关键词，所以两者的大小写不同。

以上是 postgres学习笔记的全部内容，来源链接： utcz.com/z/535848.html

postgres学习笔记

其他人也看了：