Questions tagged [postgresql-9.5]

Specifically for PostgreSQL version 9.5

Filter by
Sorted by
Tagged with
0 votes
1 answer
38 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 ...
Kyosh Pietro's user avatar
0 votes
1 answer
48 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 ...
Govind Patil's user avatar
2 votes
1 answer
171 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 ...
user2458080's user avatar
0 votes
0 answers
73 views

PostgreSQL 9.5 Create Publication?

Based on the official doc, CREATE PUBLICATION command is available on PostgreSQL 10 and later. However, I have a requirement to replicate PostgreSQL 9.5 to BigQuery using datastream, which require us ...
khgasd652k's user avatar
-1 votes
1 answer
165 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 ...
blad0506's user avatar
0 votes
1 answer
89 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 ...
Alex Jenkins's user avatar
1 vote
0 answers
943 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 ...
zimon's user avatar
  • 23
1 vote
1 answer
107 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, ...
dabadaba's user avatar
  • 133
0 votes
1 answer
45 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 ...
Fatjon Lala's user avatar
-4 votes
1 answer
167 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 ...
ritznew's user avatar
3 votes
1 answer
1k 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 ...
copossum's user avatar
  • 131
0 votes
2 answers
1k 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 ...
Franck White's user avatar
0 votes
2 answers
52 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 ...
FranckT's user avatar
  • 17
1 vote
2 answers
175 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
FranckT's user avatar
  • 17
0 votes
1 answer
1k 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 ...
Luky's user avatar
  • 101
-1 votes
2 answers
1k views

Get distinct values for all columns for all tables from specific schema

