Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.

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

How to correctly deal with IDENTITY fields on parent/child tables when using inheritance in PostgreSQL

I have a rather simple question when playing with a PG 15.1 database. I've tried to set up a simple inheritance case: DROP TABLE IF EXISTS cities CASCADE; CREATE TABLE IF NOT EXISTS cities ( id INT ...
0 votes
1 answer
802 views

PostgreSQL - Share data on Network drive [closed]

I currently use a simple Microsoft Access database, stored on the company Network Drive, for my department's data needs. This works quite well as everybody in my team has access to the shared folder ...
3 votes
1 answer
1k views

How can I list all privileges that a PostgreSQL role has?

... including the inherited ones? When organizing access rights through different roles, this seems to be something really useful, yet I haven't found anything that would show how to do this. AIUI, I ...
18 votes
3 answers
27k views

Collation version mismatch

I replicated a database, it's working fine but when I enter the replication database it displays the following message: The database was created using collation version 2.31, but the operating system ...
0 votes
1 answer
42 views

How to ensure data consistency in a distributed system

I'm developing a distributed system with microservices with only one write-only database and the rest read-only in logical replication My challenge is to have consistency in the data, especially in ...
1 vote
1 answer
586 views

Unable create partition by trigger

1) I have table CREATE TABLE tor ( events_date timestamp without time zone NOT NULL, message text CONSTRAINT tor_pkey PRIMARY KEY (events_date, message) )PARTITION BY RANGE (events_date); 2) ...
0 votes
2 answers
380 views

How to get pg_locks related to a specific database in Postgres?

This question is related to my previous one here In SQL Server, I could get the lock type from a specific database using the code below: SELECT request_mode FROM sys.dm_tran_locks WHERE ...
1 vote
1 answer
660 views

postgresql statement taking ages to cancel in pgAdmin III

I was running a query in pgAdmin III and it was taking a long time. After 9 minutes I decided to cancel it using the stop button. The UI stated that it was cancelling and stayed in that state for ...
6 votes
3 answers
2k views

Is there a performance penalty for unique key constraint violations in Postgres?

In my API, the user can potentially send a request which tries to create a new row when a row with that unique key exists. Currently, I'm catching the unique key error and returning a message to say ...
1 vote
1 answer
20 views

Create Constraint To Ensure Date Range Is Within Another Date Range

I am trying to create a scheduling application, and part of it requires the date range of a row to be within the date range of a row in another table, which it is referencing via a foreign key. Note: ...
0 votes
0 answers
26 views

PostgreSQL 16 Role Attributes Confusion

I'm working with Azure flexible PostgreSQL. I am trying to build out a v16 database and have run into some issues with permissions. I'm an Oracle guy historically, and I'm struggling to understand ...
1 vote
1 answer
3k views

How to insert multiple values and handle conflict at the same time

I have a table where the primary key is a combination of two columns. When inserting multiple values at a time (batching) conflicts might happen since in the list of values passed there might be ...
0 votes
0 answers
15 views

How to ingest data from HANA to Postgres?

This is a very basic question, but I am new to Postgres and want to ingest some data from hana views. Something like: 1. Run queries/stored procedure to extract/join data stored in HANA 2. Bring into ...
1 vote
1 answer
27 views

will null-ing JSONB column in postgres table before performing ALTER TABLE DROP of that column help?

I changed table structure and I am about to remove old JSONB column from about 2.5M rows big table. The JSONB makes this table really big - pg_total_relation_size: 408GB Performing ALTER TABLE ... ...
1 vote
1 answer
30 views

Postgres insert into `select distinct` view if not exists

