Postgresql

Introduction This is a work-in-progress. Reader discretion is adviced! Client library Use the newest Npgsql library - ref documentation for nuget package and recommended use. Make sure to use NpgsqlDataSource that was introduced in Npgsql 7.0. Configure npgsql with DI as described here (missing in the standard docs at the time of writing). Commands (Npgsql) Do not concatenate User-Defined types as strings into the SQL commands. (SQL injection preventation) Parameters Use parameters instead of embedding values in the SQL. »

Update json property Generic need: update json property (not top level) Example case: undelete soft deleted instance by updating the IsSoftDeleted boolean property update storage.instances set instance = jsonb_set(instance, '{Status, IsSoftDeleted}', 'false') where org = 'serviceowner' and instance -> 'InstanceOwner' ->> 'PersonNumber' in ('ssn1', 'ssn2') Delete json property Generic need: delete json field (not top level) Example case: undelete soft deleted instance by removing the SoftDeleted timestamp property update storage.instances set instance = instance::jsonb #- '{Status, SoftDeleted}' where org = 'serviceowner' and instance -> 'InstanceOwner' ->> 'PersonNumber' in ('ssn1', 'ssn2') »