UPDATED CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB) RETURNS JSONB AS $$ DECLARE result JSONB; v RECORD; BEGIN result = val1; FOR v IN SELECT * FROM jsonb_each(val2) LOOP IF result @> jsonb_build_object(v.key,v.value) THEN result = result – v.key; ELSIF result ? v.key THEN CONTINUE; ELSE result = result || jsonb_build_object(v.key,’null’); END IF; END LOOP; … Read more
I believe xmax::text::int > 0 would be the easiest trick: so=# DROP TABLE IF EXISTS tab; NOTICE: table “tab” does not exist, skipping DROP TABLE so=# CREATE TABLE tab(id INT PRIMARY KEY, col text); CREATE TABLE so=# INSERT INTO tab(id, col) VALUES (1,’a’), (2, ‘b’); INSERT 0 2 so=# INSERT INTO tab(id, col) VALUES (3, … Read more
If you use the –clean option of pg_restore, the old tables will be dropped before the new ones are created. If you do not use the –clean option, you will get an error message that the table already exists, but pg_restore will continue processing unless you use the –exit-on-error option. If your goal is to … Read more
Use array_append function to append an element at the end of an array: UPDATE table1 SET integer_array = array_append(integer_array, 5); 5 is a value of choice, it’s of an integer datatype in your case. You probably need some WHERE clause as well not to update the entire table. Try below to see how it works: … Read more
Basically because it wasn’t possible to retroactively change how views work. I’d like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists. You can filter access to the view its self with row security normally. The tables accessed by the view will … Read more
The ON CONFLICT construct requires a UNIQUE constraint to work. From the documentation on INSERT .. ON CONFLICT clause: The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or … Read more
To add the value use the JSON array append opperator (||) UPDATE jsontesting SET jsondata = jsondata || ‘[“newString”]’::jsonb WHERE id = 7; Removing the value looks like this UPDATE jsontesting SET jsondata = jsondata – ‘newString’ WHERE id = 7; Concatenating to a nested field looks like this UPDATE jsontesting SET jsondata = jsonb_set( … Read more
I think that this is an interesting question that deserves an in-depth answer; please bear with me if it is a little bit lengthy. In short: Your guess is right, and you can use the following RETURNING clause to determine if the row was inserted and not updated: RETURNING (xmax = 0) AS inserted Now … Read more
If you look at your log output, the following lines appear towards the end: local-postgres9.5 | server stopped local-postgres9.5 | local-postgres9.5 | PostgreSQL init process complete; ready for start up. Apparently, stopping and restarting the Postgres server is part of the initialisation process. In fact, the second-to-last line says local-postgres9.5 | LOG: database system is … Read more
In UPDATE use delete (-) and concatenate (||) operators, e.g.: create table example(id int primary key, js jsonb); insert into example values (1, ‘{“nme”: “test”}’), (2, ‘{“nme”: “second test”}’); update example set js = js – ‘nme’ || jsonb_build_object(‘name’, js->’nme’) where js ? ‘nme’ returning *; id | js —-+————————- 1 | {“name”: “test”} 2 … Read more