Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE customers (
  id int
, email text
);

CREATE TABLE customers_email_history(
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY  -- autoincrement!
, customer_id int
, email text
);

INSERT INTO customers VALUES 
  (1, 'john@doe.com')
, (2, 'smith@john.com')
;

INSERT INTO customers_email_history VALUES
  (1, 1, 'test@gmail.com')
, (2, 1, 'john@doe.com')
, (3, 2, 'smith@john.com')
, (4, 2, 'test@john.com')
, (5, 2, 'smith1@john.com')
;

-- sync id sequence
SELECT setval(pg_get_serial_sequence('customers_email_history', 'id'), max(id)) FROM customers_email_history;
2 rows affected

5 rows affected



setval


5


INSERT INTO customers_email_history(customer_id, email)
SELECT id, email
FROM   customers
EXCEPT ALL
(
SELECT DISTINCT ON (customer_id)
       customer_id, email
FROM   customers_email_history
ORDER  BY customer_id, id DESC
);
1 rows affected

TABLE customers_email_history;


id
customer_id
email


1
1
test@gmail.com

2
1
john@doe.com

3
2
smith@john.com

4
2
test@john.com

5
2
smith1@john.com

6
2
smith@john.com

id	customer_id	email
1	1	test@gmail.com
2	1	john@doe.com
3	2	smith@john.com
4	2	test@john.com
5	2	smith1@john.com
6	2	smith@john.com