Default ID with Korma and Postgresql?

Question

I have the following schema:

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

This is the corresponding art-piece entity:

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

I'm wondering why the following returns 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})

Shouldn't the ID SERIAL PRIMARY KEY field populate automatically? Is this something to do with Korma's interaction with PSQL?

Answer 1

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

The problem here is that you try to insert NULL value into id column. Default value is inserted only if you omit the column or use DEFAULT keyword (instead of NULL).

To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word

PostgreSQL Serial Types

So you have to change the query to:

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

---

Another workaround (in case you don't have permissions to change the query) would be to add a trigger function that will replace NULL value in id column automatically:

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