Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
2
votes
1answer
2k views

PostgreSQL 9.5, getting “cached plan must not change result type” error

I had a really odd and unexpected behaviour in PostgreSQL. Debugging I found that the problem only occurred while including CREATE TEMP TABLE ... inside a function, and by using an alternative (in ...
2
votes
2answers
17k views

Increasing Postgresql 9.5 max_connections to at least 300

Right now I am using the default postgresql.conf which has maximum connections set to 100. I need to increase max_connections to 300. Postgresql 9.5 is the only thing running on my Linode. I am ...
2
votes
1answer
2k views

Create Materialized View from foreign table

I'd like to create a simple materialized view from a table which lies in a different database. The two databases are on the same server. What do I have to add to make the query access the foreign ...
2
votes
1answer
3k views

Postgresql 9.5 BRIN index dramatically slower than expected

I have a table with over 12 million rows of log data and have migrated to Postgres 9.5 to take advantage of the new BRIN index because I have disk space limitations. I assumed that my situation was ...
2
votes
1answer
658 views

PostgreSQL - can I use logical replication from a replica's WAL?

I would like to use logical replication. However, as PostgreSQL works now, it can eat up the whole disk and then block the database. Especially in Amazon Web Services RDS, this is a problem because ...
2
votes
2answers
678 views

Postgresql large transaction OOM killer

We have a Postgresql 9.5 running on CentOS release 6.5 (Final) The server has 16GB of ram. We are running a long transaction which adds and updates 10s of millions of rows in multiple tables. Besides ...
2
votes
1answer
106 views

Does PostgreSQL rewrite JOINs with OR's into UNIONs?

(Abbreviated) table summary: -- Table cases: id SERIAL PRIMARY KEY, application_number VARCHAR(30) NOT NULL, publication_number VARCHAR(30) NOT NULL, -- ...
2
votes
2answers
8k views

PostgreSQL: ANY (VALUES(…)) in WHERE clause causes drastic slowdown

Okay, I previously asked a question regarding a large dataset but it was never answered, so I decided to cut it down and ask about a smaller subset of the previous setup and simplify what I am trying ...
2
votes
1answer
60 views

naming a column differently then its original one in foreign data wrapper of PostgreSQL

I have two databases. In each database I have an employee table which differ from each other in the number of columns and column names. Here is what the table looks in the first database: Emp_ID ...
2
votes
1answer
2k views

Creating Synonym For a Table in PostgreSQL over dblink?

I have two PostgreSQL databases connected over dblink extension. I want to create synonym for a table in first database so the queries that are coming through application I can route them to the table ...
2
votes
1answer
616 views

Unable to remotely connect to Postgresql 9.5 instance

I am struggling to connect to a remote Postgresql 9.5 instance running on Ubuntu 16.10. The usual: psql: could not connect to server: Connection timed out Is the server running on host "xx....
2
votes
2answers
2k views

Returning a json array slice in PostgreSQL

Postgres 9.5 Given a table with one field of type json, about 700 rows, and where each row has about 4,000 elements in a single array... my_db_field: [0.44577, 0.4855, 0.45429, 0.54437,...] [0....
2
votes
1answer
38 views

Create range from column

I have a list of positions: pos 123 100024 1025 5426 I want to define a range of these positions E.g. 100-1000 1001-2500 etc... So when i do a Select of how many positions there are in that ...
2
votes
2answers
785 views

Effectively searching through entire 1 level nested JSONB [closed]

Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level). How does one effectively optimize a query to ...
2
votes
1answer
1k views

index using btree_gin on smallint/bigint

I try to use a btree_gin index on smallint column, but the index is never used for queries. According to documentation (http://www.postgresql.org/docs/9.5/static/btree-gin.html) it should work. If I ...
2
votes
1answer
84 views

How can it be possible that sum operation is so much faster than count?

When I make a simple benchmark test in a table that has 11.065.763 records. I get the results below: select sum(t.amount * t.exchange) from table t; Finalize Aggregate (cost=658027.39..658027.40 ...
2
votes
1answer
102 views

Cannot run psql in postgresql 9.5

I am using Postgresql 9.5 on Ubuntu 16.04 LTS. I receive the below error when I type psql: a psql: could not connect to server: No such file or directory Is the server running locally and accepting ...
2
votes
1answer
747 views

How to upgrade postgres 9.5 with non default data directory and checksum enabled to 10.1

I am trying to upgrade postgres 9.5.4 to 10.1 on Ubuntu 14. The data directory for 9.5.4 is /var/lib/postgresql/data/postgresql0 The steps that I have followed Install postgres 10.1 ubuntu@van-...
2
votes
1answer
475 views

Using SELECT within to_tsvector call in CREATE INDEX

I'm attempting to create a GIN index for full-text search on a field that may have text in it that is one of several different languages. The languages are pre-set and we'll know which language we ...
2
votes
1answer
641 views

Streaming Replication failing from standby with connection issue

I'm setting up streaming replication between a primary and standby server. On the standby server, when I start up the instance I see that replication isn't taking place. Looking at the log, I see the ...
2
votes
2answers
910 views

Postgres inherit from one role and not another

I'm a little new with postgres but have a decent understanding of roles. What I have are users that inherit from their normal "data_analyst" group. This group is used to manage all data analyst users ...
2
votes
0answers
119 views

Restoring from PostgreSQL 9.5 dump to 10 is super slow

I am trying to restore database dump created on PostgreSQL 9.5.16 to PostgreSQL 10.7 and it is taking a lot of time to restore. Restoring the same dump to 9.5 doesn't take that much. So I am wondering ...
2
votes
2answers
60 views

How to select blocks with sequential data and aggregate the ids

I have the following table: id(int) startDate(timestamp) endDate(timestamp) plan_id(int) planned(bool) machine(int) --------------------------------------------------------------...
2
votes
0answers
235 views

Does VACUUM FULL ANALYZE a partition locks the main table in postgresql?

I'm using postgresql 9.5 and partman for partitioning tables. there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition ...
2
votes
2answers
350 views

Delete records if not present in subquery

I want to delete records from a table that are not found in a subquery. But it's not having the desired result. The table has multiple rows per SKU. Each price row has a valid_from and valid_to date ...
2
votes
0answers
398 views

Bulk Updates (inserts, update) and check constraints

Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
2
votes
0answers
518 views

PostgreSQL Cluster vs PostgreSQL Master-Slave Replication

I want to setup PostgreSQL and PostGIS to run some experiments (geospatial queries). If I setup a PostgreSQL cluster, this means that the read requests will be distributed across the nodes of the ...
2
votes
0answers
270 views

Primary key violation

We see records in a table that violate primary key and unique key constraints. What could have caused it? How can we track the problem (what needs to be logged/recorded to understand the situation ...
2
votes
1answer
3k views

Pull Data from one database and insert to another database ( same schema )

I am currently working on moving data from one database to another in PostgreSQL. I am studying python with psycopg2 to connect and run SQL queries in one database. My problem is how can I pull and ...
2
votes
1answer
159 views

Setting up a cascading trigger on a parent table

Let's say I have started to keep a log of all the postcards that I send so I define the table postcards: Table "postcards" Column | ...
2
votes
1answer
84 views

PostgreSQL RULEs and NEW pseudo relation

I'm struggling to implement class-like hierarchy storage on top of PostgreSQL. Currently I am creating a single table for each class with descendant tables containing pkey and fields diff. Consider a ...
2
votes
1answer
2k views

How to insert nested JSON arrays into a table?

I'm trying to insert a JSON array that came from another JSON array (array of arrays) into a table using json_populate_recordset(), but I'm receiving the following error: SQL Error [22023]: ERROR: ...
2
votes
1answer
132 views

PostgreSQL: simple equality query returning no results for indexed columns

I have a very large table (around 140M rows) with five columns: CREATE TABLE IF NOT EXISTS citations ( citing_id text, citing_issn text, cited_id text, cited_issn text, year int ); ...
2
votes
0answers
531 views

Any way to squeeze performance from Postgres JSONB queries?

This is using RDS Postgres 9.5.2 We have a table which contains about 25 columns, and one is a jsonb col that essentially is a simple/flat json object (key/value, not complex objects). The data in ...
2
votes
3answers
208 views

Execution Performance Issue with Similar Query

I have been having some performance issues running a simple query on PostgreSQL on a table that has about 3 million rows with a join on a table that has about 120 rows. If I run a query only on the ...
2
votes
0answers
310 views

Pgpool II and Postgresql upgrade scenario using different major versions

Pgpool FAQ states that: You cannot mix different major versions of PostgreSQL, for example 8.4.x and 9.0.x And the reason that I understand very well is: Pgpool-II assumes messages from ...
2
votes
0answers
1k views

How to effectively monitor PostgreSQL 9.5 Replication?

Background: I've got quite some experience running and automating MySQL Clusters with several independent replication streams. However I rarely had to run an PostgreSQL replication myself. They were ...
2
votes
0answers
1k views

Why is this dockerized Postgres not able to authenticate with a password?

I am working on changes to an Alpine fork of the official Docker postgres image ( https://hub.docker.com/r/deinspanjer/docker-postgres95-alpine34/ ). It has been working fine for a while, but I ...
2
votes
0answers
219 views

Postgresql row-level security generates different query plan from manually inserted WHERE clause

(Copied from my post to the postgresql mailing list https://www.postgresql.org/message-id/CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com. Unfortunately I need at least 10 ...
2
votes
1answer
6k views

Postgresql one master multiple slaves failover

My problem is same as the one asked in below topic Streaming Replication Failover - how to point second slave at new master? Currently I am working on Postgresql 9.5, I have one master and two ...
2
votes
0answers
54 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
2
votes
0answers
18k views

List of escape sequences for Postgresql string data types

I'm trying to find the documentation of a complete list of escape sequences for string data types in Postgresql. So for example, if you need to escape a quote character inside of a quoted string, you ...
1
vote
2answers
13k views

How To Remove Spaces Between Characters In PostgreSQL?

I want to match two tables based on a specific column which is a card number, there are spaces between characters in some of card numbers in the second table like below: 'NES 123_4_5' 'MTS 678_9_0' ...
1
vote
3answers
8k views

How to update complex jsonb column?

I have a table with following definition: create table json_test ( filter_data jsonb); and i insert to it values like this: '{"task_packets": [ { "state": "PROCEEDING", "...
1
vote
2answers
406 views

Uninstalling / upgrading PostgreSQL on OSX

The problem I have PostgreSQL 9.3 installed on my MacBook Pro. I don't remember how I installed it (there are numerous ways), and now I want to upgrade to PostgreSQL 9.5. Foresnsics The running ...
1
vote
1answer
55 views

how to combine multiple timestamps and keep the most recent one?

I have a table with many timestamps in it, some of which may be null. Is there a way to combine them all and keep the most recent timestamp so I don't have to reference each timestamp individually? ...
1
vote
1answer
95 views

Repeat total count value

I have a table called paths that looks like this: id parent_id -- --------- 1 null 2 1 3 2 4 3 5 4 6 5 7 5 8 5 9 ...
1
vote
1answer
3k views

`ON CONFLICT DO UPDATE` causing deadlocks?

I have a project where I'm trying to use the PostgreSQL ON CONFLICT DO UPDATE clause, and I'm having huge numbers of deadlocking issues. My schema is as follows: webarchive=# \d web_pages ...
1
vote
3answers
139 views

Select all before first break

I have a table with three columns: id, sort, name in PostgreSQL 9.5 database. I need to select all rows starting from sort=1 and before the first break (ordered by sort). For example I have: id | ...
1
vote
1answer
47 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...