Postgresql 修改数据表
Postgresql SELECT 语句 

Postgresql INSERT 语句

PostgreSQL 是一个强大的开源对象关系数据库系统，它使用 SQL 语言进行数据的查询和操作。INSERT 语句用于向 PostgreSQL 数据库的表中添加新的行。


语法格式

[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING { * | output_expression [ [ AS ] output_name ] } [, ...] ]where conflict_target can be one of:
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_nameand conflict_action is one of:
    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]


参数说明

  • with_query -- WITH 子句允许您指定一个或多个可在 INSERT 查询中按名称引用的子查询。

  • table_name -- 现有表的名称（可选模式限定）。

  • alias -- table_name的替代名称。提供别名后，它会完全隐藏表的实际名称。当 ON CONFLICT DO UPDATE 以名为 excluded 的表为目标时，这特别有用，因为否则该表将被视为表示建议插入的行的特殊表的名称。

  • column_name -- 表中以 table_name 命名的列的名称。如果需要，可以使用子字段名称或数组下标来限定列名称。（仅插入到复合列的某些字段中会使其他字段为空。使用 ON CONFLICT DO UPDATE 引用列时，不要在目标列的规范中包含表的名称。例如，INSERT INTO table_name ...发生冲突时，DO UPDATE SET table_name.col = 1 无效（这遵循 UPDATE 的一般行为）。

  • OVERRIDING SYSTEM VALUE -- 如果指定了此子句，则为标识列提供的任何值都将覆盖默认序列生成的值。对于定义为 GENERATED ALWAYS 的标识列，在不指定 OVERRIDING SYSTEM VALUE 或 OVERRIDING USER VALUE 的情况下插入显式值（DEFAULT 除外）是错误的。（对于定义为 GENERATED BY DEFAULT 的标识列，覆盖 SYSTEM VALUE 是正常行为，指定它不执行任何操作，但 PostgreSQL 允许将其作为扩展。

  • DEFAULT VALUES -- 所有列都将使用其默认值填充，就像为每个列显式指定了 DEFAULT 一样。（此格式不允许使用 OVERRIDING 子句。

  • expression -- 要分配给相应列的表达式或值。

  • DEFAULT -- 相应的列将填充其默认值。标识列将填充由关联序列生成的新值。对于生成列，允许指定此项，但仅指定从其生成表达式计算列的正常行为。

  • query -- 提供要插入的行的查询 （SELECT 语句）。有关语法的说明，请参阅 SELECT 语句。

  • output_expression -- 在插入或更新每行后由 INSERT 命令计算并返回的表达式。表达式可以使用以 table_name 命名的表的任何列名。写入 * 以返回插入或更新的行的所有列。

  • output_name -- 用于返回列的名称。



ON CONFLICT 子句

可选的 ON CONFLICT 子句指定引发唯一冲突或排除约束冲突错误的替代操作。对于建议插入的每个单独行，插入将继续进行，或者，如果违反了 conflict_target 指定的仲裁程序约束或索引，则采用替代conflict_action。ON CONFLICT DO NOTHING 只是避免插入一行作为其替代操作。ON CONFLICT DO UPDATE 更新与建议插入的行冲突的现有行作为其替代操作。


