On 11/19/2016 11:33 AM, Kim Rose Carlsen wrote: >> AFAIK, EXCLUDED is only available in a trigger function: > >> >> https://www.postgresql.org/docs/9.5/static/trigger-definition.html >> >> You are using EXCLUDED in a regular function so it would not be found. >> >> Can you also show the failure for your alternate method? > > From the manual > https://www.postgresql.org/docs/9.5/static/sql-insert.html > > " > conflict_action > conflict_action specifies an alternative ON CONFLICT action. It can be > either DO NOTHING, or a DO UPDATE clause specifying the exact details of > the UPDATE action to be performed in case of a conflict. The SET and > WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row > using the table's name (or an alias), and to rows proposed for insertion > using the special excluded table. SELECT privilege is required on any > column in the target table where corresponding excluded columns are read. > " > Oops, my mistake. I should have spent more time on the examples. Changing the function to; CREATE OR REPLACE FUNCTION public.upsert_job(job jsonb) RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $function$ BEGIN INSERT INTO jobs AS origin VALUES( (job->>'id')::INTEGER, COALESCE(job->>'employee_name'::TEXT, 'test_name'), COALESCE(job->>'address'::TEXT, 'test_address'), job->>'phone_number'::TEXT ) ON CONFLICT (id) DO UPDATE SET employee_name = COALESCE(EXCLUDED.employee_name, origin.employee_name), address = COALESCE(EXCLUDED.address, origin.address), phone_number = COALESCE(EXCLUDED.phone_number, origin.phone_number); END; $function$ ; makes it work. So looks like constraints are checked before you get to the ON CONFLICT section. -- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
