Question 253175 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Postgres 11 update query on conflict

Suppose I have an update query for a table with constraints (Postgres 11).

I do not know the constraints so I won’t be able to determine whether an update query will fail or not.

If an update query failes due to a conflict I want to do nothing and return 0 (no rows were updated).

With an insert you can have ‘INSERT INTO … ON CONFLICT DO NOTHING’ but this option does not exist in an update query.

Is there a solution?

Tags:

2

Answers


  1. 0

    You could try using a CTE to get the result of the the UPDATE statement, and then return the updated rows using RETURNING *, but if it fails due to a conflict, it returns nothing.

    WITH updated_rows AS (
  UPDATE your_table
  SET column1 = 'new_value'
  WHERE condition
  RETURNING *
)
SELECT COUNT(*) AS rows_updated FROM updated_rows;
    Login or Signup to reply.
  2. 0

    Not a direct answer but there is no reason to make a statement like: I do not know … so I won’t be able to …. Instead that should prompt the question: How do I …. In this case that would be How do I determine the constraints on a table? The the following is one such way:

    select table_name, constraint_name, constrant_definition
  from (select distinct on(con.conrelid::regclass::text,con.conname,con.contype)
                con.conrelid::regclass::text  table_name
              , con.conname                   constraint_name
              , con.contype
              , pg_get_constraintdef(con.oid) constrant_definition
           from pg_catalog.pg_constraint con
           join pg_catalog.pg_class      rel on rel.oid = con.conrelid
           join pg_catalog.pg_namespace  nsp on nsp.oid = con.connamespace
          where nsp.nspname = 'public'
            and rel.relname = 'non_standard_calendar'
       ) sq
 order by table_name
        , case when contype = 'p' then 1 
               when contype = 'u' then 2 
               when contype = 'f' then 3
               when contype = 'c' then 4 
               else 5
          end;

    Then with that information you can pose a better question to your issue, or make the question mute.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search