Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
0
votes
1answer
59 views

How to pivot multiple columns in PostgreSQL 9.5?

I have the sample data as follows and want to find if I can use crosstab function in this case. Sample Data: ITEM | CD | TYPE |PARTS | PART | CNT | Item 1 | A | AVG |2 | 1 | 10 | Item ...
0
votes
0answers
13 views

Unit postgresql.service failed to load: No such file or directory

When i try to connect to my postgres database, i get the error below: could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "10.2.1.96" and accepting ...
1
vote
0answers
86 views

Postgres table with 35m rows slow queries. how can i improve the performance?

SELECT count(DISTINCT c.id) as clickCount FROM clicks c LEFT JOIN links l on c.link_id = l.id LEFT JOIN user_agents ua on c.user_agent_id = ua.id AND ua.robot IS NULL ...
1
vote
3answers
51 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 ...
2
votes
1answer
166 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 | ...
4
votes
1answer
1k views

Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

I have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over ...
1
vote
1answer
119 views

How to synchronize a Windows database for Linux?

I have some database (PostgreSQL 9.5) on a Windows server, and I would like this data to be synchronized to a Linux server, that is connected in an application. Is it possible to synchronize?
1
vote
1answer
87 views

Postgis distance filter does not work as expected

I have a issue with simple PostGIS query. Here is a query: SELECT id, location, ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance FROM pins WHERE ...
5
votes
1answer
281 views

PostgreSQL - How does multicolumn B-Tree index work with order by on 1st column and IN lookup for 2nd?

I created such table (similar to example from http://use-the-index-luke.com/sql/example-schema/postgresql/performance-testing-scalability ) CREATE TABLE scale_data ( section NUMERIC NOT NULL, ...
8
votes
4answers
35k views

Delete all data in Postgres database

I have created a fresh db dump from a production server with the --data-only and --column-inserts flags, so I only have a bunch of insert statements to insert data when performing a restore on a ...
2
votes
1answer
136 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 ); ...
0
votes
0answers
33 views

