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

767 questions
31
votes
12 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
  • 513
  • 1
  • 5
  • 11
25
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
  • 291
  • 1
  • 3
  • 6
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
  • 725
  • 1
  • 6
  • 12
17
votes
1 answer

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,144
  • 1
  • 15
  • 27
17
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
  • 205
  • 1
  • 2
  • 5
14
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,148
  • 5
  • 34
  • 51
14
votes
1 answer

How to query for empty array in JSONB?

Consider this example: postgres=# CREATE TABLE emptyarray (fields jsonb); CREATE TABLE …
baijum
  • 1,399
  • 1
  • 16
  • 24
14
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
  • 143
  • 1
  • 1
  • 5
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
  • 533
  • 2
  • 7
  • 23
12
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
  • 189
  • 1
  • 7
12
votes
2 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,343
  • 2
  • 20
  • 20
12
votes
1 answer

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
  • 218
  • 3
  • 7
11
votes
3 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
  • 367
  • 1
  • 2
  • 14
11
votes
2 answers

How to implement full text search on complex nested JSONB in Postgresql

I have pretty complex JSONB stored in one jsonb column. DB table looks like: CREATE TABLE sites ( id text NOT NULL, doc jsonb, PRIMARY KEY (id) ) Data we are storing in doc column is a complex nested JSONB data: { "_id": "123", …
rusllonrails
  • 4,850
  • 1
  • 31
  • 27
9
votes
2 answers

Slow nested loop left join with index scan 130k times in loop

I am really struggling to optimize this query: SELECT wins / (wins + COUNT(loosers.match_id) + 0.) winrate, wins + COUNT(loosers.match_id) matches, winners.winning_champion_one_id, winners.winning_champion_two_id, winners.winning_champion_three_id,…
Ferhat Sayan
  • 222
  • 1
  • 5
  • 18
1
2 3
…
51 52