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
1 vote
1 answer
303 views

How to force Redshift/Postgres to aggregate nth_value?

I'm trying to retrieve the 2nd, 3rd, 4th order/transaction from an orders table using a query that looks like this: select customer_email, nth_value(created_at, 1) over (partition by ...
1 vote
1 answer
919 views

Duplicate rows with primary key constraint in PostgreSQL 10.6

I have a table that's had CONSTRAINT table_pkey PRIMARY KEY (id) since setup. However, I recently found that I have around 100 duplicate rows (I ran select count(*) from (select count(*) from table ...
0 votes
2 answers
637 views

Parameterized Query without Prepared Statements and PgBouncer

my app is written in Go and I use PgBouncer as a connection pool to my Postgres Database. One downside of PgBouncer, in transaction mode, is that I can't use prepared statements. So I have 2 options, ...
0 votes
0 answers
47 views

Pgbackrest connection to Patroni cluster with HAProxy (VIP)

I have Patroni cluster with: 1 VIP 3 nodes HAProxy 3 nodes PostgreSQL Patroni 3 nodes etcd And dedicated pgbackrest server, from which I want to connect to Patroni cluster and take backups. But I ...
0 votes
1 answer
476 views

auto_explain does not log

looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf: shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain' ...
2 votes
1 answer
2k views

Repmgr: Error to promote Standby Cluster to Master Cluster in failover

I maked the configuration of the failover system based in Repmgr, but i don't haved success to promote the Standby Cluster to Master Cluster in case of failover. Following my configurations, this ...
0 votes
1 answer
1k views

PostgreSQL update column in one table with value from another, based on key from first table

Sorry about the title, I wasn't sure how best to word this. My situation is this: I have two tables: paper_author [paper_id, author_id, author_name] authors [author_id, author_name] Right now, the ...
0 votes
1 answer
72 views

Why is this PostgreSQL query with LIMIT so slow?

