Insert records based on last entry of foreign table

Question

I have the following simple table relations:

CREATE TABLE PRODUCT (
  ID                BIGSERIAL,
  TYPE              VARCHAR(24),
  TITLE             VARCHAR(128),
  PRIMARY KEY (ID)
);

CREATE TABLE EVENT (
  ID               BIGSERIAL,
  STATE            VARCHAR(64) NOT NULL,
  DATETIME         TIMESTAMP   NOT NULL,
  PRODUCT_ID        BIGINT,
  FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(ID),
  PRIMARY KEY (ID)
);

The "STATE" in Event, is converted from a Java Enum, can be: INITIALIZED, AVAILABLE, PROCESSED, etc.

I want to do the following: if the last event entry of the product has STATE 'PROCESSED', then create a new insert in the EVENT table with STATE 'AVAILABLE'. This must be for all products.

As you can see, the event table has a reference to product. I have looked at: Insert, on duplicate update in PostgreSQL?

But could not figure it out.

score 1 · Answer 1 · answered May 27 '18 at 11:20

INSERT INTO EVENT( STATE, DATETIME, PRODUCT_ID )
SELECT 'AVAILABLE', current_timestamp, product_id
FROM EVENT e
WHERE PRODUCT_ID = 123
  AND e.STATE = 'PROCESSED'
  AND NOT EXISTS (
     SELECT 'anything' FROM event e1
     WHERE e1.PRODUCT_ID = e.PRODUCT_ID
       AND e1.DATETIME > e.DATETIME
);

If this comand is supposed to run in multithread/multiuser environment, then the whole transaction must consist of these four commands, otherwise duplicate records may appear:

BEGIN TRANSACTION;

/* lock the parent record */
PERFORM id FROM PRODUCT WHERE id = 123 FOR UPDATE;

INSERT INTO EVENT( STATE, DATETIME, PRODUCT_ID )
    SELECT 'AVAILABLE', current_timestamp, product_id
    FROM EVENT e
    WHERE PRODUCT_ID = 123
      AND e.STATE = 'PROCESSED'
      AND NOT EXISTS (
         SELECT 'anything' FROM event e1
         WHERE e1.PRODUCT_ID = e.PRODUCT_ID
           AND e1.DATETIME > e.DATETIME
    );

COMMIT;

Thanks for your answer. The answer of Gordon Linoff was exactly what I wanted. — ielkhalloufi, May 27 '18 at 13:06

score 1 · Accepted Answer · answered May 27 '18 at 12:31

If your data is inserted sequentially, I would be more inclined to use id for the comparison as in:

insert into event (state, datetime, product_id)
    select 'AVAILABLE', current_timestamp, product_id
    from event e
    where e.state = 'PROCESSED' and
          not exists (select e2.state
                      from event e2
                      where e2.product_id = e.product_id and
                            e2.id > e.id  -- you can use timestamp
                     );

What interests me more is that you have said that state is an enum. Postgres does support enumerated types, and you don't have one. However, what you want to do is often handled using a check constraint:

CREATE TABLE EVENT (
  ID               BIGSERIAL,
  STATE            VARCHAR(64) NOT NULL,
  DATETIME         TIMESTAMP   NOT NULL,
  PRODUCT_ID        BIGINT,
  FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(ID),
  PRIMARY KEY (ID),
  CONSTRAINT CHECK_EVENT_STATE CHECK (STATE IN ('INITIALIZED', 'AVAILABLE', 'PROCESSED'))
);

Great, exactly what I wanted!! Data insertion is sequential, and what I meant with the Enum, is a Java Enum. I updated the post. Thanks again! — ielkhalloufi, May 27 '18 at 13:05

Insert records based on last entry of foreign table

2 Answers2