默认 ID 与 Korma 和 Postgresql

Question

>我有以下模式：

CREATE TABLE IF NOT EXISTS art_pieces
(
  -- Art Data
  ID SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  description TEXT,
  price INT NULL,
  -- Relations
  artists_id INT NULL
);
--;;
CREATE TABLE IF NOT EXISTS artists
(
  -- Art Data
  ID SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

这是相应的艺术作品实体：

(defentity art-pieces
  (table :art_pieces)
  (entity-fields
    :id
    :title
    :description
    :price
    :artists_id)
  (belongs-to artists))

我想知道为什么以下返回PSQLException ERROR: null value in column "id" violates not-null constraint：

(create-piece {:title "The Silence of the Lambda" 
               :description "Something something java beans and a nice chianti" 
               :price 5000})

ID SERIAL PRIMARY KEY字段不应该自动填充吗？这与科尔玛与PSQL的互动有关吗？

Answer 1

INSERT INTO "art_pieces" ("description", "id", "price", "title") VALUES (?, NULL, ?, ?)

这里的问题是您尝试将NULL值插入id列中。仅当省略列或使用关键字（而不是 NULL）时，才会插入默认值DEFAULT 。

若要将序列的下一个值插入到序列列中，请指定应为序列列分配其默认值。这可以通过从 INSERT 语句中的列列表中排除列来完成，也可以通过使用 DEFAULT 关键字来完成

PostgreSQL 串行类型

因此，您必须将查询更改为：

INSERT INTO "art_pieces" ("description", "id", "price", "title") VALUES (?, DEFAULT, ?, ?)
-- or
INSERT INTO "art_pieces" ("description", "price", "title") VALUES (?, ?, ?)

---

另一种解决方法（如果您无权更改查询）是添加一个trigger函数，该函数将自动替换id列中的值NULL：

CREATE OR REPLACE FUNCTION tf_art_pieces_bi() RETURNS trigger AS
$BODY$
BEGIN
    -- if insert NULL value into "id" column
    IF TG_OP = 'INSERT' AND new.id IS NULL THEN
        -- set "id" to the next sequence value
        new.id = nextval('art_pieces_id_seq');
    END IF;
    RETURN new; 
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER art_pieces_bi
BEFORE INSERT
ON art_pieces
FOR EACH ROW EXECUTE PROCEDURE tf_art_pieces_bi();