Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

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

How to find out unused port on Linux server

We are trying to use port 5444 for our new PostgreSQL instance and we are not sure whether 5444 is used or unused. Could you please help us to find the answers to the below questions. We want to ...
0
votes
0answers
14 views

Need Suggesion on PostgreSQL Database Minor upgrade Procedure from 9.5.15 to 9.5.19:

Thanks Alot each and every one. Could you please help us to below points: We are trying to deploy the latest postgres minor version 9.5.19. We have deployed postgres server using yum repository. ...
0
votes
0answers
22 views

select * from schema.table takes longer than explain analyze select * from schema.table from pgAdmin 3 [duplicate]

I have a table which contains 300.000 rows. I am trying to select * from schema.table from pgAdmin and psql. Execution time from the pgAdmin: explain analyze select * from schema.activity; "Seq ...
2
votes
1answer
36 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 ...
0
votes
0answers
23 views

Seq scan and bitmap scan return conflicting results in regex query

Edit: I believe the following thread covers the same issue, and it seems the issue was fixed in 9.6.3: Postgres function index doesn't work correctly with regular expressions. Here's my ...
-1
votes
1answer
25 views

append the text to postgres crosstab query [closed]

Am using below crosstab query: SELECT * from crosstab('SELECT id,task_id, status_id FROM table ORDER BY 1,2') AS final_result(id character varying(50), task1 int,task2 int,task3 int,task4 ...
0
votes
0answers
14 views

pgBouncer exceeds connection limit even with idle connections

I'm using pgBouncer to balance connections in PostgreSQL. Sets a limit of 2048 connections in the PostgreSQL main configuration and also in pgBouncer. It happens that there are several connections ...
4
votes
2answers
235 views

GIN index ignored when query has many (more than 18) OR statements

I have an issue where the execution is not using a GIN index when going over 18 OR statements. This is my index: CREATE INDEX emailjobs_externalreferences_gin ON vsko_mailer_api_prod.emailjobs USING ...
0
votes
0answers
16 views

DDL Logs In Postgresql.Conf File

I want to log only ddl commands to my postgresql .wal files. So, i configured my postgresql.conf file to log those sql queries but it did not work. It cathes every query that works on my postgresql ...
0
votes
4answers
66 views

Postgresql extremely slow count (with index, simple query) [duplicate]

I need to run these simple queries on a table with millions of rows: SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123; SELECT COUNT(*) FROM "subscriptions" WHERE "...
0
votes
0answers
27 views

PostgreSQL managed by AWS RDS - rdsadmin maxusedtransaction ID not shrinking

We are using AWS RDS Postgres. We reached the 200 million limit on one of the DB and triggered VACUUM FREEZE which took down the instance for roughly 40mins however the XID didn't shrink at all. We ...
0
votes
0answers
33 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
1answer
153 views

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

SELECT count(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 WHERE l....
0
votes
1answer
61 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 ...
1
vote
3answers
54 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 ...
0
votes
0answers
34 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
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?...
0
votes
0answers
33 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
23 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 ...
0
votes
0answers
5 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
17 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 ...
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: ...
3
votes
0answers
193 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 ...
0
votes
0answers
30 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, ...
-3
votes
1answer
266 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 ...
0
votes
0answers
23 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
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
18 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 ...
1
vote
1answer
262 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 ...
0
votes
1answer
20 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
50 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 ...
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
10 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
106 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 ...
0
votes
0answers
26 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
38 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 ...
0
votes
0answers
843 views

How to run multiple sql statements in Postgres using the tool PGADMIN?

I have 170 Alter Drop Constraint statement, 120 Truncate table, 120 Insert table and 170 Alter Add Constraint. All of these should be run in one in one script as batch script and I'm using PGADMIN IV ...
-2
votes
1answer
28 views

postgres query to insert data in a table from other table using combination of two common entries of thecolumns in both tables [closed]

I have 2 tables t1 & t2 both containing 3 columns a,b,c the entries in column 'a' and 'b' are same but ordered differently in t1 & t2. I want to use combination of 'a'&'b' entries of t1, ...
3
votes
4answers
221 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
55 views

Slow PostgreSQL query - how to improve it?

[Beginners question :-) hopefully on-topic here.] In one of the customized reports of our system there is a query which is toooo slow (lasting 20+ minutes to return a result). Please help me how to ...
0
votes
1answer
706 views

How to repair corrupted PostgreSQL pg_toast error

The hard drive on my PostgreSQL 9.5 server died, but I managed to recover most of the data. I've restored the database to a solid state hard drive, and I can access most of the data. However, when I ...
2
votes
2answers
66 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) --------------------------------------------------------------...
0
votes
1answer
29 views

postgresql has more DB connection in staging servers than production server

We have postgresql 9.5 where we use it for an application confluence. in staging and in production both max connection is same. But we see staging uses more connection than production e.g staging has ...
2
votes
0answers
305 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 ...
0
votes
1answer
22 views

Update column value divisible by 100

Hello experts I need to update next column value with divisible by 100
2
votes
1answer
88 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 ...
0
votes
1answer
279 views

SQL update multiple rows in destination table with same id but different values from other table

creditNote table id invoice_id generated_at(timestamp) status 10101 111 2018-02-28 14:42:39.247 generated 23982 111 2018-03-30 11:11:11.247 ...
2
votes
1answer
136 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 ...
1
vote
1answer
175 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) ); ...
1
vote
0answers
15 views

After a PC freeze (Windows 7) and disk integrity check, most data from PostgreSQL table was deleted

My development server is on a PC, using 64-bit Windows 7. My PC has been randomly freezing on me lately, requiring a hard reboot (holding down power button until PC turns off) Yesterday, as I was ...