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
764 views

Postgresql Logical replication initial slot snapshot too large

I'm trying to replicate some data from one database to another with logical replication. But it gets stuck into the catchup state. When I'm looking at the logs initial slot snapshot too large I'm not ...
7 votes
2 answers
25k views

How can I change an existing type from "bigint" to "bigserial"?

I have a PostgreSQL table with the following structure: I simply need to change the TYPE of prove_identity_id from bigint to bigserial. I read the docs but wasn't able to understand how to legally ...
0 votes
2 answers
44 views

Point in time Postgres restore

I was trying to do Point in Time Restore, I had my placed by previously backed up WAL file in my directory to where my restore command (/var/lib/postgresql/16/main/pg_wal/wal_archive) in postgres.conf ...
0 votes
1 answer
443 views

attach csv file on email sent from postgres

I have the following code to send emails and wanted to see if it's possible to attach a csv file? CREATE OR REPLACE FUNCTION public.send_email_html(text, text, text, text) RETURNS integer LANGUAGE ...
1 vote
2 answers
46 views

Merging information from multiple rows into one row

I have three tables event, person and event_person_map event: id integer PRIMARY KEY event_name text event_date date person: id integer PRIMARY KEY full_name text email_address text event_person_map:...
2 votes
1 answer
18 views

postgres_fdw passes/does not pass the LIMIT clause to the remote destination

I'm making a FDW (Foreign Data Wrapper) in PostgreSQL for myself using C. And in the test environment below, myFDW receives the query with/without LIMIT clause case by case. Test environment: psql → ...
5 votes
1 answer
972 views

postgresql: Replaying of streaming WAL files is slow on some standby servers

Environment: Postgresql 9.6.21. Multiple standby servers managed by patroni + Cluster: db11 (12345678901234567) ------+---------+-----+-----------+ | Member | Host | Role | ...
1 vote
1 answer
633 views

Adding a check constraint on a new column with default value on a large table

I have a large table (1–2m rows) and need to add a column. It is an integer column with a default of 0. I am also adding a check constraint that column >= 0. Currently, I am making the change in ...
0 votes
1 answer
852 views

postgres reverse jsonb array

Postgres 13 I have a table with jsonb array and I need to revert the array. Any idea on how I can do that? CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$ SELECT ARRAY( ...
0 votes
1 answer
1k views

PostgreSQL: running SQL statements in parallel in a SQL script

I use a for loop in a script to compute geometries according to values in a series, and insert the results into a table (t1). Is there a way to rewrite the loop below using parallelism? What approach ...
1 vote
1 answer
3k views

PostgreSQL - Update not working inside function

I am creating a web app that handles coupons. When a user claims a coupon the coupon batch count "should" be updated, and a new coupon is created. For some reason the UPDATE query does not work ...
0 votes
2 answers
57 views

Finalize GroupAggregate parent using Partial HashAggregate child nodes in Postgres query plan

I have seen in some query plans that the parent is a Finalize GroupAggregate but its child nodes are Partial HashAggregates. When does this make sense? As an example, I have a query that resembles ...
3 votes
2 answers
553 views

Specific changes to glibc 2.28 that affect PostgreSQL

I am preparing for upgrading a PostgreSQL server to Debian 10 (Buster) with the warning from Debian release notes about changes to gclibc affecting PostgreSQL in mind. The PostgreSQL wiki provides two ...
1 vote
3 answers
55 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 ...
0 votes
2 answers
402 views

How to know the locale that has been used to initialize a Postgres server?

I need it in order to create a new database with initdb with the same locale, otherwise pg_upgrade will fail.
0 votes
2 answers
27 views

PostgreSQL plan cache and variable number of arguments

Query: select * from table where col in ( values (?), (?), (?) ) The query text is generated, and number of (?) is variable and up to 10000. It works just fine, just wondering, whether Postgres ...
0 votes
1 answer
2k views

Transfer data from MS SQL to PostgreSQL with SSIS ODBC destination

I try to sync data from MS SQL to postgres with SSIS. I configure ODBC destination using psqlodbc_12_01 driver downloaded from here My SSIS package has a single data flow task which just gets data ...
0 votes
1 answer
26 views

How to store historical correlation matrices?

Every day we calculate the correlation between ~40 different financial instruments and want to be able to store them in our PostgreSQL database. What would be the best way of storing these values? We ...
2 votes
1 answer
2k views

Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}). There're ...
0 votes
1 answer
4k views

