Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
17,168
questions
0
votes
3
answers
85
views
How to add the extension pg_squeeze to shared_preload_libraries in Azure Postgres?
I am trying to enable pg_squeeze on a Azure Postgres instance. I have successfully enabled it in the azure extensions. However, when I go to add it to the shared_preload_libraries I get the following ...
- 332
1
vote
1
answer
27
views
How to extract indeterminate attributes from xml in a postgres db?
I've inherited a legacy DB in which excel data is stored in a text column of a table in a postgres DB. A value from that column might look like:
<Sheets>
<Sheet1>
<Addresses E54=&...
-1
votes
1
answer
98
views
How to sum values within blocks with Postgres?
Given this table called prices:
my_date
my_value
my_separator
2012-02-10
100
1
2012-02-11
120
1
2012-02-12
120
1
2012-02-13
110
1
2012-02-14
100
0
2012-02-15
115
0
2012-02-16
130
1
2012-02-17
...
- 1,203
0
votes
2
answers
28
views
Is there a way to "rearrange" arrays when selecting?
I have a table post_likes with one column being a post ID (uint), and another column being an array of IDs (uint[]) of users who liked the post.
If I'm selecting info from multiple tables on multiple ...
- 103
0
votes
0
answers
12
views
PostgreSQL: pg_settings view's "pending_restart" not showing correct value
I have seeing a strange behaviour with PostgreSQL13 pg_settings system view. I tried updating max_connections using SET command and left without a restart. But if I query from the same session or any ...
- 341
0
votes
0
answers
22
views
How do I successfully migrate a (very) old PostgreSQL database with PostGIS objects to a new version of PostgreSQL and PostGIS?
I've been trying to migrate a PostgreSQL database on version 9.0.1 with PostGIS objects to Postgres version 12.9 (both on linux) and I've been encountering numerous errors. I believe the source ...
- 620
0
votes
1
answer
16
views
PostgreSQL physical and logical replication - key values
PostgreSQL.
When using physical replication, does a given row in a table in the replica database have the same primary key value as its corresponding row in the primary database?
Same question when ...
0
votes
0
answers
9
views
Apgdiff doesn't retain "<schema_name>.<tablename>" format in the diff
I am using apgdiff tool to generate a diff between two sql dump files.
CREATE TABLE abc.xyz (...)
In my final diff file, the tool adds a SET search_path=abc,pg_catalog;
I would expect it to not play ...
- 101
0
votes
1
answer
30
views
Querying whole PostgreSQL table in replica on CTID
I noticed it is quite fast to query a whole PostgreSQL table in chunks on ctid as explained for example in this Airbyte blog post. I need this for a syncing task that might take from hours to a few ...
- 101
1
vote
1
answer
61
views
Impact of Reordering Composite Index on PostgreSQL: Performance, Space, and Overhead Considerations
I am optimizing SQL query performance by reordering a composite index in PostgreSQL. I need to understand potential repercussions, including space usage, data saving overhead, and any other impacts of ...
- 13
1
vote
2
answers
53
views
How to use an expression like date_trunc() in GROUP BY?
I have a couple of joined tables with industrial data on them:
create table v2.tag (
tag_id integer generated always as identity,
tag text not null,
primary key (tag_id),
unique (tag)
);
...
- 113
0
votes
1
answer
73
views
PostgreSQL: Approach for aggregating records and upsert vs delete and insert
We have a file processing service that extracts some device configuration information from daily files and stores it into PostgreSQL tables.
We have two layers of tables,
The loading tables that ...
- 143
1
vote
1
answer
37
views
Optimizing Query to Retrieve Names from pg_buffercache View in PostgreSQL
I had a database with large amount of tables / partition tables where I need to retrieve the database name, relation name, parent relation name and tablespace name instead of their respective OIDs ...
- 55
0
votes
0
answers
17
views
How to select the closest point to another point based on the value of an attribute?
I have a stream layer.
I created a layer of cross sections on the watercourses (CF).
I extract the cross-section and watercourse intersection point layer (I).
I add altitude z to layer I and z+5 to ...
- 125
0
votes
1
answer
39
views
Locking write to tables while Postgres procedure is executing
I have created a procedure in Postgres that will create a table with the contents of another three tables (SELECT with JOINS). Then the resulting table will be altered adding two columns that didnt ...
- 103
1
vote
1
answer
27
views
update with group column in the same table
I try to assign random value in ref column.
You can see my try in https://dbfiddle.uk/949fv4di
Result should be :
ref
prod
23
prodA
23
prodA
12
prodB
12
prodB
12
prodB
23
prodA
7
prodC
23
...
- 113
1
vote
1
answer
47
views
Setting a value for max_locks_per_transaction
We find ourselves in a situation where we need to increase the value max_locks_per_transaction as the default isn't appropriate. I've spent some time looking and I can't find any information to ...
- 23
0
votes
1
answer
115
views
When will PostgreSQL use /dev/shm?
I have been deploying PostgreSQL 9.4 on Docker for several years, and recently I upgraded the PostgreSQL version to 15.7. However, I have noticed that after upgrading to 15.7, some queries will start ...
0
votes
1
answer
64
views
Postgresql query with soft ordering
I have a table with around 100.000.000 entries mostly ordered by date of creation (without timezones and some entries <0.01% the primary key and the correct order would be identical).
One common ...
- 103
1
vote
1
answer
37
views
How to sort query results using random() order but stable, e.g. based on the day of the week?
I have a query such as this:
SELECT
cs1.id,
cs1.uid,
cs1.title,
cs1.description
FROM related_chat_session rcs1
INNER JOIN chat_session cs1
ON cs1.id = rcs1.related_chat_session_id
WHERE
rcs1....
- 13
0
votes
0
answers
32
views
Postgres hot standby has a negative replication delay
I am trying to calculate the replication delay of a hot standby in Postgres. I am using the following calculation:
SELECT pg_wal_lsn_diff(pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()) AS ...
- 101
-1
votes
1
answer
34
views
Remove Null if Match
Table A
Column1
Column2
column3
Mobile
group_filtering
on
Mobile
offer_group
1619
Mobile
country_code
0
Lan
duration
10
Lan
type
auto
Table B
ID
Name
Description
1619
B2B
B2B Club
Expected ...
0
votes
1
answer
44
views
PostgreSQL: Best practises for Deleting huge volume of Data in Replicated Environment
We have a prod database (PostgreSQL13) with a hot-standby(physical replication) as well as logical replication to a different subscriber server. Now we are in requirement of purging few year's record ...
- 341
0
votes
1
answer
64
views
The refresh time for a PostgreSQL materialized view is higher than creating a new one
The refresh time for a PostgreSQL materialized view is longer than creating a new one.
For example, the data select query executes in 10 minutes, and creating a new materialized view takes 15 minutes. ...
0
votes
0
answers
51
views
What is the most performant way of modelling a many-to-many-manies relationship?
I have a data model that looks like this:
CREATE TABLE foo (
id PRIMARY KEY
);
CREATE TABLE bar (
id PRIMARY KEY
);
CREATE TABLE baz (
id PRIMARY KEY
);
CREATE TABLE users (
id PRIMARY KEY
);
...
- 101
0
votes
0
answers
31
views
Quickest way to see sql queries that have modified a specific row in Postgres [duplicate]
I'm trying to find a way to see what queries ran in the past have modified the data of a row.
For example say you have a table like this:
users table
id
name
email
1
Bill
[email protected]
Let's say ...
- 101
0
votes
1
answer
56
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%'...
- 115
0
votes
3
answers
43
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 ...
0
votes
1
answer
54
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 ...
- 129
2
votes
2
answers
78
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 ...
- 23
0
votes
1
answer
39
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 ...
- 620
0
votes
1
answer
29
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 ...
- 3,448
0
votes
1
answer
35
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 ...
- 143
0
votes
1
answer
30
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 "...
- 539
0
votes
0
answers
30
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
2
answers
75
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 ...
- 203
0
votes
1
answer
30
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 ...
- 111
0
votes
1
answer
26
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 ...
- 101
0
votes
0
answers
39
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 ...
- 1
0
votes
0
answers
43
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(...
- 103
0
votes
1
answer
35
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 ...
- 23
0
votes
0
answers
17
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 ...
- 35
0
votes
1
answer
49
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 ...
- 1
2
votes
1
answer
101
views
DELETE Query with IN condition having performance issues in production
Introduction
Let's say we have the tables as shown in the diagram,
Corresponding DDL:
CREATE TABLE Manager (
manager_id INT NOT NULL,
manager_name CHAR(50) NOT NULL,
PRIMARY KEY (...
0
votes
0
answers
12
views
Failed to prune sessions: no pg_hba.conf entry for host, although connection works fine
I am using an AWS RDS Postgres database and Typeorm to connect to the database. I have also configured the RDS database to require a SSL connection. I pass the CA certificate to the database like this:...
- 101
0
votes
1
answer
54
views
How to create a table with only indexes and no actual data?
Question: Is is possible to create a table in PostgreSQL for the sole purpose of creating specialized indexes (used for advanced search use-cases), without storing duplicate data in such tables?
...
- 333
0
votes
0
answers
28
views
pgAdmin 4: How can I change the default starting value of sequences?
I don't like sequences starting from 1 while vectors, arrays etc. start from 0. Having sequences start from 0 makes it easier to store the rows in a container easier.
Each time I create a table in ...
0
votes
1
answer
48
views
How to Index/Query JSONB Columns with Arbitrary Keys for Efficient Membership Queries in PostgreSQL?
In Postgres, how do I properly index/query jsonb columns that only contain key-value pairs where the keys are arbitrary, to speed up membership operations?
I have a table structured like this:
CREATE ...
- 111
0
votes
2
answers
114
views
Why must I commit after the ALTER TABLE DDL to make changes visible?
If I execute this in DBeaver:
alter table classes alter column reference set not null;
on my local database which only I have open in DBeaver, it completes successfully.
But then I can't open the ...
- 203
0
votes
1
answer
28
views
PostgreSQL - How to optimize slow Bitmap Heap Scan?
My app is calculating the SuperTrend indicator over the price of multiple cryptocurrencies. I have the following table and indices:
-- DDL generated by Postico 2.1.1
-- Not all database features are ...
- 23