<postgresql> inserting data from temporary table to main table with json values-CodePudding

I am using postgresql and I am trying to insert into the main table from a temporary table.

In the temp table I have 2 columns: md5sum (varchar and not null) and values (json data).

I tried these queries:

insert into main_table SELECT distinct * FROM temp ON CONFLICT (md5sum) DO NOTHING;

and

insert into main_table(md5sum, values)
    select distinct md5sum, values
    from main_table
         where not exists (
            select md5sum, scores
            from temp
            where 
               temp.md5sum = main_table.md5sum);

But I keep getting this error: could not identify an equality operator for type json.

How to solve this error?

CodePudding user response：

You cannot do distinct on json type. The workaround is to cast it to jsonb. Try if the following works for you.

insert into main_table 
SELECT distinct md5sum, scores::jsonb 
FROM temp ON CONFLICT (md5sum) DO NOTHING;