I have a .NET WebApi with two entities (please forgive the silly names, it's just an example): public class Father { public int Id { get; set; } public string Name { get; set; } = null!; ...
0 votes
0 answers
17 views

How does pg_restore detect missing depending entities?

A database origin was dumped in postgres "custom" format using: pg_dump -d origin --data-only -Fc > file.dump. The dump was then used to restore all data in a different database with the ...
1 vote
1 answer
52 views

Postgres BRIN index for time-series table

I have multiple time-series tables with Pkey on timestamp column. I'd like to test performance improvement if I change the index to be BRIN instead of BTREE. Yet, I'd also like to have a UNIQUE ...
0 votes
5 answers
132 views

Why do Databases use HEAP to store table data while other structure like B-tree exists?

Database Engines like MS SQL Server , PostgreSQL etc uses HEAP to store table data while using B-tree to store index data. Time complexity for most operations in B-tree is O(log n) while for HEAP it ...
1 vote
1 answer
295 views

How to update a foreign key with random values from the referenced table?

In order to generate test data for a postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'. How to do it? I ...
2 votes
0 answers
35 views

Upgrade and migration of PostgreSQL 10 and PostGIS 2.4 to PostgreSQL 16

I have a really old PostgreSQL and PostGIS bundle (10 and 2.4), that I want to upgrade and migrate to a different server. Upgrading postgres alone wouldn't be a problem, but PostGIS makes it tricky. I'...
0 votes
0 answers
39 views

Automatically create full backups every 7 days

I have a postgresql database configured with pgbackrest for backups. It retains full backups for 7 days: [global] repo1-retention-full-type=time repo1-retention-full=7 I've set up cron to create a ...
0 votes
1 answer
61 views

How can I get a range of values from sparse data

I have the following table (simplified): create table tunnels ( interval varchar not null, ts_start timestamp not null, ts_end timestamp, ...
3 votes
1 answer
727 views

ts_headline() not highlighting properly for <-> operator

ts_headline() does not seem to highlight the input text in a way that respects the <-> operator. The following query: SELECT ts_headline('English','This Commercial Bank does not have any ...
9 votes
2 answers
23k views

PostgresSQL dynamic execute with argument values in array

I'm wondering if this is possible in Postgres: Best explained using a contrived example: create or replace function test_function(filter_param1 varchar default null ...
0 votes
1 answer
646 views

Creating and dropping partition in PostgreSQL "on the fly"?

In our web-applicatons we have PostgreSQL database. Users write into and delete from this database ecology forecasts. Because data amount is significant (more than 100 Gb), we use declarative ...
1 vote
1 answer
264 views

postgres ts_headline function is not returning matches it should during full text search

Background: I've been using Postgres full-text search and it has met my needs quite well. Though there is some unexpected behavior that I cannot seem to wrap my head around. It has to do with the full-...
2 votes
1 answer
165 views

How to convert full-text search highlighted fragments into rows in PostgreSQL full-text search?

I am using PostgreSQL 10. I have a "booktbl" table which has 4 book_name paragraph_title content ts_content ts_content column is a tsvector column. When Iexecute this query: select book_name ,...
1 vote
1 answer
36 views

How to extract anonymous row type fields from window function result in PostgreSQL

Context: This question deals with extracting anonymous row type fields in general. The top answer notes that in v13, this was enabled: Allow ROW expressions to have their members extracted with ...
10 votes
2 answers
6k views

Show DOMAIN details

Showing the columns and constrains of a table in PostgreSQL is done with: \d+ <table_name> Which lists the columns, data types and modifiers for a table. How can I show the details and ...
3 votes
1 answer
457 views

On PostgreSQL 9.5, does partitioning a table prevent autovacuum from automatically running ANALYZE?

I'm trying to grasp the following sentence from https://www.postgresql.org/docs/9.5/static/sql-analyze.html The autovacuum daemon, however, will only consider inserts or updates on the parent table ...
0 votes
1 answer
35 views

In Postgres, duplicate rows on a column are not detected by select group by, but unique index built is failing with duplicate values

I have a table with users and column username. I am trying to build an index on the users#username column but it is failing with an error for a duplicate value. The value detected is an Arabic ...
0 votes
0 answers
34 views

Handling feed and feed subscriptions with document database

I have a data feed that contains channels that users can subscribe to. It's similar to Facebook or LinkedIn where each channel is a topic and users can subscribe to as many channels as they like and ...
0 votes
1 answer
4k views

invalid integer value "CONNECTION" for connection option "port" Previous connection kept

I'm new to postgres and I'm trying to configure table level logical replication(PUBLICATION/SUBSCRIPTION) on postgres-12. I have updated postgresql.conf file and updated wal_level=replica. Also, then ...
1 vote
1 answer
2k views

Postgres SQL on AWS RDS how to grant permission for schema, select on tables to user

I have logged in as superuser and created a readonly user. When I run grant command, it says that schema does not exists I am logging in as root: sql -h myhost_name -U root -d postgres -W grant ...
0 votes
2 answers
15 views

With an older version of PgAdmin, when creating a database, we cannot find where to set IS_TEMPLATE

We are using PgAdmin connecting to an instance of PostgreSQL. With PgAdmin, in the context of Servers > [the_server_login] > Databases > Create > Database, we input the necessary ...
0 votes
1 answer
308 views

Restore backup global using pgadmin

I created backup global by rightclicking Postgres 15 in pgadmin It generates .sql file. How do i restore the backup global in pgadmin ? I tried searching on google but didnt found the answer
2 votes
1 answer
810 views

How to count selectivity rows in PostgreSQL 8.2

I have problem with two queries on PostgreSQL v8.2. It seem like the optimizer chooses a wrong index: db=# explain select count(*), messagetype, alias from event where templateinfoid = 10 and ...
27 votes
4 answers
63k 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
0 answers
36 views

PostgreSQL Failure To Reinitialise Replica With pg_basebackup [duplicate]

I have a master-replica PostgreSQL 9.5 setup. I'm trying to reinitialize the replica by doing the following: Stop replica service ( /etc/init.d/postgresql stop ) Clear data directory ( rm -rf /path/...
0 votes
0 answers
20 views

How to setup pgBadger to work with journal correctly [closed]

I'm wondering how make pgBadger correctly work with systemd journal. The documentatin says, that --journalctl takes over the command for journalctl that is used to filter. According to documentation ...
0 votes
1 answer
605 views

PITR with barman fails - is my understanding even accurate?

I mange to take backups and recover them to my postgres-server. Now I want to see what PITR is able to do with these steps create a backup (20220111T062908) wait a minute create a new database (at 06:...
0 votes
1 answer
655 views

How do I get PostgreSQL to keep inserting after unique index violation?

I have ETL (Pentaho DI 8.2) to fill a Data Vault Hub table. Basically the ETL depends on the UNIQUE index to refuse duplicates of the business key column. In SQL Server this works great. With ...
0 votes
0 answers
34 views

I have a table of Histogram values and I need to do a P95 calculation and I don't know how to express this in SQL [closed]

I hope this is an ok place to be asking this. The table is tracking aggregated API response times essentially by storing the bars of histograms of the times for each API and day. I need to use this to ...
0 votes
1 answer
454 views

select time intervals from a nested query

I have a table which has the following columns: id, start_timestamp, device_id I am trying to get results of all start_timestamps within 5 seconds of a range of generated periodic timestamps. For ...
0 votes
1 answer
72 views

Slow query when using OR clause

I have 4 tables and it takes quite a long time to query with OR clause (AND clause works fine). News articles Rows: ~7000 Schema: id | integer publication_date | timestamp with time ...
11 votes
2 answers
12k views

What are the compression options for storing large amounts of text in Postgres?

I need to store large amounts of text in Postgres, mainly command logs and the output of long running commands and need to know if there are some settings that could help compress the data ...
0 votes
1 answer
46 views

How do I convert a varchar of nested json to jsonb, then flatten it?

Apache Hop doesn't have rich json processing, so I'm pushing the data to postgres to do the job With a deeply nested (subset example below) blob of json, how can I flatten, then store the result? I'll ...
0 votes
1 answer
1k views

Add column to database, used with logical replication generates large WAL files

I have a PostgreSQL 10 master database with a logical replication, but I faced with an issue. I added one more column (int, without default value) to a big table in master (weight 39gb, over 100 ...
0 votes
1 answer
4k views

How to import json table into PostgreSQL table?

I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data: [{ "token": "a978f9793a594d11860e9cf7962996d7", "name": "vehicle.moving", "description": "...
0 votes
0 answers
7 views

CloudSQL for Postgres S3 extension

In AWS RDS for Postgres, there is an extension called AWS_S3 that provides functions within Postgres that allow me to import data directly from a bucket into a table and export data from a table ...
1 vote
1 answer
2k views

How do you monitor PostgreSQL queries?

I have an application which uses PostgreSQL. I want to monitor all the queries that the application sends to the database. Is there a way to monitor the queries sent?
1 vote
2 answers
39 views

Select unique rows based on condition stored in another table

I want to select rows based on conditions that is fetched from the database. The tables structure are mentioned below. # Questions Table: ╔════════════╦═══════════════╦════════╗ ║ id ║ ...
2 votes
1 answer
321 views

How to influence the planner on Postgresql

I have the following: a complex query, 'match', that returns about 200 rows (ids) a huge table, 'usernames', indexed on id that maps them to usernames When I try to left join the query (1) with the ...
0 votes
1 answer
1k views

Restoring schemas with the sequences

This evening a developer asked to restore only schemas from a database into another database. A colleague of mine did this, but after that the developer came to me saying that the sequences are ...
0 votes
1 answer
5k views

Unrecognized configuration parameter when using set_config(text, text, boolean) in CTE

I'm trying to implement a simple ABAC system using row level security, with main policy defined as following: CREATE policy resource_access ON resource USING ( ( org_id::varchar = ...
0 votes
1 answer
368 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 ...
1 vote
1 answer
469 views

Schema changes on sharded database

I have performance issues with one particulary large table, 500+ Million rows, 300Gb data, Postgres 10.5. It is already partitioned. I am working on optimising it here and there, but that is not ...

1
3 4
5
6 7
338