If you’re able to upgrade to Postgresql 9.5, the jsonb_set command is available, as others have mentioned. In each of the following SQL statements, I’ve omitted the where clause for brevity; obviously, you’d want to add that back. Update name: UPDATE test SET data = jsonb_set(data, ‘{name}’, ‘”my-other-name”‘); Replace the tags (as oppose to adding … Read more
In short – use JSONB instead of JSON or cast JSON to JSONB. You cannot compare json values. You can compare text values instead: SELECT * FROM movie_test WHERE tags::text=”[“dramatic”,”women”,”political”]” Note however that values of type JSON are stored as text in a format in which they are given. Thus the result of comparison depends … Read more
Use pg_drop_replication_slot: select pg_drop_replication_slot(‘bottledwater’); See the docs and this blog. The replication slot must be inactive, i.e. no active connections. So if there’s a streaming replica using the slot you must stop the streaming replica. Or you can change its recovery.conf so it doesn’t use a slot anymore and restart it.
There are two operations to get value from JSON. The first one -> will return JSON. The second one ->> will return text. Details: JSON Functions and Operators Try SELECT (json_data->’position’->>’lat’)::float + 1.0 AS lat FROM updates LIMIT 5
SELECT jsonb_array_length(‘[“question”,”solved”]’) AS length; or SELECT json_array_length(‘[“question”,”solved”]’) AS length;
If you’re able to upgrade to Postgresql 9.5, the jsonb_set command is available, as others have mentioned. In each of the following SQL statements, I’ve omitted the where clause for brevity; obviously, you’d want to add that back. Update name: UPDATE test SET data = jsonb_set(data, ‘{name}’, ‘”my-other-name”‘); Replace the tags (as oppose to adding … Read more