Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.4]

for PostgreSQL questions specific to version 9.4

Version 9.4 of was released Dec. 18, 2014.

Major improvements on the previous versions include:

  • Allow materialized views to be refreshed without blocking reads
  • Logical change-set extraction allows database changes to be optionally recorded in logical format
  • Allow background workers to be dynamically registered, started and terminated
  • Add structured (non-text) data type (JSONB) for storing JSON data
  • Add SQL-level command ALTER SYSTEM command to edit the postgresql.conf configuration file

The official documentation for this version is available at: http://www.postgresql.org/docs/9.4/static/index.html

1293 questions
238
votes
11 answers

How to perform update operations on columns of type JSONB in Postgres 9.4

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns. Documentation for JSONB types and functions:…
jvous
  • 2,383
  • 2
  • 12
  • 4
90
votes
1 answer

Calculate JSONB Array Length Using PostgreSQL 9.4

I'm running the latest version of PostgreSQL 9.4.5-1.pgdg14.04+1, and am attempting to calculate the length of a JSONB array using the JSON_ARRAY_LENGTH function as described in the PostgreSQL 9.4 Documentation Here is the exact query I'm attempting…
Joshua Burns
  • 8,268
  • 4
  • 48
  • 61
65
votes
7 answers

How to convert PostgreSQL 9.4's jsonb type to float

I'm trying the following query: SELECT (json_data->'position'->'lat') + 1.0 AS lat FROM updates LIMIT 5; (The +1.0 is just there to force conversion to float. My actual queries are far more complex, this query is just a test case for the…
fadedbee
  • 42,671
  • 44
  • 178
  • 308
57
votes
2 answers

How to delete replication slot in postgres 9.4

I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | xmin…
Igor Barinov
  • 21,820
  • 10
  • 28
  • 33
49
votes
1 answer

Operator does not exist: json = json

when I try to select some record from a table SELECT * FROM movie_test WHERE tags = ('["dramatic","women", "political"]'::json) The sql code cast a error LINE 1: SELECT * FROM movie_test WHERE tags = ('["dramatic","women",... …
Isaac
  • 689
  • 1
  • 6
  • 13
37
votes
3 answers

postgres: Index on a timestamp field

I'm new to postgres and I have a question about the timestamp type. To set the scene, I have a table like the one below: CREATE TABLE IF NOT EXISTS tbl_example ( example_id bigint not null, example_name text, example_timestamp…
rm12345
  • 1,089
  • 3
  • 18
  • 32
33
votes
4 answers

Postgres jsonb 'NOT contains' operator

I'm experimenting with postgres jsonb column types, and so far so good. One common query I'm using is like this: select count(*) from jsonbtest WHERE attributes @> '{"City":"Mesa"}'; How do I reverse that? Is there a different operator or is it…
user101289
  • 9,888
  • 15
  • 81
  • 148
28
votes
3 answers

Create nested json from sql query postgres 9.4

I need to get as a result from query fully structured JSON. I can see in postgres that there are some built in functions that may be useful. As an example I created a structure as follows: -- Table: person -- DROP TABLE person; CREATE TABLE…
Snorlax
  • 787
  • 2
  • 9
  • 22
28
votes
1 answer

Difference between set, \set and \pset in psql

I get a little confused some times when working with psql between when to use a set vs. \set vs. \pset. I think that: set is for session variables on my connection to the db. For example SET ROLE dba; \set is for local variables for this psql…
David S
  • 12,967
  • 12
  • 55
  • 93
27
votes
2 answers

Postgres GROUP BY on jsonb inner field

I am using Postgresql 9.4 and have a table test, with id::int and content::jsonb, as follows: id | content ----+----------------- 1 | {"a": {"b": 1}} 2 | {"a": {"b": 1}} 3 | {"a": {"b": 2}} 4 | {"a": {"c": 1}} How do I GROUP BY on an…
JGem
  • 377
  • 1
  • 3
  • 7
26
votes
1 answer

What is the second argument in array_length() function?

Postgresql 9.4 has functions for array. One of them is array_length(anyarray, int). It get two argumetns. What is the second argument? In all examples it has value 1. But nowhere says what it is.
Haru Atari
  • 1,502
  • 2
  • 17
  • 30
24
votes
1 answer

How to create an empty JSON object in postgresql?

Datamodel A person is represented in the database as a meta table row with a name and with multiple attributes which are stored in the data table as key-value pair (key and value are in separate columns). Simplified data-model Now there is a query…
cansik
  • 1,924
  • 4
  • 19
  • 39
24
votes
1 answer

What is Create User and Create Role in Postgresql?

Please explain what is the meaning of Create User and Create Role in PostgreSQL i am new at PostgreSQL. I try to learn bye myself i understand Create User mean that user who are able to access database cluster and mange it in with in same computer…
Muhammad Raza
  • 847
  • 1
  • 8
  • 22
24
votes
2 answers

Does JSONB make PostgreSQL arrays useless?

Suppose that you want to store "tags" on your object (say, a post). With release 9.4 you have 3 main choices: tags as text[] tags as jsonb tags as text (and you store a JSON string as text) In many cases, 3rd would be out of question since…
comte
  • 3,092
  • 5
  • 25
  • 41
21
votes
1 answer

Flatten aggregated key/value pairs from a JSONB field?

I am working in Postgres 9.4 with the following table: Column │ Type │ Modifiers ─────────────────┼──────────────────────┼────────────────────── id │ integer │ not null default practice_id │…
Richard
  • 62,943
  • 126
  • 334
  • 542
1
2 3
…
86 87