Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
20 views

docker postgres 9.6 cannot append to pg_hba.conf

I have an instance of postgresql running in a docker container that I use for development, so it's regularly torn down and built up again. End of last week, I suddenly started getting an error when ...
2
votes
1answer
68 views

Very bad query plan in PostgreSQL 9.6

I have a performance problem with PostgreSQL 9.6.17 (x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit). Sometimes very inefficient query plan is chosen for ...
0
votes
1answer
31 views

Postgresql - Identify process issuing a query

With this table Table "scheduled_message" Column | Type | Collation | Nullable | Default ----------------+--------------------------+-------...
0
votes
1answer
30 views

Perform update and raise notice in Postgres Function

I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened. I don'...
0
votes
2answers
76 views

Add auto increment to already existing primary key column PostgreSQL

I have a database schema of the following table: database=# \d person Table "public.person" Column | Type | Modifiers -------------+----------------------...
0
votes
1answer
24 views

Error while connecting psql to PostgreSQL 9.6

I am using Postgresql 9.6 and getting an error while connecting psql. Error- ./psql psql.bin: FATAL: the database system is starting up. What could be the issue? In the log it says 2021-02-18 13:23:...
0
votes
0answers
38 views

Update all the sequences

I had to restore a PostgreSQL 9.6 database from dump files (created with the command pg_dump --dbname=$DATABASE_URL --username=postgres --file=/usr/postgres-dump --format=directory). After the restore ...
2
votes
3answers
181 views

Select a row from one table, if it doesn't exist, select from another table

How can I select a row from one table, but if it doesn't exist, fall back to selecting it from a secondary table? Here's a dumbed down version of what I'm trying to achieve create table table_1 ( ...
0
votes
1answer
32 views

How can you recursively query across 2 columns?

Sorry my DBA skills are a bit limited, but here's an example schema (simplified from my real table) create table test ( id serial primary key, person TEXT, start_point TEXT, ...
0
votes
1answer
16 views

How I can check whether 2 databases despite being in different postgresql servers of versions are the same?

I copied an existing posttgresql database from one server to another. The source of my data is a postgresql 9.6 whilst the newer server is in postgresql 11. So I want to know how I can check that both ...
3
votes
1answer
202 views

postgres: could not receive data from WAL stream: ERROR: requested WAL segment has already been removed

Configuration: postgres 9.6 with a 3 cluster node. db1 is the master, db2 and db3 are replicas. WAL files are archived in AWS S3 using custom pgrsync tool. Cluster managed by patroni. The ...
1
vote
1answer
26 views

Execution plan favorize joins over sort

recently I ran into an issue on our production server (Pg 9.6) and even though I found solution for my problem I would like to understand better the reason behind it. For easier explanation I am using ...
1
vote
2answers
73 views

Postgres prefers slow Seq Scan over fast Index Scan

Tables I have the following two tables. books, which contains ~5 million rows: Table "public.books" Column | Type | ...
0
votes
1answer
42 views

Slow query / wrong query plan for SORT BY LIMIT LEFT JOIN