Invalid input syntax for timestamp error with Postgres Copy command

I am trying to load data in Postgres through COPY command in a SQL file COPY <table name> (<column1>, <column2>, <column3> etc etc) FROM stdin; 1 test1 test2 ...
0 votes
1 answer
376 views

Realtime Update MySQL on every Update occurred on PostgreSQL

I have a PostgreSQL for my accounting system and a MySQL database for my php website, i want to sync these two database for every update may occurred on PostgreSQL it also updates MySQL database, they ...
0 votes
1 answer
17 views

Restoring incremental backup of Postgres

I have restored basic backup successfully of Postgres. However, after that I have tried to restore incremental backup of Postgres(it was a couple of write to test table), and it had a couple of WAL ...
0 votes
1 answer
36 views

Postgres: how to insert and delete many records and to not run out of space

I have a PostgreSQL installation deployed in Kubernetes (with the CloudNative operator) and allocated a 25GB volume for it. My architecture involves several microservices interacting with various ...
0 votes
1 answer
27 views

How to cancel vacuum process in AWS RDS PostgreSQL

Is there a way to cancel an automatic vacuum in rds postgresql? When running select pg_cancel_backend(...); select pg_terminate_backend(...); with a user that has the rds_superuser role an error ...
1 vote
0 answers
3k views

PostgreSQL buffer page size limitations - rebuild with larger size?

We just ran into a limit on indexing due to the 'index size cannot be more than 1/3 of page size (2712)' error. This indicates the buffer page size is 8192 (8k). This seems awfully small during the ...
1 vote
2 answers
3k views

postgresql copy comand with date in filename

I have a query and want the output in a .csv file with today's date and time in the name (I am using PostgreSQL v12). COPY (SELECT * FROM mytable) TO 'C:\Users\me\mylocation\filename_date_time.csv' ...
0 votes
1 answer
1k views

Postgresql tries to connect to fe80::1, permission denied

I upgraded a couple of days ago to IPv6 on my local test machine and since then I've been experiencing some issue with connecting to my local PostgreSQL server. I set the host to 'localhost', which ...
1 vote
2 answers
35 views

Is there a way to calculate a column based on an expression of other columns?

I'm looking to select a column that's based on the value of other columns. The use case is that I'm interested in intermediate values of a complicated expression. Something like: SELECT 1.0 as avg, 2....
-2 votes
0 answers
15 views

Postgres - Add business days to timestamp

I'm trying to create an SLA date by adding 2 business days to a timestamp, how do a achieve this in postgres? I saw one post talking about date types but the solution did not work with timestamps ...
8 votes
4 answers
23k views

postgresql-setup --initdb with custom data directory

I am trying to setup Postgres 12 on Centos 8. Postgres shall be installed in the default directory i.e. /var/lib/pgsql, however I want the data directory to be in /data/pgsql I want to use postgresql-...
0 votes
1 answer
36 views

Postgres: replication conflicts with append only table and no interaction from read replica

I'm having trouble with canceled queries because of replication conflicts. Specifically, I get the message User query might have needed to see row versions that must be removed event if no deletes are ...
0 votes
1 answer
760 views

PostgreSQL size of used shared buffer vs actual result size

I am debugging a production issue where a regular index scan used in a join reports very high buffers usage (sometimes gigabytes) when inspecting using explain (analyse, buffers). Because it's reading ...
0 votes
2 answers
30 views

PostgreSQL16 on Windows - Clean up pg_wal archive location

Local pg_wal is cleaning up like it should. And pg_wal\archive_status has all the file with .done (Example: 0000000100000004000000DE.done). Archive_mode is set to on and archive_command is copying to ...
1 vote
1 answer
941 views

Postgres Citus error "complex joins are only supported when all distributed tables are joined on their distribution columns with equal operator"

