https://www.postgresql.org/docs/9.6/sql-insert.html#SQL-ON-CONFLICT
On my new_users table, I have a unique index on email and account where the deleted_at value is null. This allows only one combination of email and account for any row where deleted_at is null.
I want to move those old_user rows to a new_users table, but since the old_users table may have duplicate account/email combinations, I want to ignore them.
This query will essentially "upsert" the data from old_users to new_users. If there are any violation of the unique index for a particular row, that row will be ignored.
Any new_users rows that are soft deleted (deleted_at is NOT null) will be ignored, allowing any duplicate rows to be inserted.
The goal is to run this query many times as needed to keep the new_users table in sync with the old_users table until sometime in the future when we switch from using old_users to using the new_users.
INSERT INTO new_users ( id, email, account )
SELECT id, email, account
FROM old_users
WHERE deleted_at IS NULL
ON CONFLICT
-- this is the unique index definition, "index_expression". From the manual: "Follows CREATE INDEX format".
( email, account)
WHERE deleted_at IS NULL
DO NOTHING;