Korma和Postgresql的默认ID？

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字段不应该自动填充吗？这与Korma与PSQL的交互有关吗？

Answer 1

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

这里的问题是您试图将NULL值插入到id列中。只有在省略列或使用DEFAULT关键字(而不是NULL)时，才会插入默认值。

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

PostgreSQL Serial Types

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

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();