Questions tagged [postgresql-9.6]

PostgreSQL version 9.6

Filter by
Sorted by
Tagged with
0
votes
0answers
17 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 ...
0
votes
1answer
83 views

Counting and grouping over multiple OUTER JOINs

I'm creating a view of advertisement logs (impressions, clicks, and clicks per impression). I have a simple table structure and some working queries but I'm having some trouble composing these into a ...
1
vote
1answer
7 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?
0
votes
1answer
715 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
100 views

Performance issues with CAST function

Observing this performance issue that can't be explained with EXPLAIN in a PostgreSQL 9.6 database. There is currently a table public.users, which has a cast on it to output a record to JSONb. (e.g. ...
0
votes
0answers
12 views

PostgreSQL Trigger 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
2answers
182 views

sum only different id's values with any aggreagte function in postgresql

I need your help. Let's assume that we have a table like below +---------------+-------+--------+----------+ | barcode | shop | id | entrance | +---------------+-------+--------+----------+...
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
37 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
21 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
186 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
10k 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
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
2answers
41 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
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
21 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
383 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
48 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
449 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
24 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
18 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, ...
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
115 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
37 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
45 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
135 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
106 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
19 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
69 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
39 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
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
2answers
197 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
41 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
31 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
32 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
409 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
2k 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 ...

1
2 3 4 5
10