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
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 ...
5
votes
1answer
9k views

Drop table taking too long

I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time. It's been running for 2 days now. I suspect the rollback function to be the ...
0
votes
0answers
23 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
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 ...
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
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
350 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
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 ...
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 ...
0
votes
1answer
430 views

Postgresql Replica is in sync but repmgr says node unattached

I've set up a replica of a Postgresql instance using repmgr, and the replica is totally in sync with the master instance and have been in sync since some days, but repmgr tells me replica isn't ...
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, ...
3
votes
1answer
6k views

Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone. F.e. the date 2018-08-...
1
vote
3answers
1k views

Cannot revoke permissions or drop user in PgSQL AWS RDS

I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and ...
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 =, ...
1
vote
1answer
2k views

PostgreSQL database size doesn't match used space on disk

I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which ...
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(*) ...
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
1answer
104 views

pglogical library

I would like to try pglogical to set up an archive server from a live database, filtering with INSERT, UPDATE statements (DELETE statements will not be replicated in the archive server). I follow the ...
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
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 "...
0
votes
1answer
1k views

Why is PostgreSQL's auto-explain not working in AWS RDS?

These are my settings: According to the documentation, as far as I can tell, this combination of settings along with a statement_timeout value of 3600000 should mean that hour-long queries time out, ...
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
2answers
185 views

Postgres list constraints that use a stored procedure

I have a postgres database (v9.6) and I have multiple CHECK constraints that use user-defined functions. something like: CREATE TABLE payment ( amount bigint, CONSTRAINT amount_gt_0 CHECK (...
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 ...
5
votes
4answers
7k views

Postgres 9.6 reload or restart after config changes

I need to change a couple of log config settings in postgresql.conf. How do I know which parameters need a restart or reload? Is there a list of parameters that need a restart?
1
vote
1answer
99 views

Slow PostgreSQL 9.6.5 query with LIMIT, but only for tsquery with no results

Table a has > 3 million rows and this index: CREATE INDEX idx_a_txt ON a USING gin (txt); This query takes 5 seconds if I search for something that doesn't exist, and 31 milliseconds if 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
374 views

PostgreSQL query suddenly very slow after new column addition

I have a query that's performing terribly after adding a new column. Here's the slow version: SELECT COUNT(*) FROM activities INNER JOIN "users" ON "users"."id" = "activities"."user_id" AND "users"."...
3
votes
1answer
1k views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
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
1answer
138 views

pgAdmin4 version 3.1 how to update to the last version

We use the pgAdmin4 version 3.1 on Red Hat connecting to a server PostgresQL 9.6. In the log of the server, I notice some errors generated by the pga4dash : ERROR: column "waiting" does not exist ...
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 ...
0
votes
1answer
626 views

Dump roles on AWS without pg_dumpall -g access (postgresql 9.6.8)?

We have a system on AWS RDS Aurora, PostgreSQL v 9.6.8 option. We are trying to move its roles to a new encrypted 10.5 instance, but we don't have superuser access that's required to do pg_dumpall -g ...
0
votes
1answer
3k views

Join JSONB column to normal PK column in Postgres

I am trying to join a normal table to a table with a jsonb field via a property on that jsonb field in Postgres. Example: Normal table: create table Res ( id uuid default uuid_generate_v4() ...
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
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 ...

1
2 3 4 5
9