I have a following 3 tables a,b,c all with compound unique key (application_id, tid) however all those tables are not related with foreign key (it's a limitation of our solution). All tables has ...
0
votes
0answers
36 views

Can't refresh Materialized View Concurrently

I'm trying to run REFRESH MATERIALIZED view CONCURRENTLY my_view in Postgresql, and I'm getting the following error: SQL Error [XX000]: ERROR: unexpected attlen: -2 Where: SQL statement "SELECT ...
0
votes
1answer
343 views

SQL Error [42501]: ERROR: permission denied for table

I am very new to postgres so please my apologies in advance if I sound naive. I am still trying to learn. I am trying to create a readonly role and then create a role and assign readonly role to the ...
1
vote
0answers
39 views

Flags Cloud SQL for Postgresql

Context: I have a process that runs daily, with different amounts of data, if the process is on the weekday (process last 30 days), for weekends (process last year). The input is of 10 tables of ...
0
votes
1answer
46 views

postgres cluster: Crash of master crashes the replicas too

Environment: Postgres version: 9.6 Cluster of 3 servers, with Patroni and etcd Scenario: When index of tables were started with 16 parallel requests (on a 16CPU machine), postgres on the master ...
0
votes
1answer
181 views

Why does an alias with a 'having' clause not exist in PostgreSQL?

I am trying to calculate distance between two coordinates and fetching some information based on a few conditions. A similar query with the 'having' clause was working in MySQL. But why is it not ...
1
vote
1answer
53 views

Query to find if there are more than X occurences within any period of given length minutes

I have spent many hours thinking about a solution for my problem but I give up. Let's imagine a table user_id | occurred_at -- OK match example 1 | 2020-01-01 08:00:00 <- First match of ...
2
votes
1answer
131 views

ERROR: role “user1” cannot be dropped because some objects depend on it

On my Google Cloud SQL database instance (Postgres 9.6), I am unable to drop a user/role via the GCP Console that was created via the GCP Console. When I try, the Console shows a notification that ...
0
votes
2answers
18 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 ...
0
votes
0answers
33 views

Postgresql - extremely slow basic SELECT query

I have a materialized view with about 1.6 million entries that gets refreshed every hour or so. I have a && and ANY() as the filters in the query. I observed the query was taking too long (...
-1
votes
1answer
91 views

Postgresql Regex - escape possessive quantifiers [closed]

I have a query that is formed in Java SELECT * from COMPONENTS WHERE name ~* = 'searchTerm'; This query hits PostgreSQL via JDBC and returns the results. I have to use ~* because the user from the UI ...
3
votes
1answer
103 views

archive_command: Can hardlink (ln) be used instead of cp?

We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool pgrsync. The archive_command was initially set to cp %...
1
vote
1answer
169 views

Issues with enabling pg_stat_statements

I am having issues quering pg_stat_statements and puzzled what else I missed? See below what I have done. I still have issues when I query pg_stat_statements! I got error saying the libraries is not ...
1
vote
2answers
146 views

In Postgres, what should be the autovacuum strategy for big tables (hundreds of millions rows) that get added/removed thousands of rows every day?

I have quite a big table with a lot of daily traffic for reads, inserts and deletions. Currently, it has 392 million of live tuples and 27 million of dead ones. Vacuum settings (...
0
votes
1answer
42 views

Postgresql - Distinct array aggregation

This is my current query SELECT project_id, release_id, array_agg(DISTINCT version_bom_id) AS version_bom_ids, sum(case when review_status='REVIEWED' then 1 else 0 end) AS reviewed_count, sum(case ...
0
votes
0answers
138 views

Data mismatch in View2 [SELECT * from View1] with View1, how to fix?

The problem I encounter can be oversimplied as CREATE View#2 AS SELECT * FROM View#1; SELECT * FROM View#2; SELECT * FROM View#1; Then View#1 and View#2 return different results. This challenges ...
0
votes
1answer
18 views

How to pick a chunk from the text column in postgresql

I have a table ABC with the following 2 columns id(PK) Name 1 Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) 56 tablet 4 x 14 tablets 2 Zocor 10mg tablets (You &...
0
votes
1answer
56 views

PostgreSQL 9.6.17 COALESCE function error

I'm trying a SQL query with the function COALESCE in PostgreSQL 9.6.17, but it produces an error: invalid syntax for type double precision: "" Example query: SELECT COALESCE(date_part('...
0
votes
0answers
22 views

PostgreSql - Join two tables to extract a combination of three columns

I have a problem where I need to join two identical tables to extract a column called status. Table 1 id | status ---------------------------------------- 1 | APPROVED 2 | WAITING 3 ...
0
votes
2answers
276 views

I need to do periodic commits in my purge function

I wrote a purge function that runs in Postgres 9.6. The function goes through all of the tables in our application starting from the bottom most child table and ending with the top most parent table ...
1
vote
1answer
21 views

How do I Order By on multiple rows in Postgresql

I have a table with columns with severities like: name | critical | major | trivial ---------------------------------------- Apache |2 |3 |2 Eclipse|5 |0 |4 TestNg |0 ...
3
votes
1answer
286 views

Postgresql: Refreshing materialized view fails with “No space left on device” and a huge spike in disk usage

I have a materialized view that refreshes every five minutes. The SQL aggregates the data among many tables with over 800k rows in each. However, the when using the "REFRESH MATERIALIZED VIEW ...
1
vote
0answers
122 views

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback. I'd like to ...
1
vote
0answers
21 views

Move Our Google Cloud SQL Instance to Compute Engine

We are running several Cloud SQL Instances on GCP and plan to move all of them to some Compute Engine instances, depending on the applications that use it. Our main concern is cost of the server. We ...
8
votes
1answer
345 views

How to get non-overlapping distinct intervals from a PostgreSQL table?

Using postgresql 9.6. The table has user sessions and I need distinct non overlapping sessions printed. CREATE TABLE SESSIONS( id serial NOT NULL PRIMARY KEY, ctn INT NOT NULL,...
0
votes
0answers
25 views

Smaller client results in a very slow query

I am testing a query on PostgreSQL 9.6 on a very small tenant and a big one (within the same database, schema, tables). The query is super slow on a small client, which is quite surprising as I would ...
2
votes
1answer
25 views

Postgresql 9.6, create a user with only select permissions for a given database/schema

I want to create a user that has: has its default privileges revoked when I create it can only perform selects against a given database/schema Is this possible?
3
votes
1answer
304 views

Trigger with IS DISTINCT FROM on rows with json/jsonb without comparison itemizing?

Is it not possible to do a full row comparison in an INSERT/UPDATE trigger when the table has json or jsonb columns? The error I'm getting makes me think I'd need to individually compare each column ...
0
votes
1answer
54 views

Change datatype from DATE to VARCHAR()

CREATE OR REPLACE procedure create_table_dateWise27() LANGUAGE plpgsql as $$ declare d_date date; begin select to_char(current_timestamp :: timestamp - interval '1 hour','yyyy-mm- dd')into ...
1
vote
3answers
50 views

I want to get all the weeks of a month with the start and end dates of those weeks without creating a table

My SQL is: SELECT date_trunc('week', '2020-06-01'::timestamp)::date ||'-'|| (date_trunc('week', '2020-06-01'::timestamp)+ '6 days'::interval)::date; However, using this code it shows only one week'...
1
vote
0answers
180 views

Postgres Unnest or flatten a nested array inside of JSONB

I've got a table MY_TABLE that has the following columns: User Name Data Purchased Data is of the form : [{ "Id": 1, "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
0
votes
1answer
17 views

How can we sort the words inside a string in PostgreSQL?

In my postgresql table one column data is like below 250 TABLETS + PVC + PVDC + ALU + HOSPITAL PACK + Film-coated tablet - BLISTER - [/Tablet] - QUETIAPINE FUMARATE 28.78 mg is present 20 ML + ...
1
vote
2answers
93 views

Explaining recurring crash in postgresql deployment

I'm having trouble with unexpected system crashes emanating from a postgresql deployment on a moderately busy production server (Django 1.8 app deployed on DO droplet with a gunicorn application ...
0
votes
0answers
30 views

How to find the column value with the biggest storage size in the DB?

Is there any way to find out which column value has the largest storage out of all tables in PostgreSQL DB? I'm looking for currently used storage, information_schema.column only shows the length of ...
0
votes
0answers
17 views

Query planner not using Index scan for WHERE IN condition [duplicate]

I have a table INVENTORY in PGv9.6.11 and when a run query with WHERE _column_ IN(), query planner using seq scan even have index on that _column_ CREATE TABLE INVENTORY ( process_id bigint, ...
0
votes
0answers
23 views

Trying to write recursive query to get list of tables by foreign key

I am using this query to purge data from the bottom of the tree up. This is just the first step in my process. Does anyone have any suggestions on how to handle the recursion? Thanks DROP TABLE IF ...
7
votes
2answers
2k views

SELECT DISTINCT ON, ordered by another column

Please consider the following table test: CREATE TABLE test(col1 int, col2 varchar, col3 date); INSERT INTO test VALUES (1,'abc','2015-09-10') , (1,'abc','2015-09-11') , (2,'xyz','2015-09-12') , (2,...

1
2 3 4 5
10