I have an events table which is a denormalized table containing item data. There is a one-to-many relationship between items and events. CREATE TABLE events ( id UUID PRIMARY KEY, item VARCHAR(...
5 votes
1 answer
2k views

How to encrypt a column using aes-256-gcm encryption in PostgreSQL?

As a database point person, I am required to encrypt SSN numbers wherever they are in a table. We use PostgreSQL. My application counterparts require that we use the AES-256-GCM encryption algorithm. ...
2 votes
0 answers
12 views

PostgreSQL: Using Statement Level Triggers on Views

I am building an import/export interface on Postgres that requires semantic datamodel transformations. To facilitate the export, I created some views which provide me with a 1:1 match to my export ...
0 votes
1 answer
3k views

How do I stop WAL streaming in BARMAN cron?

I am having an issue "resetting" or fixing a backup server I have in BARMAN: -bash-4.2$ barman check *server-name* Server *server-name*: PostgreSQL: OK superuser or standard ...
0 votes
1 answer
365 views

Trigger to enforce unique constraint on text column (Geometry) to resolve INSERT conflicts

I have a simple table with a geometry column like so: CREATE TABLE geomtable ( gid serial, geom geometry, trip_count integer ); I want a unique constraint on the geometry column, such that ...
59 votes
7 answers
170k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
15 votes
4 answers
6k views

What is the right data type to store only -1 0 and 1 in postgres column?

I want to store only 3 states in a column (if it was 2, I would use a boolean) I considered using an ENUM('up', 'down', 'none') but it seems ENUMs take some significant space. Is there a better way to ...
0 votes
0 answers
24 views

What is the correct configuration for an RDS instance to allow for upgrades when the databases are very large?

I've been using Amazon RDS instances for Postgres databses for a while, but I haven't had to do a lot of upgrading on large databases until recently. I hit an issue where one database was hundreds of ...
-1 votes
0 answers
13 views

Cannot register new server on pgadmin

When I try to register new server on pgadmin, it happens this error. How can I resolve that (I'm using Mac M1, server is running too)
0 votes
1 answer
432 views

How to reset object permissions to default?

I've just been added to a project using Postgresql 11.4 where database security has been an afterthought. As part of tightening things up, I've used ALTER DEFAULT PERMISSIONS to set sane ACLs on any ...
1 vote
0 answers
25 views

what is the best way to create database index on json cloumn in postgress

I want to create a database index on json column on field customer_number which has the follwoing data.. { "version": "3.0", "order_header": { "...
1 vote
1 answer
327 views

Error during plperl compilation: quoted execution trapped by operation mask

When I try to create a perl function, I get an error. CREATE OR REPLACE FUNCTION foo() RETURNS text AS $BODY$ use v5.28; use warnings; my $output = `/data/myapp`; # or qx('/data/myapp') return ...
0 votes
1 answer
3k views

Why is my postgres `base` directory filling up rapidly?

My pg_xlog directory was filling up because my standby server was down. This brought the volume to 100% usage and shut down postgres. I brought up the standby servers, moved pg_xlog to another volume, ...
0 votes
0 answers
37 views

Database Schema for an Inventory Management System

I am looking for some review on the following schema I've worked on for an inventory management system. It will be used in a govrenmental facility for managing item issuance to employees, to have a ...
1 vote
2 answers
34 views

How to optimally do Postgres `IN (...set...)` query with a large set of values

I have a table with millions of rows, and I want to find all rows that have any one of a supplied list of a few thousand values in a specific column. Bascially I want to run an IN(...set...) query, ...
0 votes
1 answer
82 views

Why doesn't Postgres apply limit on groups when retrieving N results per group?

I've come across many questions/answers for Greatest/Top N per group type problems that explain how to solve the problem - generally some variation of row_number() or CROSS JOIN LATERAL, but I'm ...
1 vote
1 answer
3k views

pg_dump issue while dumping single schema

pg_dump does refuse to dump a given schema in my DB. pg_dump -d <dbname> -s -n dm_geo -f <filename> Result -- -- PostgreSQL database dump -- -- Dumped from database version 9.5.4 -- ...
-2 votes
0 answers
26 views

How does a database rollback incomplete transactions upon crash recovery?

Here's my understanding of what happens in PostgresSQL and MySQL: BEGIN_TXN entry is written in the WAL at the beginning of the transaction and flushed to disk COMMIT_TXN entry is written to WAL at ...
0 votes
0 answers
13 views

postgres 11->15 upgrade issue with pg_ts_dict and pg_ts_parser

I'm trying to upgrade v11 to v15 and running into the following issue public4u8p1dnq05qcq3nwoyudmjfqxxuheiny.pg_ts_dict.dict_init public4u8p1dnq05qcq3nwoyudmjfqxxuheiny.pg_ts_dict.dict_lexize ...
0 votes
1 answer
725 views

PostgreSQL is skipping indexes when using `order by` + `limit`

I have a postgreSQL database with two big tables, connections and sessions select "c"."id" "id", "ch"."title" "channel", "ch".&...
2 votes
1 answer
61 views

Use case of ALTER EXTENSION ADD/DROP in upgrade script of extensions

I am recently looking at the pg_stat_statements upgrade script and found out these set of commands here /* First we have to remove them from the extension */ ALTER EXTENSION pg_stat_statements DROP ...
0 votes
2 answers
60 views

Cheaper query plan takes much longer to execute

I am running a query like explain (analyze, buffers) select col1, col2, count(col3) as c from table1 group by 2, 1 order by 2, 1 When the work_mem is set at 4 MB, the plan looks like this: "...
1 vote
1 answer
853 views

In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
0 votes
2 answers
843 views

Insert records from one table to other with default serial

I have two tables with same structure, first column gid as serial, and many other columns after that. I want to insert selected rows from one table to the other. Without serial it is really easy: ...
0 votes
0 answers
24 views

postgresql logical replication replica identity on whole user tables

Customer has about 4960 tables in different schemas and will apply a logical replication whole database. Is there any easy way to set 4960 tables' replica identity full. (alter <schema.table> ...
0 votes
2 answers
36 views

Full folder /wal

How can I efficiently delete thousands of WAL (Write-Ahead Logging) files in the /wal directory in a PostgreSQL database to free up disk space while ensuring the integrity of the database? -rw------- ...
1 vote
0 answers
56 views

Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?

The issue My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
1 vote
2 answers
50 views

Need a Role that can CREATE USER but not allowed to GRANT Predefined Roles in PostgreSQL <16

We are administrating DB in kubernetes to our customers. For each new cluster, we create a new user with CREATE ROLE and CREATE DB roles given to them, so that they can create their own databases and ...
2 votes
1 answer
2k views

How to specify with a parameter a field to extract from a RECORD variable

Here's a table: CREATE TABLE t_heights (id INT, height REAL); INSERT INTO t_heights VALUES (1,53.63), (2,45.19), (3,47.06); Reading through the table rows with a FOR...LOOP and a RECORD type ...
1 vote
1 answer
4k views

How can I control how Pentaho DI formats integers coming from a text file?

I have a simple pipe-delimited text file with integers recorded as a sequence of numbers: 012345|00678 |various|text|fields There are some leading zeros, and some trailing white space, but no ...
0 votes
1 answer
778 views

'Could not fsync file' while running VACUUM FULL on a table

I'm getting an error for around half of my tables in Postgres while trying to run VACUUM FULL. For example, I have a table named 'qwerty', and when i try to vacuum it i get the following error: ERROR:...
0 votes
1 answer
22 views

Index for constantly updated rows

I have a cron on my server that runs every minute, it looks for all registered people who did not have their last connection updated in the last 10 minutes and changes the value in a column in the ...
0 votes
2 answers
40 views

Referencing information from same table in Postgresql

I have a geo table that contains countries localities (city, town, village, island, archipelago) locations (venue/business + boroughs/district/area), e.g - Big Ben, or Southwark Borough. For extra ...
0 votes
1 answer
51 views

Get the best performance out of postgres [closed]

So I have a postgres db in a machine A and I have configured grafana in machine B. Grafana has at least 10 dashboards with average of 10 panels each The issue I am facing is After few days there are ...
0 votes
1 answer
41 views

Postgres is not using CPU cores unsless running from psql or using "explain analyze"

Im running postgres 16.1 with a single user and no other activity on the server. I run this Test for many times and the results are very consistent. The Problem The query below will run with very ...
2 votes
1 answer
756 views

In Postgres, is an unprivileged role able to execute a function with elevated privileges?

For context, I am using: postgREST: a thin REST client on top of Postgres (which does all the heavy lifting) Azure AD: which handles identity and access of the organization Since users, roles, ...