0

Given this table

create table contacts (
    customer int not null,
    phone char not null,
    facebook_id char not null
    /* some other fields */
)
  • I'd like to make sure there can't be two rows with the same (customer, phone) or (customer, facebook_id) or (customer, phone, facebook_id).
  • If there is a duplicate (customer, phone) during an INSERT, I'd like to update facebook_id with the incoming value, and the other fields.
  • If there is a duplicate (customer, facebook_id) during an INSERT, I'd like to update phone with the incoming value, and the other fields.
  • If there is a duplicate (customer, phone, facebook_id) during an INSERT, I'd like to update other fields.

I could create 3 separate unique indexes but I wouldn't be able to use the ON CONFLICT... DO UPDATE clause which need a single index inference. I can't either omit the index inference (and let Postgres bump into the first conflict) because DO UPDATE needs it.

How should I solve this problem ?

PS: empty '' values can be inserted and must be considered null (i.e. should not be considered duplicates). I can make those fields nullables if necessary.

0

1 Answer 1

1

This looks like flawed requirements. I suggest you split and have two tables, one with (customer, phone) to store the phones and another with (customer, facebook_id) to store the facebook ids of each customer.

If you also want to have the original table with the 3-column unique key, in order to store what combinations of phone and facebook ids each customer is using, you can keep it as well.

The suggestion becomes - I also assume you have a customer table:

create table customer (
    customer_id int not null,
    constraint customer__PK
        primary key (customer_id),
    customer_name text
    /* other customer attributes */
) ;

create table contact_phone (
    customer_id int not null,
    phone text not null,
    constraint contact_phone__PK
        primary key (customer_id, phone),
    constraint contact_phone__customer__FK
        foreign key (customer_id) 
        references customer,
    phone_source text
    /* other attributes related to customer - phones */
) ;

create table contact_facebook (
    customer_id int not null,
    facebook_id text not null,
    constraint contact_facebook__PK
        primary key (customer_id, facebook_id),
    constraint contact_facebook__customer__FK
        foreign key (customer_id) 
        references customer,
    facebook_source text
    /* other attributes related to customer - facebook */
) ;

and your original contacts renamed to contact_combo:

create table contact_combo (
    customer_id int not null,
    phone text not null,
    facebook_id text not null,
    constraint contact_combo__PK
        primary key (customer_id, phone, facebook_id),
    constraint contact_combo__contact_phone__FK
        foreign key (customer_id, phone) 
        references contact_phone,
    constraint contact_combo__contact_facebook__FK
        foreign key (customer_id, facebook_id) 
        references contact_facebook,
    combo_source text
    /* other attributes related to customer     */
    /* and combination of phone and facebook_id */
) ;

The INSERT procedure is going to be slightly complicated and depending on requirement details (e.g. do you allow a customer to be stored with only phone and without facebook, is it possible to have a customer without known pone or facebook, etc) but there will be no problem with using ON CONFLICT .. DO UPDATE as each table has a single unique constraint.

1
  • After a lot of thoughts and for the sake of simplicity I'm going to merge both fields into one (external_id) and add a external_id_kind field (either phone_number or facebook_psid). It's not likely a contact would've had both phone_number and facebook_psid fields filled in anyway. Thanks for spending time writing your solution which I'm sure would work but would've been too complex for the use case.
    – ryancey
    Commented Sep 22, 2022 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.