UPDATE existing row to insert an array of json - postgresql

Question

I have the table a_table with this primary key: PRIMARY KEY (comp_code, name).

To which I am inserting data from a dictionary called officer_item_copy:

{
 'address': 'string',
 'name' : 'a non unique name'
 'appointed_on': '23/2/1988',
 'comp_code': 'OC319508',
 'former_names': [
                 {'forenames': 'string', 
                  'surname': 'string'},
                 {'forenames': 'string', 'surname': 'string'}
                 ],
}

At the column former_names I want to store information about previous names which is a list of dictionaries in Python, so I created the following restriction JSON [], where I want to insert a certain json array.

I understand how to construct an insert statement thanks to this post - but this inserts a new line, whereas i want to insert the array of json in an existing with a certain primary key (name, code).

I'm using this post as reference for my code below.

Here's my code so far:

# cast dictionaries to json objects
json_array = [json.dumps(i) for i in list_of_dicts_former_names]
insert_data = (json_array, )

# update table "a_table"
cur.execute("""UPDATE company_officers_list 
            SET 
                former_names = (%s::json[]), 
            WHERE 
                comp_code = {1} 
            AND
                name ={2}""".format(officer_item_copy["comp_code"],
                                    officer_item_copy["name"]), 
       insert_data)

and my error:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-472-af4c715da33d> in <module>
      7                     name = {}
      8              """.format(officer_item_copy["comp_code"], officer_item_copy["name"]), 
----> 9            insert_data)

ProgrammingError: syntax error at or near "Name"
LINE 7:                     name = Unique Name 2

score 0 · Answer 1 · answered Apr 09 '19 at 07:06

Found it!

Silly mistake in my code, forget to quote the values of the WHERE ... AND statement.

cur.execute("""UPDATE company_officers_list 
                SET 
                    former_names = (%s::json[]) 
                WHERE 
                    comp_code = '{}'
                AND
                    name = '{}'
             """.format(officer_item_copy["comp_code"], officer_item_copy["name"]), 
           insert_data)

Another possible solution, given that i am inserting in an empty field, could have been to use the array_cat function.

cur.execute("""UPDATE company_officers_list 

               SET former_names = array_cat(former_names, (%s::json[])) 

               WHERE comp_code = '{}' 

               AND name = '{}';""".format(officer_item_copy["comp_code"],
                                          officer_item_copy["name"]), 

            insert_data)

This link helped me see examples of the array_cat function:

https://makandracards.com/makandra/36097-postgresql-how-to-add-remove-modify-array-values-and-how-to-replace-1-value-with-multiple-values

and the general update page docs page helped me spot the missing ''

https://www.postgresql.org/docs/9.1/sql-update.html

UPDATE existing row to insert an array of json - postgresql

1 Answers1