Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
14 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
13 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
38 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
22 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
14 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
20 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
348 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
13 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
46 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
22 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
15 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
5 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 =, ...
2
votes
1answer
53 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
27 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
42 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
16 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
42 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
102 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
23 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
20 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
8 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
30 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
28 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
27 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
35 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
17 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
43 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
51 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
112 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 ...
0
votes
0answers
20 views

Manual Failover in PostgreSQL without changing database connection string in services

our ADO.net application uses npgsql to connect to postgresql database. We have implemented a normal cluster of 2 servers(master and slave) as of now. As we don't want to change the database ...
0
votes
0answers
19 views

Postgres partition search in reverse order of partitions

Our production system is on postgres 9, with a partition table. In one of the select queries, we cannot pass the partition key for the select, but we want the data from the latest partition, is it ...
1
vote
1answer
113 views

Postgres 11: Not using the correct indices after upgrade

We upgraded lately our database from PostgreSQL 9.6 to 11 on RDS (after the upgrade we did vacuum full and analyze for the DB). We noticed that a frequent query changed its execution plan, and now it ...
0
votes
0answers
36 views

Is It Possible Backing Up Postgres Backups To Windows Backup Server With PgBackRest

I want to take my postgres backups to our backup backup server which works on windows server, with pgbackrest. My postgres databases are on CentOS/7 OS. I can take those backups to my local but i want ...
2
votes
1answer
67 views

Postgres UPDATE with data from another table - index only scan used for correlated subquery but not join

Context I'm tuning a bulk UPDATE which selects from another (large) table. My intention is to provide a covering index to support an index only scan of the source table. I realise the source table ...
0
votes
0answers
32 views

Multiple sub query in insert statement for postgreSQL

I am trying to insert into table with multiple queries. And its not working, here is my work. insert into post_topics_ta (id,post_id,topic_id,confidence_score) SELECT * FROM post_topics WHERE ...
0
votes
1answer
35 views

How to perform schema migration from array to many-to-many to lookup table?

I currently am storing values in a column of type array like follows (this is just an example): CREATE TABLE blog_posts( id INTEGER PRIMARY KEY, blog_title TEXT, tags TEXT[] ); With the ...
2
votes
1answer
29 views

Simple select query hangs

I have a simple query: SELECT a.name AS a_name, b.description AS b_description FROM a JOIN b ON a.id = b.a_id WHERE b.table_name IS NULL; (Names changed to protect the innocent) ...
1
vote
1answer
26 views

Remove the deeper jsonb element in Postgres

[ { "type":"test.added", "values":[ { "field":"field1", "value":"fieldValue1" }, { "field":"field2", "...
0
votes
2answers
69 views

Is it Possible To Take Full And Differential Backup in Postgresql Like Sql Server?

I have a postgresql server. I want to take its backup full and differential mode as it is like in sql server. Is it possible to do this in postgresql? How can i do it, if it is?
0
votes
2answers
114 views

Missing data for one schema in pg_dump

I have a Postgres 9.6 database with several schemas, and a regular backup of this database is done with a command like pg_dump -h localhost -d db -U postgres -Fc -f file.dump When I restored a ...
0
votes
0answers
14 views

postgres 9.6 - use data from xml to call procedure

I work with a system based on Postgres 9.6 (updated from 9.1 or something similar). One of latest changes requires an ability to remotely call procedure on database with some arguments. Due to various ...
0
votes
1answer
114 views

PostgreSQL 9.6 pg_rewind - takes a long time to replicate

I use Streaming Replication,and Replication works normally. Two servers are HA and PostgreSQL DB is replicated. The data in the DB is about 40G. When a failover occurs, the slave is successfully ...
1
vote
1answer
23 views

How to block pg_stat_reset() statement in DB

Team, Recently we have found one of our application is triggering pg_stat_reset() executing everyday which is causing performance issue . to disable this statement thru application level will take ...
1
vote
1answer
257 views

Multiple concurrent “REFRESH MATERIALIZED VIEW”: how to manage?

I have a large Postgres database with many tables, some with tens of millions of rows. Several working processes update the database concurrently. For faster searches, relevant data is compiled into a ...
0
votes
1answer
20 views

Is it possible to make simple postgres dump using replication server?

PostgreSQL 9.6, configured backup server via barman tool. Using WAL streaming (pg_receivexlog) and streaming backup (pg_basebackup). All working good. However for development/debugging purposes I may ...

1
2 3 4 5
9