I would like to get all distinct values for all columns in each table of a specific schema. First, I summarised all my tables/columns in a new_table (myschema.dico) CREATE TABLE myschema.dico AS ( ...
FranckT's user avatar
  • 17
2 votes
1 answer
846 views

Unused Indexes - PostgreSQL Database [closed]

I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes? SELECT idstat.relname AS ...
NEI's user avatar
  • 31
2 votes
1 answer
180 views

Create cyclic pointers in postgresql 9.5

The problem is to write the "ADD-VALUE" request defined below. For each "cat" value from the table below, keep only 3 records. Figure this table: id cat value updatedAt 1 cat1 v1 ...
Slim's user avatar
  • 237
2 votes
2 answers
3k views

Possible for two simple updates on the same table to deadlock?

Table A is: id integer version varchar data jsonb (large data 1mb) fkToBid integer (references B.id constraint) Table B is: id integer other... Processes are aggressively running the two updates ...
Slim's user avatar
  • 237
0 votes
1 answer
583 views

Postgres DB Timezone Change

Can You please guide us on how we can change the time zone of the Database to UTC which is migrated from AWS which was in time zone UTC? Now the Postgres 12 is installed in Ubuntu 20 which shows (show ...
Jatin Bisht's user avatar
0 votes
2 answers
605 views

PostgreSQL 9.5 database showing signs of corruption in logs, though clients work normally

We have a database server running PostgreSQL 9.5 on a Ubuntu 16.04 LTS box, with the total data size just under 30 GB. We've set archive_mode to on, and archive_command is rsyncing WAL files to ...
JK Laiho's user avatar
  • 145
0 votes
3 answers
287 views

Postgres Specific table access

We have a requirement in our Postgres Database, We want to give specific table permissions to a particular set of user, we also have airflow in our environment which syncs tables, but sometimes any ...
Jatin Bisht's user avatar
0 votes
1 answer
647 views

pg_upgrade from Postgres 9.5.5 to Postgres 12.3 errors out

I am trying to upgrade the database cluster from 9.5.5 to 12.3 using pg_upgrade. I ran it with the --check option, and the result came back "clusters compatible", but when I run it with the -...
sharadov's user avatar
  • 342
0 votes
1 answer
4k views

Drop tables but space not claimed in postgres 12

I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link (-k) option. So basically I am having two data directories i.e. One is old data directory (v9.5) ...
erTugRul's user avatar
  • 153
1 vote
3 answers
1k views

Postgres 12 drastically slower than postgres 9.5 with identical queries from import/export

I setup a new postgres database (v12) by export/importing from an old one (v9.5). When I query the new one, it's drastically slower despite being on a more powerful machine. Shared buffers are set to ...
Caleb Pitman's user avatar
0 votes
1 answer
848 views

PostgreSQL: pg_upgrade 9.5 to 13 failing with "connection to database failed: FATAL: database "template1" does not exist"

I'm trying to upgrade a 9.5 server to 13 but the pg_upgrade process is failing after the main checks. Server is running CentOS 8 on EC2 and is using the pgdg repo installs of each server. bash-4.4$ /...
Jeremy Wilson's user avatar
1 vote
2 answers
748 views

Postgres Upgrade Issues with pg_upgrade

I am trying to upgrade 9.5.5 to 12.3 using pg_uprade, got the following error Old and new pg_controldata maximum relation segment sizes are invalid or do not match 9.5.5 Blocks per segment of large ...
sharadov's user avatar
  • 342
0 votes
1 answer
352 views

Performance issues when INSERTing in one table, but not another that has many more indexes

I have the following table DDLs (names changed): (note the UUID PK) CREATE TABLE myschema.item ( item_id uuid NOT NULL DEFAULT uuid_generate_v4(), ref_id uuid NOT NULL, type_id uuid NOT ...
user991710's user avatar
0 votes
1 answer
72 views

Problem Insert Data to connected tables postgresql

I have 2 existing tables: ‘projects’ and ‘units’.The two tables are connected through the project id. In the table 'projects' the column is called ‘id’ and generates every time a new project is ...
Malks's user avatar
  • 1
0 votes
0 answers
96 views

Poor performance in a SELECT using Inheritance

I will detail my problem in good detail. I have a marketplace application so I make a decision to make a multitenant per schema approach. I like this approach because of the isolation of data, but I'm ...
wavrik's user avatar
  • 101
0 votes
1 answer
811 views

could not connect to server: can't connect postgres on the server to postico on my local

when I connect postgres on the server to postico on my local I get this error could not connect to server: Operation timed out Is the server running on host "http://159.65.65.203/" (92.242....
Amer Bearat's user avatar
5 votes
2 answers
3k views

How can I safely rename a sequence in PostgreSQL, ideally without downtime?

We want to rename some sequences in our production database for certain reasons. Is there a way to do this safely in production without having to first close all connections to the database? It's ...
Henrik N's user avatar
  • 197
0 votes
1 answer
976 views

Default Postgres privileges not showing on \ddp

As far as I understand there are set of default Postgres public privileges. I have created user and granted him privilege using: CREATE USER foo WITH PASSWORD 'foo'; \connect bar; GRANT SELECT ON ALL ...
Dusan Gligoric's user avatar
0 votes
0 answers
34 views

Returning the column names used in Query

I am working with a tool that connects to PostgreSQL, which returns the result of a query in a multi-dimensional array, but, I need the name of the columns used in the query, is there any way to ...
Alexandre's user avatar
  • 121
0 votes
1 answer
5k views

Postgresql: Permission denied to schema to user created based on master with rights

I'm trying to write a function that eventually will rotate users. Currently I have the code below which works and creates a new user using the prior user which ultimately has the same rights as the ...
user1013388's user avatar
1 vote
1 answer
3k views

What does pg_stat_all_tables.idx_scan actually mean?

We have a busy PostgreSQL 9.5 server with OLTP-like traffic where pg_stat_all_tables.idx_scan values go up with considerate rate while we're having performance problems. Does that (idx_scan ...
Mikko Rantalainen's user avatar
0 votes
1 answer
203 views

Gap Finding - Dates

Essentially i face a islands & gaps problem but unlike many of the examples i found i do not have a unique ID to group over to find my gaps. Data is timestamp without timezone formatted and an ...
Alex Vermeulen's user avatar
0 votes
0 answers
2k views

Error: duplicate key value violates unique constraint, but the column does not have a unique constraint? postgreSQL

By the way, I'm trying to automate the below using python, but I've isolated the SQL code and tried it by itself on pgAdmin III, and it still doesn't work. So I have a table called tableName, and I ...
throwaway2020's user avatar
1 vote
1 answer
2k views

What does it mean when Postgres query state is null

What does it mean when a query state is null? my_database_name> SELECT pid, datname, usename, application_name, query_start, state, state_change FROM pg_stat_activity; +-------+------------------+-...
ATN's user avatar
  • 113
0 votes
1 answer
124 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 ...
nosferatu's user avatar
  • 101
6 votes
3 answers
16k 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 ...
barteloma's user avatar
  • 161
1 vote
1 answer
1k 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 ...
Mansoor's user avatar
  • 129
2 votes
1 answer
337 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 ...
Richard's user avatar
  • 245
0 votes
0 answers
314 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, ...
Carlos Delgado's user avatar
0 votes
1 answer
65 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,
RutherA's user avatar
0 votes
2 answers
54 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 ...
user2569831's user avatar
0 votes
1 answer
231 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 ...
Siva's user avatar
  • 1
0 votes
0 answers
45 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. ...
Siva's user avatar
  • 1
2 votes
1 answer
1k 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 ...
Francesco Fazio's user avatar
-1 votes
1 answer
464 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 ...
RutherA's user avatar

1
2 3 4 5
8