0

I'm trying to perform an INSERT operation with an ON CONFLICT DO UPDATE clause in PostgreSQL. My table contains column names with aliases (e.g., "ITEM_CODE(material_code)"), and I want to update the corresponding fields when a conflict occurs on "ITEM_CODE(material_code)". Here's the SQL query I'm using:

INSERT INTO api_itemmaster (
    "ITEM_CODE(material_code)",
    "ITEM_NAME(material_name)",
    "ITEM_GROUP_CODE",
    "UNIT(base_unit)",
    "PACK(pack_size)",
    "ITEM_CLASS_CODE(material_type)",
    "DIVISION_CODE(division)",
    "SEGMENT_CODE",
    "CAT_CODE",
    "ACTIVE",
    "FORMT",
    created_on,
    updated_on,
    active_state
) VALUES (
    '5000',
    'OSKAR',
    '225',
    'STR',
    '15',
    'ZF',
    '8000',
    '30000',
    '004',
    '1',
    '20',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP,
    false
) ON CONFLICT ("ITEM_CODE(material_code)") 
DO UPDATE SET
    "ITEM_NAME(material_name)" = EXCLUDED."ITEM_NAME(material_name)",
    "ITEM_GROUP_CODE" = EXCLUDED."ITEM_GROUP_CODE",
    "UNIT(base_unit)" = EXCLUDED."UNIT(base_unit)",
    "PACK(pack_size)" = EXCLUDED."PACK(pack_size)",
    "ITEM_CLASS_CODE(material_type)" = EXCLUDED."ITEM_CLASS_CODE(material_type)",
    "DIVISION_CODE(division)" = EXCLUDED."DIVISION_CODE(division)",
    "SEGMENT_CODE" = EXCLUDED."SEGMENT_CODE",
    "CAT_CODE" = EXCLUDED."CAT_CODE",
    "ACTIVE" = EXCLUDED."ACTIVE",
    "FORMT" = EXCLUDED."FORMT",
    updated_on = EXCLUDED.updated_on;

However, the ON CONFLICT DO UPDATE clause doesn't seem to work, and no updates are applied to the existing row.

Ensured that the table and column names are correct. Verified that the ITEM_CODE(material_code) constraint is unique in the table. Confirmed that the syntax aligns with PostgreSQL's ON CONFLICT documentation.

Improve this question
4
  • Your syntax seems OK. Are you getting an error message? To help you further we need an MRE. Your MRE should provide DDL statements to create table and constraints and insert statements to add sample data Commented Nov 19, 2024 at 9:03
  • 1
    My table contains column names with aliases - a table-column-name isn't an alias by definition. You only have an alias if you rename a column when selecting from a table/view/etc. Regarding your question - if the ON CONFLICT isn't being run then you must be getting "duplicate unique value" errors. Commented Nov 19, 2024 at 9:07
  • How do you know that you are getting conflicts in the first place? Commented Nov 19, 2024 at 16:07
  • Welcome to the SO community. The community will help you with issues, but there are some expectation of you. Please spend a few minuets to Take the Tour and review How to Ask. Then update your question to include table definition(s) (DDL), sample data, and expected results. All as formatted text or even better a fiddle, but - no images. Commented Nov 19, 2024 at 18:15
Related questions
1698
How can I do an UPDATE statement with JOIN in SQL Server?
1111
PostgreSQL error: Fatal: role "username" does not exist
888
How to do an update + join in PostgreSQL?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.