The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

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

Is it possible to modify postgresql.conf using pg_ctl?

I am programmatically starting PostgreSQL instances using pg_ctl init and pg_ctl start. I need to modify the configuration in postgresql.conf (specifically listen_addresses, and maybe some other ...
2
votes
1answer
384 views

Postgres cascades truncates to unrelated tables

I've created a number of tables in my public schema. Occasionally, I need to run a seeder to refresh the users table when I perform tests. To do this, I'm executing TRUNCATE TABLE users RESTART ...
2
votes
1answer
5k views

INSERT into three tables with many-to-many from one table

With Postgres 9.5 using a trivial example. Given I have this data: Table everything: | fruit | country | |--------|---------| | Banana | USA | | Banana | Panama | How would I create these ...
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
19k 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
45 views

Can you import a Postgresql 9.5.10 db into a Postgresql 10.9 server?

I would like to know if it is safe to import a dump made from a database running on a PostgreSQL server 9.5.10 to a PostgreSQL server 10.9. Basically I am able to do the import. It does not fail. But ...
2
votes
1answer
856 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
881 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
390 views

Postgres not using expression index

I am using Postgres 9.5. It is possible this is related to the changes in 9.6 pointed out by PostgreSQL not using index during count(*), but I cannot be sure yet. I have a table that has a character ...
2
votes
1answer
114 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
1answer
68 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
702 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
40 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
906 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
89 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
166 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
859 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
565 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
699 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
1k 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
2answers
67 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
328 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
484 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
422 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
571 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
304 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
4k 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
196 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
103 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
3k 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
138 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
593 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
235 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
332 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
226 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
7k 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
55 views

Postgres planner overerstimate index only scan

\d hits; Column | Type --------------+----------------------------- id | integer link_id | integer ...
2
votes
0answers
20k 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
3answers
59 views

Take average of multiple nullable columns

I have an example table (year) as below in PostgreSQL 9.5: Name | 2010 | 2011 | 2012 ------------------------- A | 10 | | 40 B | 10 | 20 | 30 Now, if I write a simple query as ...
1
vote
3answers
9k 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
14k 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
2answers
434 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
4k views

PostgreSQL; Update different column on condition using CASE statement

I have a table which has different types of balance and I need to deduct the balance from different columns based on availability. Here is the query (Sample) I tried to use but it didn't work update ...