Can postgreSQL OnConflict combine with JSON obejcts?-CodePudding

I wanted to perform a conditional insert in PostgreSQL. Something like:

INSERT INTO {TABLE_NAME} (user_id, data) values ('{user_id}', '{data}') 
WHERE not exists(select 1 from files where user_id='{user_id}' and data->'userType'='Type1')

Unfortunately, insert and where does not cooperate in PostGreSQL. What could be a suitable syntax for my query? I was considering ON CONFLICT, but couldn't find the syntax for using it with JSON object. (Data in the example)

Is it possible?

CodePudding user response：

You can use INSERT ... SELECT ... WHERE ....

INSERT INTO elbat
            (user_id,
             data)
            SELECT 'abc',
                   'xyz' 
            WHERE NOT EXISTS (SELECT *
                                     FROM files
                                     WHERE user_id = 'abc'
                                     AND data->>'userType' = 'Type1')

And it looks like you're creating the query in a host language. Don't use string concatenation or interpolation for getting the values in it. That's error prone and makes your application vulnerable to SQL injection attacks. Look up how to use parameterized queries in your host language. Very likely for the table name parameters cannot be used. You need some other method of either whitelisting the names or properly quoting them.

CodePudding user response：

Rewrite the VALUES part to a SELECT, then you can use a WHERE condition:

INSERT INTO { TABLE_NAME } ( user_id, data ) 
SELECT
    user_id,
    data 
FROM
    ( VALUES ( '{user_id}', '{data}' ) ) sub ( user_id, data ) 
WHERE
    NOT EXISTS ( 
        SELECT 1 
        FROM files 
        WHERE user_id = '{user_id}' 
        AND data -> 'userType' = 'Type1' 
        );

But, there is NO guarantee that the WHERE condition works! Another transaction that has not been committed yet, is invisible to this query. This could lead to data quality issues.