Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
38 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
12 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
32 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
19 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
28 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
16 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
40 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
33 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 ...
2
votes
0answers
14 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 ...
9
votes
1answer
81 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
13 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
46 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
23 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
40 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
12 views

Is there any way to check WHERE clause stats on table

Not sure whether this is a valid ask or not, I'm trying to get the stats of the columns which are using in user query/ETL functions for the where clause. Can we check on a table, which column has ...
0
votes
0answers
33 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
15 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
44 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
25 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
517 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
17 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
49 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
16 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
25 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
24 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
24 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
266 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) ...
0
votes
1answer
88 views

Stored Procedure for Vacuum in PostgreSQL

I want to create a stored procedure to trigger vacuum, I know there are many ways to automate vacuum, but I would like to use the stored procedure (Postgresql 9.6). The problem Im facing is, I can't ...
1
vote
1answer
60 views

Deleting duplicate subscribers using max uuid postgresql

I am trying to eliminate duplicate subscribers from the subscribers table for a api_customer_id, this is the query that I use to get those duplicate subscribers : select subscribers.email, count(*) ...
1
vote
0answers
20 views

Postgres Major version upgrade 9.4 -> 9.6. PITR compatibility across versions?

Just dug into postgres documentation and couldn't get a clear answer on this. With Debian Jessie hitting EOL for Debian LTS in June, we are upgrading our database servers along with our other servers ...
2
votes
2answers
252 views

Understanding where pg_toast is coming from

I've got a fair amount of storage in pg_toast relation | size ----------------------------------+--------- pg_toast.pg_toast_43934449 | 87 GB pg_toast....
0
votes
1answer
532 views

operator does not exist: timestamp with time zone + integer in PostgreSql

I came across with a strange issue in postgres. I have two databases create in two different periods. Both are running in the same PostrgreSql 9.6 version in the same machine. When I run the query ...
0
votes
1answer
65 views

How do I locate my Postgres data directory while the server is down?

I'm on Mac 10.13.6. I restarted my machine and Postgres, which normally starts upon restart, wasn't running. Sadly, I cannot remember how or where I installed it. I'm able to locate instances of "...
2
votes
0answers
21 views

How to fetch related rows on multiple condition with ranking

I have following table for storing item information in PostgreSQL. id | name | category | seller | m_age | status | type_id | region | ----------------------------------------------------------...
0
votes
0answers
9 views

High cost on my query on partitioned table

I have a partitioned table as follows: create table daily_summary ( id uuid default uuid_generate_v4() not null constraint daily_summary_partition_pkey ...
2
votes
1answer
45 views

Is there a memory overhead using partitions in postgres vs. a single table?

Background I am ingesting time series data totalling to about 10M rows per day, where each row has a timestamp, a player id and some other columns. That data is then queried via an API for a ...
1
vote
0answers
44 views

Difficulty Optimizing Postgres Write Throughput

I'm trying to index a collection of roughly 127K files using a PostgreSQL 9.6 server on RDS. I had expected the process of writing these documents to the DB to take about 8 hours, but over time I ...
2
votes
1answer
43 views

Index for a complex similarity query Postgres

I have a materialized view that has a few columns that I need to run a complex OR query on with similarity matching and the structure is like this mymatview: column a, column b, column c, column d, ...
0
votes
1answer
21 views

setup table level Autovacuuming on postgres database

I am trying to setup table level Autovacuuming on postgres database. What all metrices I need to collect and analyze specific to that particular table? I have to enable autovacuuming on all the ...
0
votes
0answers
47 views

Postgres - Revoke access to public schema for an user

As a DBA, I have created an user 'user1' to access 'abcd' schema as per the request. But, the user I have created is also getting read access to the schema 'public' in the database. An application ...
0
votes
0answers
23 views

Postgres replica out of sync

we have a postgres 9.6 replica configured among 2 servers. We used the following configuration to create the replica: postgresql.conf wal_level = hot_standby max_wal_senders =...
0
votes
2answers
47 views

Does Postgres ever require a naked field without single quotes?

I have this query: UPDATE user_map_table SET user_id = '1', value = '{"bob":"diane"}', key = 'couple', value_type = 'string', added = 'false' WHERE user_id = 1 it worked fine. The thing is the ...
2
votes
0answers
55 views

CREATE TABLE IF NOT EXISTS fails with privilege exception when table exists

I discovered that CREATE TABLE IF NOT EXISTS will fail with insufficient_privilege if the role executing the statement does, in fact, not have the necessary privileges, but does so regardless of ...
0
votes
1answer
205 views

Setup VIP for PostgreSQL Replication

I have been assigned a task to setup Streaming replication in Postgresql. I am not a DBA by profession, doing this thing for the first time. I have set up replication, now as we don't want to change ...
0
votes
0answers
41 views

hierarchical query for all tables in a given schema

I am trying to write a hierarchical query to show me all of the tables in a given schema from parents to children to grand children, etc. I split the code into a view and a query to hopefully make it ...

1
2 3 4 5
10