Skip to main content

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
0 answers
6 views

Trigram index is slow for common terms

I have a PostgreSQL database with bunch of string columns in a table with a trigram index on them using pg_trgm. They are queried like this SELECT [...] FROM mytable WHERE mycolumn ILIKE '%searchterm%'...
0 votes
2 answers
24 views

Dynamic column selection in a subquery

I have two tables, one containing polls and the other poll votes, like this (simplified): CREATE TABLE IF NOT EXISTS polls ( id SERIAL PRIMARY KEY, token VARCHAR(20) ); CREATE TABLE IF NOT EXISTS ...
1 vote
1 answer
690 views

When are wal released or removed in postgresql?

I am relatively new to postgresql administration and I still have doubts about the operation or management of the wal files, specifically how these files are released or deleted, have the conception ...
2 votes
2 answers
45 views

Many-to-many: select an entity by matching relationship

I have a many to many relationship implemented: CREATE TABLE public.message ( id BIGSERIAL PRIMARY KEY, name varchar(40) UNIQUE NOT NULL ); CREATE TABLE public.package( id BIGSERIAL ...
1 vote
1 answer
657 views

Remap array columns to single column with value

Here is the sample database schema. | id | Critical | High | Low | -------------------------------------------- | 1 | {apple, ball} | {cat} | {dog, egg} | Now I want to remap these to ...
5 votes
2 answers
2k views

List PostgreSQL extensions per database

I have a Postgres 9.5 server with 30 databases. I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin. I know that SELECT * FROM ...
2 votes
1 answer
2k views

Export each row of a table into a separate text file

I have a table in my database that contains data with three columns: id, name, and notes. The notes column contains unstructured text data that I want exported for each row as its own text file. This ...
0 votes
1 answer
36 views

UNIQUE CONSTRAINT on two columns, but duplicates appeared: how to track down and fix? [duplicate]

We have table for users: CREATE UNIQUE INDEX uk_users_login_socnet ON public.users USING btree (login, socnet) We had maintenance on our server, during which it was backed up and replicated to ...
0 votes
1 answer
2k 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 ...
1 vote
1 answer
2k views

Delete WAL archive files

I have around 200Gb of WAL archives. Can I just delete them all without considering any database restoration? I am just asking whether it is safe to delete them all or at least 50% of them. I read a ...
2 votes
1 answer
1k views

Arbitrary queries on n:m relationship, including "all" and "any"

I'm using postgres >= 9.6. I have tasks, tags and task_tags tables, for a typical n:m relationship between tasks and tags. I'd like to be able to programmatically build queries against the tasks ...
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
1k views

Java PostgreSQL library seems to convert dates to local time zone automatically

PostgreSQL 14.6 Ubuntu 22.04 I am using postgresql-42.5.4.jar which I downloaded from pgJDBC. I use this library to get data from a database and display it on a website running locally. The web server ...
2 votes
1 answer
819 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 ...
0 votes
1 answer
2k views

PostgreSQL High Memory consumption

We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this. ps aux -...
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": "...
14 votes
1 answer
14k views

Why *not* ERROR: index row size xxxx exceeds maximum 2712 for index "foo"?

We have repeatedly seen failing attempts to index columns with values exceeding a maximum size. Postgres 10 has this kind of error message for it: ERROR: index row size xxxx exceeds maximum 2712 for ...
0 votes
1 answer
4k views

Using pgagent with docker?

I wanted to dive into the world of postgresql so I installed both postgresql and pgadmin using docker (with the portainer web ui) and I found out that for automatic jobs you gotta install pgagent too ...
0 votes
1 answer
45 views

Best database solution for high volume tables / data indexing (snapshots) [PostgreSQL]

My problem at the moment, is that I need to index/save snapshots of some data every x minutes, so every x minutes I am inserting around 500k new rows, into a table, which each one represents a ...
2 votes
1 answer
330 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 ...
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
2 answers
473 views

Postgresql Aggregate Query With Sort Very Slow

I have a simple aggregate query that is running very slowly (198s) on a table with ~4.6mil rows, can anyone suggest how to optimise? SELECT u.user_id,u.firstname,u.surname,u.image_url,p.pool_name, ...
0 votes
1 answer
675 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 ...
0 votes
0 answers
38 views

Designing enrollment transfers

Here is a simplified asciigram of my database: is_paid, paid_at | -----------Enrollments-------- / \ | ...
0 votes
1 answer
25 views

Is it possible to make a No-DELETE replica/follower database?

I would like to allow an application's database to execute DELETE statements. However, for analytics purposes, I would like to keep all data, even deleted data. I'm trying to figure out if there is a ...
0 votes
1 answer
18 views

How to connect/take over a Postgresql instance without any login?

I have inherited a Postgresql server but have no records of the admin credentials or any user account. I do have local admin access to the Windows VM that hosts this Postgresql server. How can I gain ...
0 votes
1 answer
668 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:...
1 vote
1 answer
2k views

Set difference for jsonb

The hstore type in Postgres provides an operation to calculate the set difference (EXCEPT) of two records. Is there something similar for jsonb? The best I've got is SELECT * FROM jsonb_each('{"a&...
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 ...
0 votes
1 answer
30 views

How can I find or log inbound traffic connecting to a PostgreSQL database instance?

I have a postgres instance where I need to find out who/what is connecting. The installed version of postgres is 9.0.1. The instance sits on a linux box, SUSE I believe. Where could I look if it is ...
1 vote
1 answer
3k 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?
0 votes
0 answers
22 views

How to patch a Postgresql located on a Windows server? [duplicate]

I'm new to Postgresql administration and looking for some guidance on process to apply patch/upgrade to a PostgresSQL server installed on a Windows VM. Is there a patch executable or do I need to ...
0 votes
1 answer
1k views

How do you find specific column name in function/sequences

I am trying to see if a specific column exists in any functions/sequences. I have found a script which can search tables, is there anything out there that would allow me to search in functions/...
0 votes
1 answer
45 views

Postgres: is there a way to speed up changing column type from int4 to int8?

We have about 100 simple changes to make to our DB schema like this: alter table transactions alter customer_sport_id type bigint; Before it was int4. Most of the changed columns have one or more ...
1 vote
1 answer
960 views

Master ip has changed in postgresql replication

I have postgresql replication system with two machines, suppose I want to change the master ip,the streaming to slave machine will stop. Can I resume streaming with the new master ip instead of doing ...
4 votes
1 answer
1k views

Row level security in PostgreSQL for different groups

Need a little help with row-level security in Postgres. I have several tables and three group roles: group1 group2 group3 I have several login roles (users) that are members of each group, while &...
0 votes
1 answer
28 views

Performance Impact of Partial Index on INSERTs Not Involving the Partial Match

Given: postgres=# create table testing(a int primary key, b int not null); CREATE TABLE postgres=# create index on testing (b) where b = 0; CREATE INDEX postgres=# \d testing Table "...
2 votes
1 answer
2k views

Access denied error when archiving WAL during postgresql 9.5 replication setup

I want to setup Postgres replication on Windows. I followed the given youtube url step by step: youtube video. For archive_command I use this: archive_command = 'copy "%p" "\\\\ip-address of ...
1 vote
1 answer
701 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
24 views

Having trouble with composite data type in Postgres

I am trying to test an api call that loads data into my postgres db table via postman. The table includes composite value types, and that seems to be causing an error:{"error":"...
0 votes
1 answer
561 views

SELECT Every Parent Table Record and INSET Multiple Record in Child Table Against the Parent_Id

Suppose I have two tables name company and screensavers as given below: **Company:** id (pk auto inc) name (varchar) **ScereenSavers:** id (pk auto inc) name (varchar) path (varchar) company_id (int)...
0 votes
0 answers
18 views

INSTEAD OF INSERT ON never gets triggered from continous aggregate (timescale)

I'm trying to make a trigger based on when a continuous aggregate is updated. I had the trigger working on the source table, but when trying to add it on the aggregate: CREATE TRIGGER ...
0 votes
1 answer
23 views

Postgres trigger with after and on row appears to not run after writing to row

I have the following two tables: CREATE TABLE qanda.group_questions ( id uuid DEFAULT gen_random_uuid() NOT NULL, category_id uuid NULL, business_model_id int4 NULL, industry_id int4 ...
0 votes
1 answer
471 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
765 views

Create pgSQL DBs on different drives in ms windows

I have PostgreSQL 15 installed on a Windows Server 2019 Standard VM. I've installed pgSQL using the default configuration on the C:\ drive. I'm currently using the default data directory of C:...\data....
8 votes
4 answers
23k views

postgresql: How to disable all indexes of a table

There are more than 100 indexes on my table. i want to disable all the indexes temporary and want to re-enabled them once the indexing is done. It is not feasible to go and type every index name to ...
0 votes
1 answer
23 views

Adding FK to a newly attached partition slow

We have a partitioned table from which we attach and detach partitions for archiving purposes. When attempting to add a foreign key on the partitioned table, the process is taking a long time (up to ...
0 votes
0 answers
16 views

Postgres Barman archive command failed

For production environment, I am using this archive command in my postgresql server. barman-wal-archive backup pg /var/lib/edb-pge/15/data/pg_wal/%f Now when I manually run this command on my ...
0 votes
0 answers
19 views

postgresql (15) Python event_trigger

What's the syntax used to write a Postgresql event trigger in Python? This CREATE OR REPLACE FUNCTION log_schema_changes() RETURNS EVENT_TRIGGER LANGUAGE plpython3u AS $$ import plpy plyp.warning(...
0 votes
0 answers
15 views

Change cipher PBEWithMD5AndDES

I am using PostgreSQL 14. I created a database, set up a user, and performed a migration. Now, the database is connected to the application, and a security test was conducted. I have been tasked with ...

1
2 3 4 5
342