PostgreSQL 10 : How to add unique with constraint and default value -1

Question

我希望将具有约束和默认值 -1 的唯一值添加到 term 和 oldestyear:

ALTER TABLE live_mytable
    ADD CONSTRAINT live_mytable_uq
    UNIQUE (id,loan_type,coalesce(term,-1),coalesce(oldestyear, -1));

因为我的目标是能够更新插入——如果集合不存在则插入，如果存在冲突则更新 apr 列(如果集合存在但 apr值不同):

INSERT INTO live_mytable(id, loan_type, apr, term, oldestyear) 
SELECT id, loan_type, apr, term, oldestyear
FROM   imp_mytable
  ON CONFLICT (id,loan_type,term,oldestyear) 
  DO UPDATE SET
     apr = excluded.apr;

我需要一个默认值 -1 的原因是因为 unique 不识别空值，而列 term 和 oldestyear 可以为空。

我是 PostgreSQL 的新手，所以我不确定我的方法是否正确。

我的表格设计:

id       |loan_type      |apr   |term|oldestyear|
---------|---------------|------|----|----------|
    68459|auto new       |  3.50|  48|          |
    68459|auto new       |  3.50|  60|          |
    68459|auto new       |  3.99|  72|          |
    68459|auto used      |  3.24|  36|          |
    68459|auto used      |  3.74|  48|          |
    68459|auto used      |  3.74|  60|          |
    68459|auto used      |  3.99|  72|          |
    10066|auto new       |  2.99|  36|          |
    10066|auto new       |  3.29|  48|          |

我的问题是，鉴于 term 和 oldestyear 可以是无效的。并且仅当唯一集匹配时 apr 不同时才更新。

Answer 1

使用您当前的数据模型，您不能，除非您这样做 the old way在 PL/pgSQL 中使用循环尝试 INSERT 和 UPDATE 直到其中一个成功。

也许您可以使用 NULL 以外的值来表示列中的缺失值，例如 -1。这会让事情变得简单，而且还可以简化您的一些查询。