Questions tagged [postgresql-9.5]
Specifically for PostgreSQL version 9.5
358 questions
0 votes
0 answers
271 views
Postgres usage always using 100 cpu usage
In certain interval (like 60 minutes), postgres always uses 100%cpu. This problem is just starting last Thursday. I had to manually to kill the pid through HTOP. The one that makes me confused is ...
0 votes
1 answer
751 views
idle_in_transaction_session_timeout Parameter changing
I should change the parameter "idle_in_transaction_session_timeout" in my RDS postgresql. As I know with set command("SET idle_in_transaction_session_timeout=5s") I will change it ...
0 votes
1 answer
91 views
How to get variable value in function PostgreSQL in an SQL statement
I am setting a parameter with SET app.id To :appId; CREATE FUNCTION get_car_Price(price_from int, price_to int) RETURNS integer LANGUAGE plpgsql AS $$ DECLARE car_count integer; BEGIN ...
- 13
2 votes
1 answer
819 views
Using pg_repack to remove OIDs from tables in PostgreSQL
I'm prepping a legacy PostgreSQL 9.5 database for upgrade. I need to remove OIDs from tables without locking the tables for long periods of time. I have developed a strategy that seems to work, but I ...
- 123
-1 votes
1 answer
284 views
How to upgrade from psql 9.5 to 12 with short downtime and no corruption
We have two databases in two different servers with PostgreSQL 9.5 One database size is 800GB and the other is 1.8 TB. What would be the most efficient and safe way of upgrading both clusters from ...
- 1
0 votes
1 answer
177 views
Simplify duplicate INSERT commands placing NULL for values that are not found
I have many trigger functions written in PL/pgSQL that follow the below logic: SELECT my_name INTO l_my_name FROM data WHERE data.my_number = NEW.test_num; IF NOT FOUND THEN INSERT INTO test_event ...
1 vote
0 answers
1k views
Why this aggregate function cannot be converted with pg_upgrade from PostgreSQL 9.5 to PG 14.3?
I am trying to port old PostgreSQL v9.5 database (or family of databases) to PostgreSQL v14.3, with pg_upgrade utility. After many hassles with old v9.5 binaries, source files (had to be recompiled to ...
- 23
1 vote
1 answer
143 views
Why is this simple query making up 50% execution time?
I'm using postgresql 9.5. I installed the pg_stat_statements extension to analyze queries and got the following surprising result: scheduler=# SELECT substring(query, 1, 30) AS query, ...
- 133
0 votes
1 answer
76 views
Unable to locate and login to by PostgreSQL
I am using Ubuntu 20.04 LTS, and I have configured PostgreSQL 9.5 to work on the server. I am trying to log in but I have forgotten the password for the user postgres and therefore I tried to reset it ...
-4 votes
1 answer
248 views
SQL Server to PostgreSQL equivalent [closed]
I need some help in converting or equivalent of below in postgreSQL.. From MSSQL code to postgreSQL:- CONVERT(bit,CASE WHEN column_a & 1 > 0 THEN 1 ELSE 0 END) as ABC CONVERT(tinyint,CASE ...
- 1
5 votes
2 answers
2k views
List PostgreSQL extensions per database
I have a Postgres 9.5 server with 30 databases. I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin. I know that SELECT * FROM ...
- 151
0 votes
2 answers
2k views
How to find values missing from a sequence?
I want to find rows missing from a sequence of values in my table. For example, in this picture documentno 'YD4BC2006008' is missing. I want to find the missing lines via c_order_id and documentno. I ...
0 votes
2 answers
88 views
Detect multiple valid date periods for same id, interleaved with non-valid periods
My table structure and some sample data (test table), as well as my desired output (results) are available here. When I do SELECT distinct colony, min(date_check), max(date_check) from test where ...
- 17
1 vote
2 answers
429 views
count distinct values over columns
I struggle to get the result for here is my dataset A B C cnt 2 1 2 1 5 5 2 1 4 1 1 1 result expected A B C cnt 2 1 2 2 1 5 5 2 2 1 4 3 1 1 1 1 PG 9.5
- 17
2 votes
1 answer
2k views
how to call lo_unlink(lo) in batches
In Postgres 9.5 I am tying to reproduce the postgres tool vacuumlo to get rid of unreferenced large object to free disk space. References are stored in text type columns by hibernate so using the tool ...
- 141