Select query taking too much time in postgresql

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU): create table stat_data ( name varchar NOT NULL, the_date date NOT NULL, uopen numeric, uhigh ...
0
votes
1answer
1k views

Cannot login to postgres with newly created users

I have created a number of users in postgres using createuser --interactive --password but whenever I run sudo -i -u newuser I receive the following error: sudo: unknown user: staging sudo: unable to ...
1
vote
1answer
200 views

max_stack_depth not changed even stack changed in limits.conf

The attempt of making changes to max_stack_depth using select set_config('max_stack_depth', '512MB', false); because of the error shown in postgresql log: DETAIL: "max_stack_depth" must not exceed ...
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", "...
0
votes
1answer
13 views

argument 'and' incorrect, sum values of attributes

excuse the disturbance, but a comparison does not give me a boolean value, because the sum of the attributes does not return an integer value, but bigint. How do you add up the values of three queries?...
2
votes
2answers
393 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 ...
0
votes
0answers
32 views

how to insert an if check in an insert

I have an after insert at the statment level, which must insert the positive or negative result in an attribute based on the count of the values of other attributes. But control structures cannot be ...
0
votes
1answer
194 views

PostgreSQL 9.5 and PostGIS on OSX

I have installed PostgreSQL 9.5 on OSX using the EnterpriseDb package. How do I install PostGIS on it? The official PostGIS OSX Page says that: The OSX PostgreSQL/Stackbuilder combination from ...
0
votes
1answer
21 views

how to get column count in postgreSQL

i have a query below select emp_id, sum(case when leave_type = 'Planned' then 1 else 0 end) Planned, sum(case when leave_type = 'Not Informed' then 1 else 0 end) NotInformed, sum(case when ...
2
votes
3answers
215 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 ...
0
votes
0answers
4 views

Postgres break table columns into multiple tables having unique rows

I have this table called transactions. It has two columns id and report_id. id is primary key. I want to create two new tables transaction_ids and report_ids both with just one column id. Both these ...
0
votes
1answer
15 views

How to calculate quantity of WAL in db without archiving?

I would like to find out a quantity of WAL files which are being used in PostgreSQL database in last 24h. I was assigned to do Log-shipping but before I do it I want to know how many WAL files are ...
1
vote
2answers
9k views

Start, stop, configure PostgreSQL through Mac terminal

I am having basic problems using PostgreSQL on my Mac OSX Yosemite, through the terminal. I tried google-ing but this is becoming increasingly difficult and info very scattered so I keep running into ...
0
votes
1answer
15 views

function postgresql

I am analyzing a function in PostgreSQL and I come across a function that takes two parameters but I did not understand how they called, on the second parameter the function is the following: ...
1
vote
1answer
529 views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
0
votes
1answer
387 views

postgres_fdw and postgres version upgrade

Case explained I'm facing difficulties concerning performance of queries using a Postgres 9.5.4 database set up on a different server connecting via Foreign Data Wrapper to another instance of ...
1
vote
1answer
377 views

pg_repack slows down PostgreSQL replication

I have a master PostgreSQL 9.5 server and a standby server. For replication I use repmgr (WAL streaming). Typically the delay between master and standby is <5s: $ psql -t -c "SELECT extract(epoch ...
3
votes
0answers
419 views

pg_restore error: Extensions installed out of sequence

I have a PostgreSQL 9.5 server with the pg_cron extension installed. I'm creating a backup of a database named testdb using the command: $ pg_dump -c -U postgres -Fc -f test.dump testdb When I ...
3
votes
0answers
150 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 ...
7
votes
2answers
10k views

Does a COMMIT work within an anonymous plgpsql function in PostgreSQL 9.5?

I am importing a large number of large files into a number of tables to be partitioned using loops within an anonymous plpgsql code block $do$. $do$ BEGIN FOR yyyy in 2012..2016 THEN ...
10
votes
1answer
12k views

Querying JSONB in PostgreSQL

I have a table, persons, which contains two columns, an id and a JSONB-based data column (this table has just been made for demonstrational purposes to play around with PostgreSQL's JSON support). ...
0
votes
0answers
27 views

DROP DEFAULT on psql restore fails because column not existing anymore

A dump creates a command to remove default values like: ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT; When on restore the column with the default value do not exist anymore, ...
1
vote
1answer
163 views

Truncation vs Deletion When `session_replication_role = replica`

When I first run SET session_replication_role = DEFAULT; CREATE TABLE users ( id serial PRIMARY KEY ); CREATE TABLE posts( id serial PRIMARY KEY, user_id INTEGER REFERENCES users(id) ); ...
-3
votes
1answer
225 views

Postgres is Throwing a False Foreign Key Constraint Violation? [closed]

I am trying to delete a LocalTax record from Postgres. Currently, I don't think there are any Employee records that reference the LocalTax in question (8). => SELECT COUNT(*) FROM employees WHERE ...
14
votes
2answers
6k views

PostgreSQL UPSERT issue with NULL values

I'm having an issue with using the new UPSERT feature in Postgres 9.5 I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can ...
12
votes
1answer
13k views

Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

Default values are, according to documentation for min_wal_size and max_wal_size parameters: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this parameters ...
0
votes
0answers
20 views

Postgis KNN in Postgres 9.5

I'm trying to use the Postgis KNN functionality as described here: https://postgis.net/docs/geometry_distance_knn.html I have one table with all point geometries. The table has many samples, and in ...
1
vote
2answers
74 views

How do I return two aliases from the same table query?

How would I do something like this: SELECT * from notifications WHERE read=false AS unread_notifications and WHERE read=true AND deleted=false AS read_notifications; Result Example: ...
1
vote
1answer
50 views

Query performance depends on parameter value

In the system I'm working with there are many type hierarchies, where each type corresponds to a table. For example f_dp_rkkbase -> f_dp_rkk -> f_dp_inputrkk. To distinguish between different ...
0
votes
0answers
17 views

Connection lockup on syntax error Postgresql 9.5 on Ubuntu 16.04 server

I have postgresql 9.5 running on my server with mostly default configuration. Whenever I'm connected to the server through localhost or 127.0.0.1 postgres fails to return the error, be it a simple ...
0
votes
1answer
17 views

Why doesn't `vacuum full` release space immediately?

On Postgres 9.5 I had a table that grew to 30GB, but it was ultimately mostly dead rows. After cleaning it up with a vacuum full, postgres reports that the table size is now around 500MB. However, I ...
1
vote
1answer
49 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 ...
1
vote
1answer
223 views

Exclusion constraint on ENUM errors: data type dow has no default operator class for access method “gist”

Following in the style of this, I'm trying to create an exclusion constraint over an ENUM, CREATE TYPE dow AS ENUM ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' );...
0
votes
0answers
46 views

Slow query with order by join table

I have a SQL with multiple join left, because some data is empty. However, I need to sort the data using a string column from a join table. I thought about creating an index for this table, but this ...
0
votes
0answers
9 views

Incrementing copy of pg_stat_all_tables

This feels like a 'how long is my piece of string?' question. I have just realised I have an unexpected table 'stats' in my 'logs' schema. This table is a copy of pg_stat_all_tables with an extra ...
3
votes
1answer
94 views

Issue with Aggregate Function

I want to insert a tuple in my leggeapprovata table, but this table takes the attributes from 4 other tables. One of these 4 tables is the votazione table, from which it takes 6 attributes. Two do not ...
3
votes
4answers
191 views

Postgres LIKE query using a GiST index is just as slow as a full scan

What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but ...
0
votes
0answers
23 views

Performance issue of scheduled Java app after migration from Oracle to Postgresql 9.5

I have a strange issue, that I hope I can get some help on. I'm not too advanced in Postgresql so maybe I'm missing obvious things as well. Our company is planning to move from Oracle to Postgresql, ...
0
votes
0answers
37 views

What determine how much time the query will cost?

I'm developer a system, and the database was not made for me. In the beginning all query of the database are super fast, because i don't have any data. Along the project the database grown, but the ...