Questions tagged [postgresql-9.5]
Specifically for PostgreSQL version 9.5
358 questions
0
votes
0
answers
265
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
717
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
89
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
793
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
282
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
168
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
136
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
72
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
245
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
74
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
425
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