Postgres 9.5 或更高版本
UPSERT现在已正确实施INSERT ... ON CONFLICT ... DO NOTHING | UPDATE https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT。这很大程度上简化了操作：
INSERT INTO playlist_items AS m (id, playlist, item, group_name, duration, sort, legacy)
VALUES
(651, 21, 30012, 'a', 30, 1, FALSE)
, (DEFAULT, 21, 1, 'b', 34, 2, DEFAULT) -- !
, (668, 21, 30012, 'c', 30, 3, FALSE)
, (7428, 21, 23068, 'd', 0, 4, FALSE)
ON CONFLICT (id) DO UPDATE
SET ( playlist, item, group_name, duration, sort, legacy)
= (EXCLUDED.playlist, EXCLUDED.item, EXCLUDED.group_name, EXCLUDED.duration, EXCLUDED.sort, EXCLUDED.legacy)
-- (..., COALESCE(l.legacy, EXCLUDED.legacy)) -- alternative: see below
RETURNING m.id;
我们可以附上VALUES条款至INSERT直接，这允许DEFAULT关键词。如果出现独特的违规行为(id)，Postgres 更新。我们可以使用排除的行UPDATE. 手册： https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
The SET and WHERE中的条款ON CONFLICT DO UPDATE可以访问
使用表名称（或别名）的现有行，以及行
建议使用特殊插入excluded table.
And:
请注意，所有每行的效果BEFORE INSERT触发器是
反映在排除值中，因为这些影响可能有助于
到从插入中排除的行。
剩余的角落案例
您有多种选择UPDATE： 你可以 ...
- ...根本不更新：添加一个
WHERE条款至UPDATE仅写入选定的行。
- ...仅更新选定的列。
- ...仅当列当前为 NULL 时才更新：
COALESCE(l.legacy, EXCLUDED.legacy)
- ...仅当新值是时才更新
NOT NULL: COALESCE(EXCLUDED.legacy, l.legacy)
但没有办法辨别DEFAULT值和实际提供的值INSERT。仅产生EXCLUDED行是可见的。如果您需要区别，请回到之前的解决方案，我们可以为您提供这两种解决方案。
Postgres 9.4 或更高版本
这是一个棘手的问题。您遇到了此限制（根据文档 https://www.postgresql.org/docs/current/sql-values.html#AEN89091):
In a VALUES出现在顶层的列表INSERT， 一个
表达式可以替换为DEFAULT来表明目的地
应插入列的默认值。DEFAULT不能使用时VALUES出现在其他上下文中。
大胆强调我的。如果没有要插入的表，则不会定义默认值。所以没有direct解决你的问题，但有多种可能替代路线，取决于具体要求.
从系统目录中获取默认值？
You could从系统目录中获取这些pg_attrdef https://www.postgresql.org/docs/current/catalog-pg-attrdef.html 就像@Patrick 评论的那样 https://stackoverflow.com/questions/23794405/postgresql-9-3-cte-upsert-generating-default-value-for-a-table/23797357#comment36602284_23794405或来自information_schema.columns https://www.postgresql.org/docs/current/infoschema-columns.html。完整说明请参见此处：
- 获取 Postgres 中表列的默认值？ https://stackoverflow.com/questions/8146448/get-the-default-values-of-table-columns-in-postgres
但后来你still只有一个列表rows用表达式的文本表示来烹饪默认值。您必须动态构建和执行语句才能获取要使用的值。乏味又混乱。相反，我们可以让内置的 Postgres 功能为我们做到了这一点:
简单的快捷方式
插入一个虚拟行并将其返回以使用生成的默认值：
INSERT INTO playlist_items DEFAULT VALUES RETURNING *;
问题/解决方案的范围
这仅保证适用于STABLE or IMMUTABLE默认表达式 https://www.postgresql.org/docs/current/xfunc-volatility.html. Most VOLATILE函数也能正常工作，但不能保证。这current_timestamp函数族是稳定的，因为它们的值在事务中不会改变。
特别是，这对以下方面有副作用：serial列（或从序列中绘制的任何其他默认值）。但这应该不是问题，因为你通常不会写信给serial直接列。这些不应该被列在INSERT根本没有声明。
剩余缺陷为serial列：序列仍然通过单个调用前进以获取默认行，从而在编号中产生间隙。再说一次，这不应该是一个问题，因为差距一般都是可以预料到的 in serial列。
还可以解决两个问题：
避免虚拟行
Clone a 临时表包括列默认值并插入that:
BEGIN;
CREATE TEMP TABLE tmp_playlist_items (LIKE playlist_items INCLUDING DEFAULTS)
ON COMMIT DROP; -- drop at end of transaction
INSERT INTO tmp_playlist_items DEFAULT VALUES RETURNING *;
...
结果相同，副作用更少。由于默认表达式是逐字复制的，因此克隆会从相同的序列（如果有）中提取。但完全避免了不需要的行或触发器带来的其他副作用。
这个想法归功于 Igor：
- Postgresql，选择一个“假”行 https://stackoverflow.com/questions/17985164/postgresql-select-a-fake-row/17988613#17988613
Remove NOT NULL限制条件
您必须提供虚拟值NOT NULL列，因为 (根据文档 https://www.postgresql.org/docs/current/sql-createtable.html):
非空约束总是复制到新表中。
要么为那些在INSERT声明或（更好）消除约束：
ALTER TABLE tmp_playlist_items
ALTER COLUMN foo DROP NOT NULL
, ALTER COLUMN bar DROP NOT NULL;
有一个快速而肮脏的方式具有超级用户权限：
UPDATE pg_attribute
SET attnotnull = FALSE
WHERE attrelid = 'tmp_playlist_items'::regclass
AND attnotnull
AND attnum > 0;
它只是一个没有数据也没有其他用途的临时表，并且在事务结束时被删除。所以这条捷径很诱人。尽管如此，基本规则是：永远不要直接篡改系统目录。
那么，让我们看一下干净的方式：
使用动态 SQL 实现自动化DO陈述。你只需要常规特权因为同一个角色创建了临时表，所以您一定会拥有。
DO
$do$
BEGIN
EXECUTE (
SELECT 'ALTER TABLE tmp_playlist_items ALTER '
|| string_agg(quote_ident(attname), ' DROP NOT NULL, ALTER ')
|| ' DROP NOT NULL'
FROM pg_catalog.pg_attribute
WHERE attrelid = 'tmp_playlist_items'::regclass
AND attnotnull
AND attnum > 0
);
END
$do$;
干净得多，而且速度仍然很快。小心使用动态命令并警惕 SQL 注入。这个声明是安全的。看：
- 如何一次性从 PostgreSQL 表中删除所有 NOT NULL 约束 https://stackoverflow.com/questions/20146475/how-to-drop-all-not-null-constraints-from-a-postgresql-table-in-one-go/23808638#23808638
- 相关答案有更多解释 https://stackoverflow.com/search?q=user%3A939860+%5Bplpgsql%5D+string_agg+pg_attributes
通用解决方案（9.4 及更早版本）
BEGIN;
CREATE TEMP TABLE tmp_playlist_items
(LIKE playlist_items INCLUDING DEFAULTS) ON COMMIT DROP;
DO $$BEGIN
EXECUTE (
SELECT 'ALTER TABLE tmp_playlist_items ALTER '
|| string_agg(quote_ident(attname), ' DROP NOT NULL, ALTER ')
|| ' DROP NOT NULL'
FROM pg_catalog.pg_attribute
WHERE attrelid = 'tmp_playlist_items'::regclass
AND attnotnull
AND attnum > 0
);
END$$;
LOCK TABLE playlist_items IN EXCLUSIVE MODE; -- forbid concurrent writes
WITH default_row AS (
INSERT INTO tmp_playlist_items DEFAULT VALUES RETURNING *
)
, new_values (id, playlist, item, group_name, duration, sort, legacy) AS (
VALUES
(651, 21, 30012, 'a', 30, 1, FALSE)
, (NULL, 21, 1, 'b', 34, 2, NULL)
, (668, 21, 30012, 'c', 30, 3, FALSE)
, (7428, 21, 23068, 'd', 0, 4, FALSE)
)
, upsert AS ( -- *not* replacing existing values in UPDATE (?)
UPDATE playlist_items m
SET ( playlist, item, group_name, duration, sort, legacy)
= (n.playlist, n.item, n.group_name, n.duration, n.sort, n.legacy)
-- ..., COALESCE(n.legacy, m.legacy) -- see below
FROM new_values n
WHERE n.id = m.id
RETURNING m.id
)
INSERT INTO playlist_items
(playlist, item, group_name, duration, sort, legacy)
SELECT n.playlist, n.item, n.group_name, n.duration, n.sort
, COALESCE(n.legacy, d.legacy)
FROM new_values n, default_row d -- single row can be cross-joined
WHERE NOT EXISTS (SELECT 1 FROM upsert u WHERE u.id = n.id)
RETURNING id;
COMMIT;
您只需要LOCK如果您有并发事务尝试写入同一个表。
根据要求，这仅替换列中的 NULL 值legacy在输入行中INSERT案件。可以轻松扩展到其他列或在UPDATE案例也是如此。例如，你可以UPDATE有条件地也是如此：仅当输入值是NOT NULL。我添加了注释行UPDATE above.
旁白：你不需要cast除第一行外的任何行中的值VALUES表达式，因为类型派生自first row.