conflict_target 可以执行唯一索引推理。执行推理时，它由一个或多个 index_column_name 列和/或 index_expression 表达式以及可选的index_predicate组成。所有table_name唯一索引，无论顺序如何，都恰好包含conflict_target指定的列/表达式，都将被推断（选择）为仲裁索引。如果指定了 index_predicate，则作为推理的进一步要求，它必须满足仲裁程序索引。请注意，这意味着如果满足所有其他条件的索引可用，则将推断非部分唯一索引（没有谓词的唯一索引）（因此由 ON CONFLICT 使用）。如果尝试推理不成功，则会引发错误。

  • conflict_target -- 通过选择仲裁程序索引，指定 ON CONFLICT 对哪些冲突执行替代操作。执行唯一索引推理，或显式命名约束。对于 ON CONFLICT DO NOTHING，可以选择指定conflict_target;省略时，将处理与所有可用约束（和唯一索引）的冲突。对于 ON CONFLICT DO UPDATE，必须提供 conflict_target。

  • conflict_action -- conflict_action 指定替代的 ON CONFLICT 操作。它可以是 DO NOTHING，也可以是 DO UPDATE 子句，用于指定在发生冲突时要执行的 UPDATE 操作的确切详细信息。ON CONFLICT DO UPDATE 中的 SET 和 WHERE 子句可以使用表的名称（或别名）访问现有行，也可以使用特殊排除表访问建议插入的行。目标表中读取相应排除列的任何列都需要 SELECT 权限。请注意，所有每行 BEFORE INSERT 触发器的效果都反映在排除的值中，因为这些效果可能导致该行被排除在插入之外。

  • index_column_name -- table_name 列的名称。用于推断仲裁程序索引。遵循 CREATE INDEX 格式。需要对 index_column_name 的 SELECT 权限。

  • index_expression -- 类似于 index_column_name，但用于推断索引定义中出现table_name列（不是简单列）的表达式。遵循 CREATE INDEX 格式。需要对 index_expression 中显示的任何列具有 SELECT 权限。

  • collation -- 指定后，要求相应的 index_column_name 或 index_expression 使用特定排序规则，以便在推理期间进行匹配。通常，这将被省略，因为排序规则通常不会影响是否发生约束冲突。遵循 CREATE INDEX 格式。

  • opclass -- 指定后，要求相应的 index_column_name 或 index_expression 使用特定的运算符类，以便在推理期间进行匹配。通常，这会被省略，因为相等语义通常在类型的运算符类中是等效的，或者因为只需相信定义的唯一索引具有相等的相关定义就足够了。遵循 CREATE INDEX 格式。

  • index_predicate -- 用于允许推断部分唯一索引。可以推断出满足谓词的任何索引（实际上不必是部分索引）。遵循 CREATE INDEX 格式。需要对 index_predicate 中显示的任何列具有 SELECT 权限。

  • constraint_name -- 按名称显式指定仲裁程序约束，而不是推断约束或索引。

  • condition -- 返回 boolean 类型的值的表达式。只有此表达式返回 true 的行才会被更新，尽管在执行 ON CONFLICT DO UPDATE 操作时所有行都将被锁定。请注意，在将冲突确定为要更新的候选项后，最后评估 condition。


语句输出

INSERT 语句完成后将将返回如下格式：

INSERT oid count

count 是插入或更新的行数。oid 始终为 0（如果 count 正好为 1 并且目标表声明为 WITH OIDS，则它曾经是分配给插入行的 OID，否则为 0，但不再支持创建表 WITH OIDS）。

如果 INSERT 命令包含 RETURNING 子句，则结果将类似于 SELECT 语句的结果，该语句包含 RETURNING 列表中定义的列和值，根据命令插入或更新的行进行计算。


使用示例

  • 向 students 表中添加一条记录：

INSERT INTO students VALUES
('S202000991', '张三', 'C01021', '1986-07-16', 'zhangsan@qq.com');

  • 向 students 表中添加一条记录，只插入部分列：

INSERT INTO students (sno, name, class_id, birth_date)
    VALUES ('S202000991', '张三', 'C01021', '1986-07-16');

  • 插入完全由默认值组成的行：

INSERT INTO students DEFAULT VALUES;

  • 一次插入多条记录：

INSERT INTO students (sno, name, class_id, birth_date)
VALUES ('S202000991', '张三', 'C01021', '1986-07-16'),
        ('S202000992', '李四', 'C01021', '1986-02-17');

  • 将查询结果插入到表中：

INSERT INTO students SELECT * FROM students_tmp WHERE birth_date < '2004-05-07';