Stack Overflow
Stack Overflow

Questions tagged [postgresql-9.6]

for PostgreSQL questions specific to version 9.6

On 29 September 2016, PostgreSQL 9.6.0 was released. Major enhancements in PostgreSQL 9.6 include:

  • Parallel sequential scans, joins and aggregates

  • Elimination of repetitive scanning of old data by autovacuum

  • Synchronous replication now allows multiple standby servers for increased reliability

  • Full-text search for phrases

  • Support for remote joins, sorts, and updates in postgres_fdw

  • Substantial performance improvements, especially in the area of improving scalability on many-CPU servers.

Use this tag for questions specific to any of the above new features.

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

830 questions
48
votes
1 answer

Postgresql query for objects in nested JSONB field

I am using PostgreSQL 9.6, and I have a table named "ItemDbModel" with two columns looks like: No integer, Content jsonb Say I put many records like: "No": 2, {"obj":"x","Item": {"Name": "BigDog", "Model": "NamedHusky", "Spec":"red dog"}} "No":…
ifdog
  • 541
  • 1
  • 4
  • 6
38
votes
13 answers

pgAdmin won't start (eternal loading)

Once upon a time I had a Postgres database that worked with pgAdmin. I have a webservice running on a WildFly server that made connections to the DB and everything worked fine. After a while(a few months) I have tried to open pgAdmin again and it…
pedroth
  • 603
  • 1
  • 7
  • 16
28
votes
2 answers

Postgresql | remaining connection slots are reserved for non-replication superuser connections

I am getting an error "remaining connection slots are reserved for non-replication superuser connections" at one of PostgreSQL instances. However, when I run below query from superuser to check available connections, I found that enough connections…
YogeshR
  • 1,606
  • 2
  • 22
  • 43
22
votes
1 answer

Postgresql | No space left on device

I am getting space issue while running a batch process on PostgreSQL database. However, df -h command shows that machine has enough space below is the exact error org.springframework.dao.DataAccessResourceFailureException:…
YogeshR
  • 1,606
  • 2
  • 22
  • 43
21
votes
1 answer

When we need return value from trigger procedure?

Documentation about trigger procedures (https://www.postgresql.org/docs/9.6/static/plpgsql-trigger.html) says: "A trigger function must return either NULL or a record/row". Example CREATE TRIGGER my_trigger AFTER INSERT ON table_name FOR EACH ROW…
roy rigan
  • 245
  • 1
  • 2
  • 5
19
votes
1 answer

PostgreSQL "tuple already updated by self"

Our database seems to be broken, normally it uses about 1-2% of cpu, but if we run some additional backend services making UPDATE and INSERT queries for 10M rows table (about 1 query per 3 second) everything is going to hell (including CPU increase…
Damian Gądziak
  • 895
  • 1
  • 9
  • 12
19
votes
1 answer

How to query for empty array in JSONB?

Consider this example: postgres=# CREATE TABLE emptyarray (fields jsonb); CREATE TABLE …
baijum
  • 1,609
  • 2
  • 20
  • 25
16
votes
3 answers

query.on is not a function

I am trying to learn how to use javascript to connect to a postgresql database but when I try to log a query to the console using query.on(...), I get a type error that says "query.on is not a function". I have searched extensively on how to resolve…
tvonk13
  • 163
  • 1
  • 1
  • 5
16
votes
2 answers

How to use `jsonb_set` on column with null values

I am using Postgres 9.6 and I have a JSONB column in which some rows have NULL value and some have dict values like {"notify": false}. I want to update the column values with more dictionary key/value pairs. UPDATE accounts SET notifications = …
iffi
  • 258
  • 3
  • 7
15
votes
3 answers

Error in creating SEQUENCEs when restoring the PostgreSQL database

UserX has following grants: CREATE ROLE "UserX" LOGIN PASSWORD 'pass'; CREATE DATABASE "DBX" WITH OWNER="UserX" ENCODING='UTF8' TABLESPACE=pg_default CONNECTION LIMIT=-1; GRANT CONNECT ON DATABASE "DBX" TO "UserX"; GRANT USAGE ON SCHEMA public TO…
Babak
  • 3,716
  • 6
  • 39
  • 56
15
votes
2 answers

Globally replace in Postgres JSONB field

I need to globally replace a particular string that occurs multiple places in a nested JSON structure, thats stored as jsonb in a postgres table. For example: { "location": "tmp/config", "alternate_location": { "name": "config", …
crowhoplaminar
  • 213
  • 1
  • 2
  • 8
14
votes
4 answers

How to install Postgis to a Keg installation of Postgres@9.6 using Homebrew?

I have installed Postgresql@9.6 and Postgis via Homebrew. However, installing Postgis via Homebrew installs the latest version of Postgresql at 10 as dependency and pinning Postgresql at 9.6.5 blocks the install of Postgis via Homebrew. Performing…
carliecope
  • 319
  • 2
  • 7
14
votes
3 answers

Querying Postgres 9.6 JSONB array of objects

I have the following table: CREATE TABLE trip ( id SERIAL PRIMARY KEY , gps_data_json jsonb NOT NULL ); The JSON in gps_data_json contains an array of of trip objects with the following fields (sample data…
zeisi
  • 5,520
  • 2
  • 22
  • 21
13
votes
4 answers

Postgresql update json data property

I created a field name is result and type is text. I just want to update 'lat' in column. When I use this query I get syntax error. How can I do? The column data…
Fatih Doğan
  • 417
  • 1
  • 4
  • 16
13
votes
3 answers

How to replicate foreign key of another table in one to many relationship

I have a three table structure: tournament, group and team. The tournament and group tables have a one-to-many relation, and group and team have a one-to-many relation as shown below. How do i replicate the value of the tournament_id from group…
pinch boi triggered af
  • 543
  • 2
  • 7
  • 26
1
2 3
…
55 56