[SOLVED] PostgreSQL ON CONFLICT add new entry with new timestamp

PostgreSQL ON CONFLICT add new entry with new timestamp

I'm trying to make a star schema using postgresql. My issue is that when I get a new entry with a changed e-mail but have the same unique name then I want have an ON CONFLICT to insert the row and just update the timestamp. But I can see that you can only use ON CONFLICT to ignore or to update row.

So what I want is to get from this: Current dataset

to this: What I want

So I have tried here to add a new entry when we have the same name but I'm only able to add them together and not as a new entry.

CREATE TABLE customers (
    user_id serial PRIMARY KEY,
    name VARCHAR UNIQUE,
    email VARCHAR NOT NULL,
    timestamp VARCHAR NOT NULL
);

INSERT INTO 
    customers (name, email, timestamp)
VALUES 
    ('IBM', 'contact@ibm.com', '2016-06-22 19:10:25-07'),
    ('Microsoft', 'contact@microsoft.com', '2016-06-29 11:10:24-07'),
    ('Intel', 'contact@intel.com', '2017-08-22 05:10:26-07')
RETURNING *;

INSERT INTO customers (name, email, timestamp)
VALUES('Microsoft','hotline@microsoft.com', '2019-05-02 19:10:2-07') 
ON CONFLICT (name) 
DO 
   UPDATE SET email = EXCLUDED.email || ';' || customers.email
RETURNING *;

Solution

You expect to have several records for the same name. Then it makes no sense to have that constraint. Instead, you should define a unique constraint for the set of rows that you expect shall not be repeated:

ALTER TABLE customers ADD constraint cms_pk primary key (user_id, name, email)