Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
1
vote
1answer
16 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
47 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
35 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 ...
-1
votes
0answers
15 views

ERROR: operator does not exist: integer = character varying LINE 47: ) at on at.application_id = ap.application_id [closed]

CREATE OR REPLACE VIEW oms_application_view (application_id, application_module, application_name, environment, launch_date, master_id, master_module, ita_group_info, ita_send_method, service_class, ...
0
votes
0answers
18 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
58 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
votes
0answers
36 views

ERROR: operator does not exist: timestamp without time zone - bigint LINE 35: - ( a.DURATION

CREATE OR REPLACE VIEW v_raw_data_check (carrier, c_dispute_mth, c_seqno, h_seqno, c_start_time, h_start_time, c_end_time, h_end_time, c_duration, h_duration, c_calling_no, h_calling_no, c_called_no, ...
1
vote
0answers
28 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
33 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
150 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
47 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
47 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
31 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
45 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
79 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 %...
0
votes
1answer
81 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
77 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
33 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
99 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
16 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
41 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
109 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
18 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
158 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
46 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
18 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
169 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
19 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?
2
votes
1answer
145 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
34 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
43 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'...
0
votes
0answers
97 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 + ...
0
votes
2answers
71 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
28 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
16 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
22 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
1k 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,...
0
votes
0answers
25 views

Can we partition or split json file while insert

I have a table with a JSON column, large files coming from the source. The issue is here while flattening data from that file is taking a long time some and sometimes DB server crashing and going to ...
1
vote
2answers
133 views

Union probably not right for this [closed]

I have a table witrh 3 columns: id price created_at 1 0 ts 2 5 ts 3 0 ts 4 3 ts I'd like to have a result table that contains 2 rows, like this: id ...
0
votes
0answers
65 views

Memory exhausting when accessing large files of JSONB columns

I have a table with one of the column data jsonb, it has proper index on the column for filter condition but some times query is running too long and some times DB crashing because of out of memory ...
1
vote
1answer
19 views

Can I tell Postgres that a new constraint is valid without actually running the check?

I have a constraint that ensures one of three columns is populated, and I'm adding a new column, so I'm replacing the old constraint with a four-column version. Since I know from the old constraint ...
0
votes
1answer
31 views

Need to identify non contigious rows

My table structure is as follows: create table daily_summary ( id uuid default uuid_generate_v4() not null primary key, machine bigint ...
0
votes
0answers
67 views

Specific User activity logging for PostgreSQL

Scenario: I want to enable user activity events for PostgreSQL(9.6). Enabling "log_statement = 'all'" will achieve this goal.However, I cannot enable this option as it is degrading server performance(...
0
votes
0answers
7 views

Count returning customers with several conditions

I have PostgreSQL 9.6.10 and a table like this one: CREATE TABLE web.orders (id integer NOT NULL, user_id integer NOT NULL, status_id integer, created_at integer); INSERT INTO web.orders(id, ...
0
votes
1answer
26 views

Constraint on existing table fails

I want to add the below constraint to an existing table: ALTER TABLE ONLY daily_summary_202004 ADD CONSTRAINT odo_no_overlapping_202004 EXCLUDE USING gist (((vehicle_gid)::text) WITH =, ...
3
votes
1answer
642 views

Create index for WHERE COALESCE() condition

I'm using PostgreSQL V9.6.11 Table DDL: CREATE TABLE test_c ( insrt_prcs_id bigint NOT NULL, updt_prcs_id bigint, src_sys_id integer NOT NULL, load_dttm timestamp(6) ...

1
2 3 4 5
10