Sfrgttk

Why did the Mercure fail?

GraphicsGrid with a Label for each Column and Row

How can "mimic phobia" be cured or prevented?

Should I stop contributing to retirement accounts?

Count the occurrence of each unique word in the file

What was the exact wording from Ivanhoe of this advice on how to free yourself from slavery?

When were female captains banned from Starfleet?

Store Credit Card Information in Password Manager?

Approximating irrational number to rational number

Pre-mixing cryogenic fuels and using only one fuel tank

What does routing an IP address mean?

Multiplicative persistence

Has any country ever had 2 former presidents in jail simultaneously?

C++ debug/print custom type with GDB : the case of nlohmann json library

How do you make your own symbol when Detexify fails?

Why Shazam when there is already Superman?

Is there a name for this algorithm to calculate the concentration of a mixture of two solutions containing the same solute?

Which one is correct as adjective “protruding” or “protruded”?

Delivering sarcasm

Why does the Sun have different day lengths, but not the gas giants?

What should you do if you miss a job interview (deliberately)?

Electoral considerations aside, what are potential benefits, for the US, of policy changes proposed by the tweet recognizing Golan annexation?

The IT department bottlenecks progress. How should I handle this?

Why is it that I can sometimes guess the next note?

Postgres complex insert or update with 2 unique constraints

0

I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,

external_id: string

disabled_by: string 

Additional notes:

1. All columns have the not null constraint.


2. unique constraint ('external_id_constraint_1') (external_id, disabled_by)


3. unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

1. an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.


2. update does not have the ability to do the insert if there is nothing to update


3. update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.

postgresql

share

asked 6 mins ago

PatPat

1011

New contributor

Pat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,

external_id: string

disabled_by: string 

Additional notes:

1. All columns have the not null constraint.


2. unique constraint ('external_id_constraint_1') (external_id, disabled_by)


3. unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

1. an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.


2. update does not have the ability to do the insert if there is nothing to update


3. update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.

postgresql

share

asked 6 mins ago

PatPat

1011

New contributor

Pat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I have a table that has these key columns. Other columns are present but these are the key columns for this question.

user_id: string,

external_id: string

disabled_by: string 

Additional notes:

1. All columns have the not null constraint.


2. unique constraint ('external_id_constraint_1') (external_id, disabled_by)


3. unique constraint ('external_id_constraint_2') (user_id, external_id)

Operations:

Add external id

Adding an new external id for user 1 it would look like this:

insert into external_ids (user_id, external_id, disabled_by) values ('user1', 'exid2', '');

Disabling an external id

If the external id is later revoked for user 1:

update external_ids set disabled_by='admin1' where user_id='user1' and external_id='exid2';

Reenabling the external_id

A user can't have more than 1 entry for a given external_id. If a row exists ('user1', 'exid2', 'admin1') then the row needs to be updated:

update external_ids set disabled_by='' where user_id='user1' and external_id='exid2';

Rather than a new row being created. However another user between the disable and the reenable attempt could have claimed the external_id. If this has happened the update should do nothing.

Solutions attempted

I am trying to get a single postgres sql statement to handle this case.

Things I tried:

1. an insert into .... on conflict on constraint ... except that I need to handle 2 different constraint violations differently.


2. update does not have the ability to do the insert if there is nothing to update


3. update doesn't have the ability to handle a constraint violation.

Question:

Is it possible to do this in a single sql statement or am I doomed to be frustrated.

postgresql

share

asked 6 mins ago

PatPat

1011

New contributor

Pat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

user_id: string,

external_id: string

disabled_by: string 

share

asked 6 mins ago

PatPat

1011

New contributor

Pat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 6 mins ago

PatPat

1011

New contributor

Pat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 6 mins ago

PatPat

1011

New contributor

Pat is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 6 mins ago

PatPat

1011