Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
1answer
23 views

how to update PostgreSQL array of jsonb

I have the structure like this (json pretty): [{ "uid": "comment_1", "message": "level 1 - comment 1", "comments": [{ "uid": "subcomment_1", "message": ...
-2
votes
1answer
30 views

Best possible design for a database about users, companies and departments [closed]

I have an app with Postgres and two tables: users and companies. A user belongs to a company so a company_id column is present on the users table. Now a company can have multiple departments so the ...
0
votes
1answer
26 views

postgresql recovery.conf file location in 11.5

I'm new to postgres. We need to take incremental backups for postgres databases as well as we must be able to perform PITR. I searched a lot on internet and tried to find out any article referencing ...
1
vote
1answer
48 views

Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck

We are using postgres 11.4 and nodejs to run sql scripts. We use node-postgres module and its Pool feature, setting it to 10. Database is running on a server with 4GB total memory. When the script ...
0
votes
1answer
21 views

Understanding trigger behavior

I'm having trouble with a trigger which I can't get my head around. I have a table called TABLE_A with an AFTER INSERT trigger that executes a procedure. The procedure basically does the following ...
0
votes
0answers
18 views

Postgres using powershell scripts - where do trigger messages go?

I have a windows (10 pro) powershell script that reads messages from an external server and INSERTS them into a postges (v10.5) database table. The table has an AFTER INSERT trigger defined on on it ...
0
votes
1answer
16 views

Jailer exclude tables to be exported

As seen in this answer I can use jailer in order to export a subset of data into an SQL script. But in my case I also want to exclude some tables completely from being extracted at all especially on ...
0
votes
1answer
113 views

Getting ERROR: could not open relation with OID 6701547 while performing VACUUM FULL on Postgres 9.2

We are facing issue in Postgres database 9.2 on RedHat EL 6.8 with an ext4 file system. We are getting below error every time whenever we preform vacuum full or reindex database db_name operation. ...
0
votes
1answer
9 views

Reporting table size to grafana

I am trying to report partition metric to influxdb so that it can be visualised in grafana. I have partitioned my table using pg_partman. I want to report the size of the default table that is created ...
2
votes
3answers
37 views

How do I select by a JSONB array containing at least one element within a range?

I need to model products with properties. Properties need to be dynamic, in the sense that application users can add / delete properties to products. For this reason, I am using a JSONB column ...
0
votes
1answer
21 views

grouping rows of common values to create a new 'group id' for each set

I am trying to achieve the below but was not able to so far, any help would be greatly appreciated. I have this data (sorted from a query by id, anchor, date, and time) that I wish to group by common ...
1
vote
1answer
20 views

two-table-over <+> functional relationship: maintaining referential integrity

I have three tables, the last two having a many to one relationship with the first, written here in SQL: create table study ( id bigint primary key, name text ); create table treatment ( ...
0
votes
1answer
27 views

Postgres, `where` condition, sort by first column, but I don't want that

I am using PostgreSQL 11.5 on Ubuntu Issue I have sql with joining tables and with where condition, which looks like this. ... where s_product.product_shop_id in ( '237002', '230041', '467173', '...
-1
votes
1answer
51 views

Postgres vs SQL Server [closed]

Can anyone share proper advantages-disadvantages? If possible, I am trying to search for really great benchmarks between both, so please share it with me. Also, which will be best between them for ...
0
votes
1answer
13 views

Add a suffix to duplicates on PostgreSQL

Not sure about the term "undouble", I am just trying to add a unique index on a column but it contains doubloons, so I need to update data first. Figure this data: BEFORE update 1;foo 2;foo 3;foo 4;...
1
vote
2answers
61 views

Replicate a database with a subset of data offered by custom queries [closed]

As seen in this question I can use the following sql command: CREATE DATABASE newdb WITH TEMPLATE olddb; In order to replicate a database into my machine. But in my case my new database only to ...
0
votes
1answer
19 views

Aurora postgres grant permission to pg_attribute

In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. ...
0
votes
0answers
22 views

Repmgr service crashes during automatic failover, slave's upstream node points to failed master - PostgreSQL

We are using Centos7 with PostgreSQL 11.5 and Repmgr 4.4 in our system. We have setup an automatic failover system using Repmgr with a 3 node cluster (1 master - Server A, 2 standbys - Server C and F) ...
0
votes
1answer
25 views

postgresql explain analyze from sql file? [closed]

I had wrote a long query for postgresql, and I want to use postgresql explain ANALYZE to find out runnig time / parallel workers information about that query. I can run that sql command by using ...
0
votes
1answer
37 views

SQL Join to put rows into columns

I have two Tables TABLE 1 ID Name 1001 XYZ 1002 ABC TABLE 2 ID GAME 1001 A 1001 B 1002 A I want the outcome of two tables join as ID NAME GAME1 ...
0
votes
1answer
14 views

Group by unique items in an array column and count records that contain each item

I have a table that looks like this: records ------------------------------------- id items[] ------------------------------------- 1 {toaster,piano} 2 {car} 3 {car, toaster} 4 {tv}...
0
votes
1answer
14 views

Return distinct rows based on matching data in a column in another table in PosgreSQL

I have a table (e_v2) with some time-series data. I want to fetch all rows where the data in the "POINT_KEY" column appears in the "KEY" column in another table (etraining_v1). The code below (I'm ...
1
vote
1answer
23 views

Alter table add column with no default blocks

tl;dr The following query blocks the postgres server when executed ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL engine version 10.6 # of rows 1.5 mils Hi, I have a somewhat ...
0
votes
1answer
23 views

postgresql different configuration for different tablespace

I'm configuring new server with both SSD/HDD disk with ubuntu 18.04. I've installed system in SSD and created two different tablespaces one in ssd and one in hdd. I would like for configuration ...
0
votes
1answer
19 views

Cumulative array aggregation in Postgres

Does anybody know if it is possible perform cumulative array aggregation in Postgres? For example, to transform this: create table records ( data text, path text[] ); insert into records values (...
0
votes
1answer
24 views

Generate unique random string to be used as value for a field inside newly created rows in PostgreSQL

I have this statement which generate a random string: SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '') FROM ...
4
votes
1answer
42 views

Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL?

If I have a table A, like so: A { id SERIAL title TEXT ... parentId INT references A.id via a foreign key constraint } I am pulling data from a source table - A_SOURCE - where there isn't a ...
0
votes
0answers
22 views

Is it a good idea to have a read connection point to both write database and read replica?

In my laravel app I have a write connection to a write database, and a read connection to a dns server that is connected to a single read replica (as instructed here) In the past the dns server was ...
5
votes
1answer
47 views

Bad execution plan when having partial index and big In-clause in Postgres

Postgres seems to always use sequential scan where it could have used a partial index to get index scan only. It only happens when an in-clause exceeds more than 100 elements. Given the following ...
0
votes
2answers
32 views

psql: could not connect to server: [closed]

I have installed PostgreSQL in Ubuntu, it installed successfully but psql command not run. It shows that psql: could not connect to server: No such file or directory Is the server running ...
0
votes
1answer
22 views

Long running READ queries stuck in “idle in transaction”

I have a JDBC connection pool in a web API that makes frequent requests to the RDS Instance. Each request is for a long running SQL query where PG decides not to use indices because the amount of data ...
0
votes
0answers
33 views

How can I only load one function from an extension?

In PostgreSQL, I am loading the uuid-ossp extension so I can use the function that creates random UUIDs in my queries: create extension if not exists "uuid-ossp"; However, as well as making ...
0
votes
2answers
65 views

Exporting table data into csv format from server to local [closed]

I have connected to remote server and trying to export a table data to local using the command: copy finance.coa to 'localusername@172.16.1.44:/home/kedarisettyakhiles/akki.csv' DELIMITER ',' CSV ...
1
vote
1answer
35 views

Connection to PostgreSQL disconnects every hour regardless of activity

I'm monitoring my PostgreSQL clients using a periodic netstat -an | grep 5432 | grep EST to check the ESTABLISHED status of the connection every 5 minutes. I have noticed that all of the connections ...
0
votes
0answers
23 views

DB design - logging transactions

I am trying to implement a booking system for charging stations that can either extend or refund a booking. I basically have the following tables: booking booking_logs payment refunds With how the ...
1
vote
1answer
17 views

Re-adding constraint after ALTER fails on row that exists

I'm getting a failure trying to add a constraint that previously existed on a table after having dropped it to do an alter: mcqueen=# ALTER TABLE ONLY public.mcqueen_base_imagemeta2 mcqueen-# ADD ...
1
vote
2answers
20 views

Improving the queries in a view to query custom types

I'll spare you the details, but I've got a situation where I'm going to be creating and managing a lot of custom types. (Postgres 11.4, ultimate deployment under RDS.) It looks like there isn't a ...
0
votes
1answer
19 views

Disable log ERROR: duplicate key value violates unique constraint

Running Postgresql in a better ask for forgiveness than permission mode during the inserts. Works great, only problem: The logs are flooded with the following message duplicate key value violates ...
0
votes
2answers
51 views

Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields? [duplicate]

I have to design a database schema for new project. I will use PostgreSQL for the implementation of the schema. Right now I'm stuck in a scenario where I need to handle foreign keys based on type and ...
4
votes
1answer
456 views

Disk usage of integer column vs boolean column in Postgres

I saw this question Bit vs. Boolean columns. I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 ...
0
votes
0answers
15 views

How to use PIVOT keyword in Postgres [duplicate]

I have one table in Postgres which has following columns: processtransactionid bigserial, TenantID uuid, ProcessInstanceId bigint attributename varchar(250), attributevalue varchar(250), ...
0
votes
0answers
25 views

PostgreSQL + pg_xlog folder with very old files and how to purge pg_xlog files

Under folder - /var/lib/pgsql/DB/pg_xlog We have very old files as the following: -rw------- 1 postgres postgres 56 May 31 2018 00000002.history -rw------- 1 postgres postgres 331 Oct 14 ...
0
votes
1answer
53 views

Efficiently finding the ultimate parent in multi-level one-to-many tables

Let's say I have a database with multi-level one-to-many tables (a contrived example): CREATE TABLE users ( id SERIAL PRIMARY KEY ); CREATE TABLE properties ( id SERIAL PRIMARY KEY, user_id ...
0
votes
0answers
15 views

DDL Logs In Postgresql.Conf File

I want to log only ddl commands to my postgresql .wal files. So, i configured my postgresql.conf file to log those sql queries but it did not work. It cathes every query that works on my postgresql ...
0
votes
1answer
26 views

How to troubleshoot hung queries in PostgreSQL RDS?

I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few ...
0
votes
1answer
29 views

Why Postgres replication write_lag is measured in seconds

I'm running pg_receivewal locally, without compression, it stores segments on the same drive with the db files. Here's the replication statistic: imbolc=# select * from pg_stat_replication; -[ RECORD ...
2
votes
1answer
39 views

PostgreSQL Serialisation failure on different ids

Serializable isolation mode can be used to avoid race conditions when upserting equal ids. So for create table u(uid int primary key, name text); if we run two similar transactions T1 and T2: begin ...
1
vote
1answer
52 views

Counting duplicate instances within column in SQL when grouping by another column

My DB looks something like the following: id | Name | JOBID ----+--------+------ 1 | Sam | a 2 | Sam | b 3 | Ali | b 4 | Tim | c 5 | Tim | d 6 | Fin | a 7 | Mona | ...
1
vote
1answer
26 views

By using SQL Window Functions, how can I get the lead of the last row in a partition?

Considering the following simple example: select lead(c) over (partition by a order by a) from (values (1,'1','date1'), (1,'2','date1'), (2,null,'date2') ) t(a,b,c) The first two ...
0
votes
0answers
17 views

Frequency of table update in postgresql?

Given postgres 11. I'm looking for statistics on how often a particular table gets something inserted/updated/deleted. There's of course this: https://www.postgresql.org/docs/11/monitoring-stats....