On Tue, Feb 5, 2019 at 11:15 AM <mcs...@gmail.com> wrote: > > Using: > SQLAlchemy 1.2.1 > PostgreSQL 10.6 > > I have a table with a pk and jsonb data and would like to do an upsert that > merges the json data > > Example SQL: > > INSERT INTO json_data (id, data) > VALUES (%(id)s, %(data)s) > ON CONFLICT ON CONSTRAINT json_data_pk > DO > UPDATE SET data = json_data.data::jsonb || %(data)s; > > Example python: > > from sqlalchemy import Column, String, Table > from sqlalchemy.dialects.postgresql import insert, JSONB > > table = Table( > 'json_data', > SqlDatabase.get_metadata(), > Column('id', String, primary_key=True), > Column('data', JSONB, nullable=False), > ) > insert_stmt = insert(table).values( > id="test", > data={'key','value'} > ) > update_stmt = insert_stmt.on_conflict_do_update( > constraint=table.primary_key, > set_=dict(data=???) > ) > > How do I reference the existing jsonb data and merge it with the new data in > the on_conflict_do_update call?
note the cast to JSONB is probably not needed because that column is already defined as JSONB. To reference a column, you just use it: table.c.data.concat("my data") see below from sqlalchemy import Column, String, Table, MetaData from sqlalchemy.dialects.postgresql import insert, JSONB from sqlalchemy.dialects import postgresql m = MetaData() table = Table( "json_data", m, Column("id", String, primary_key=True), Column("data", JSONB, nullable=False), ) insert_stmt = insert(table).values(id="test", data={"key", "value"}) update_stmt = insert_stmt.on_conflict_do_update( constraint=table.primary_key, set_=dict(data=table.c.data.concat("my data")), ) # requested: """INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT ON CONSTRAINT json_data_pk DO UPDATE SET data = json_data.data::jsonb || %(data)s;""" # prints: """ INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT (id) DO UPDATE SET data = (json_data.data || %(data_1)s) """ print(update_stmt.compile(dialect=postgresql.dialect())) # prints: """ INSERT INTO json_data (id, data) VALUES (%(id)s, %(data)s) ON CONFLICT ON CONSTRAINT json_data_pk DO UPDATE SET data = (json_data.data || %(data_1)s) """ update_stmt = insert_stmt.on_conflict_do_update( constraint="json_data_pk", set_=dict(data=table.c.data.concat("my data")) ) print(update_stmt.compile(dialect=postgresql.dialect())) > > Thanks, > Steve > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.