I have two tables defined as distributed in Citus based on the same field: select create_distributed_table('gedi','clould_metadata_id'); select create_distributed_table('cloud_metadata','...
12 votes
3 answers
23k views

How to pass a row as a parameter to a Postgres function?

I have a function build_details(), which takes as one of its parameters, a single row/record from another table. This is what I want to do, but it doesn't work: SELECT build_details( SELECT * ...
-1 votes
1 answer
21 views

POSTGRES : Data import

DB Version : 13.x Source table schema\ CREATE TABLE mig_bus_data mig_id SERIAL, mig_data JSONB Target table schema\ CREATE TABLE BUSINESS id UNIQUE SERIAL, data1 <TYPEx&...
0 votes
1 answer
474 views

Pgpool - 3.5 configuration

[enterprisedb@pgdvdb1dr bin]$ 2017-06-20 11:34:41: pid 103313: WARNING: could not open configuration file: "pgpool.conf" 2017-06-20 11:34:41: pid 103313: DETAIL: using default configuration ...
0 votes
0 answers
12 views

How to change pgpool role automatically when pg_role changes after patroni cluster switchover leader?

I try to use pgpool-II as loadbalancer to distribute select queries to replica and other dml queries to leader in my patroni cluster. After switchover on Patroni cluster, pgpool can not recognize ...
0 votes
2 answers
736 views

Deadlock on same table when creating partition in PostgreSQL

PostgreSQL 14.1. I have a partitioned table liked this: create table dataset_session ( dataset_id integer not null references dataset (id) session_id integer not null, external_id uuid not ...
1 vote
1 answer
710 views

Proper *smart* shutdown of PostgreSQL server for PGDG apt packages under Ubuntu/Debian?

I've installed PostgreSQL under Ubuntu 20.4 using the PGDG apt repository. Each time I stop the DB server, e.g., with sudo service postgresql stop, the log says 'received fast shutdown request', but I'...
27 votes
3 answers
62k views

Find objects linked to a PostgreSQL role

Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9). I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ...
0 votes
1 answer
292 views

Unable to delete a referenced row due to a trigger on a referencing table?

I ran into some problems in using triggers to ensure only deletions from cascading deletes: CREATE TYPE status_type AS enum ('A'); CREATE TABLE parent( name VARCHAR(255) PRIMARY KEY ); CREATE ...
0 votes
0 answers
36 views

Why reading data in Postgres requires lock?

Using AWS Postgres, I am seeing some issues with LWLock:lock_manager. While investigating the issues, I've found this article. I can't understand why READING data requires any kind of lock? And why ...
0 votes
1 answer
1k views

How can Amazon RDS grant CREATE EXTENSION to their RDS_SUPERUSER role?

I've been trying for some time to allow ordinary, non-superuser users to create extensions. While reading across RDS documentation, I find that their internal "master user", rds_superuser, ...
0 votes
1 answer
153 views

Why is restored database faster than original?

I ran this explain analyse in my PostgreSQL 13.7 database: explain(analyze, verbose, buffers, settings) select * from main_table; Seq Scan on public.main_table (cost=0.00..10246.15 rows=220715 width=...
1 vote
2 answers
3k 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 ...
1 vote
2 answers
174 views

PosgtreSQL — Is it possible to specify a sequence name in a GENERATED BY DEFAULT AS IDENTITY column?

When using PHP’s lastInsertId() method with PostgreSQL, it’s necessary to know the name of the sequence. If I defined the column using GENERATED BY DEFAULT AS IDENTITY, I know I can get the sequence ...
0 votes
1 answer
535 views

Why could pgloader fail with 'undocumented reason' while migrating large MySQL database to PostgreSQL?

The database I would like to import is approximately 55GB in binary format, about 36 GB exported by mysqldump. But as we know even the postgres compatible mysqldump is not 100% compatible, so I'm ...
1 vote
1 answer
2k views

CREATE ROLE if not exists (several users) in postgres

PostgeSQL: create role/user if not exists? says do $$ begin if not exists (select * from pg_user where usename = 'new_role') then create role new_role password '...'; end if; end $$ ; I ...
0 votes
1 answer
2k views

Forcing a sequential scan over the index scan

I have this query select s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment from part,supplier,partsupp,nation,region where p_partkey=ps_partkey and s_suppkey=...