Questions tagged [postgresql-9.5]
Specifically for PostgreSQL version 9.5
329
questions
0
votes
0answers
12 views
PostgreSQL performance benchmark results of version 9.5 and above [closed]
We are trying to evaluate Postgres version 9.5 and above in terms of performance benchmarks.
Could someone please share links/stats of official Postgres performance benchmark results on hardware for ...
0
votes
0answers
16 views
Is there any tool/extension available in PostgreSQL to monitor the growth of DB and objects? [closed]
We are looking for a procedure to monitor the DB growth.
Could someone please help us to track the growth of the DB and Objects on a daily base in PostgreSQL DB?
0
votes
1answer
60 views
Secure postgres 9.5 database packed on Windows desktop application
My desktop app includes a postgres database and a front app. I need the db to be accessed only by my front app. The credentials of this user are going to reside in the front app binaries.
I realized ...
1
vote
2answers
57 views
Postgresql query is very slow for my big table
My database version is postgresql 9.5.
create table if not exists request_log
(
id bigserial not null constraint app_requests_pkey primary key,
request_date timestamp not ...
1
vote
1answer
27 views
Improve Performance by Left Join and Group By Count - Postgresql
I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN.
How do I improve the left join?
I am using the below index
create index session_opportunity_index on public.session ...
2
votes
1answer
23 views
Postgres not using multicolumn index for queries on leftmost columns?
I'm working in Postgres 9.5.15 and have a table called project:
id | integer | | not null | nextval('project_id_seq'::regclass)
user_id ...
0
votes
0answers
13 views
Insert the value of column in another column as jsonb
I'm trying to insert the value of one column as the value for another column of type jsonb, so this is what I currently have:
INSERT INTO "Customers" ("id", "priceList", "metadata")
SELECT
id,
...
0
votes
1answer
14 views
how to take postgres server backup skipping some of databases?
I have PostgreSQL server version 9.5. Contains 20 Databases.
I want take server backup skipping of some 10 databases.
Am using pg_dumpall, but it was taking full server backup.
thanks,
0
votes
2answers
43 views
SQL cannot add columns to query
I have the following query (PostgreSQL 9.5.19):
with userlist as (
select a.ips,
array_agg(t.super_user_id) as super_users
from tmp_table_1 t
left join unnest(t.ips) as a(ips) on ...
0
votes
1answer
25 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
18 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
99 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
24 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
32 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
81 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
247 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
24 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
89 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
56 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
440 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
167 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
65 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
64 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
52 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
19 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
39 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
45 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
14 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
19 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
17 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
407 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
45 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
380 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
29 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
443 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
33 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
54 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 ...
3
votes
1answer
133 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
31 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
39 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
1k 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
31 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, ...
4
votes
4answers
500 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
56 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
1k 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
70 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)
--------------------------------------------------------------...
