Questions tagged [postgresql]
All versions of PostgreSQL. Add an additional version-specific tag like [postgresql-13] if that context is relevant.
13,758
questions
0
votes
1answer
12 views
How to negate the question mark operator with to_jsonb in a PostgreSQL trigger?
I'd like to insert a timestamp when updating or inserting a new record from an application in a PostgreSQL database. If the NEW object does not already have such timestamp, I want to set it to NOW(), ...
0
votes
1answer
11 views
postgresql deadlocks: SELECT blocked and blocking query
Based on the 2nd answer on https://stackoverflow.com/questions/22775150/how-to-simulate-deadlock-in-postgresql I performed the following snippet:
Transaction 1 Transaction 2
BEGIN;
...
0
votes
0answers
8 views
How to test a virtual postgres 9.6 server will be greater than a physical postgres 9.6 server
We have a physical postgres server running on 9.6, I am looking to virtualize this server, but want to if possible utilise old postgres WAL logs if its doable?
Is it possible to replay the same ...
0
votes
2answers
31 views
Set column of a select query from an array in Postgres
Is there a way to set a column of a select query from an array? Assuming we have the following table and array:
items table
item
a
b
c
d
e
...
z
array of ids
ARRAY[3, 5, 11]
How can we run a ...
0
votes
2answers
24 views
can't backup postgres 13 with pg_dump
I'm trying to backup a postgresql 13 database with the following command on MacOS:
PGPASSWORD=“my_password” /Library/PostgreSQL/13/bin/pg_dump -h xx.xx.xx.xx -p 5432 -U postgres dbname > my_backup....
0
votes
0answers
18 views
What is the intended method for automatically/streamlinedly updating minor/patch PostgreSQL versions on Windows?
I currently run PostgreSQL 12.4 on Windows 10. Since the "BigSQL" distribution is no longer around, the only choice is to use the EnterpriseDB installer for the initial installation: https://...
2
votes
1answer
16 views
Postgres difference between restart_lsn and confirmed_flush
According to the documentation, restart_lsn is
The address (LSN) of oldest WAL which still might be required by the consumer of this slot and thus won't be automatically removed during checkpoints...
...
0
votes
0answers
39 views
How to efficiently deal with high inserts rate and query on multiple columns in PostgreSql 12
I have a log table that takes inserts at a rate of 5k-10k rows per second. Here is the table structure:
CREATE TABLE IF NOT EXISTS public.activity_log
(
"Id" bigint NOT NULL GENERATED BY ...
0
votes
1answer
26 views
Why scaling up an app by 50x exceed the PostgreSQL max_connection value of 100?
I went into this issue on purpose by scaling up a dockerized Python application 50x using this command docker-compose up --scale app=50 app:
This app connects to a single PostgreSQL database (also as ...
1
vote
1answer
40 views
PostgreSQL Query Optimization - Joining with a lookup table does not use index
I am attempting to optimize indexes and querying for a postgres database, and have run into some issues around a lookup table I have used to support joins for a many:many relationship. Here is the ...
0
votes
1answer
19 views
How to grant PostgreSQL user connect privileges to a database but not be able to query any tables in its public schema?
I have a database named somedb owned by user someuser. I have revoked all privileges from public to this database.
I want to grant connect privileges to anotheruser, but I don't want anotheruser to be ...
0
votes
1answer
26 views
Strange selection pushdown in postgres 13
Trying to sample (not uniformly) a flight delay table in postgres I stumbled on a weird pushdown of selection with a subquery.
with
grp_counts as (select month , 0.01 c from flights group by ...
0
votes
1answer
10 views
convert RDS csvlog into pgreplay-go compatible for replaying
Before upgrading AWS RDS PostgreSQL version at production, I want to replay 1-hour recorded logs at the test instance.
Unfortunately pgreplay is single-threaded application, limited by single CPU ...
4
votes
1answer
50 views
INSERT ON CONFLICT DO NOTHING efficiency (PostgreSQL)
In Postgres, is it inefficient to perform an “INSERT… ON CONFLICT DO NOTHING” if >99.9% of the time the record exists and nothing is done?
E.g. let’s say I have a Node process which fetches 100k ...
2
votes
2answers
67 views
Why is this SQL query with UNION significantly faster than the same query without?
I'm trying to optimize a query, which never completes on Postgres 12.7. It takes hours, even days, make the CPU goes 100%, and never returns:
SELECT "id", "counter", "item_id&...
0
votes
1answer
20 views
Show more details on Postgres logical replication errors
Wonder if there is a way to add more details (like the column name, database) to logical replication errors in case of missing columns. I got general log entries like this:
2021-09-16 14:47:37.149 CDT ...
0
votes
0answers
13 views
What is the API based data? [closed]
I am new to a company (IOT company),
I have been hearing that we are an API-Data Scheme architecture
i don't know what does that mean, but when I asked they said that our data scheme is not fixed and ...
0
votes
1answer
27 views
Ordering of LSN in postgres WAL files
There is a mysterious comment in the source code of debezium, which says that LSNs need not be ordered in the WAL file. How can that be possible? If LSN represents the byte offset in the WAL, how can ...
0
votes
1answer
21 views
Non-Incrementing Generated ID for PostgreSQL
I am currently trying to make a table with an ID that is unique and auto-generated by PostgreSQL but not-autogenerated and doesn't start from 1
Ideally, I'd prefer for it to be a certain length (ie 15 ...
0
votes
2answers
35 views
Postgresql explain alter table add column doesn't work
I'm using Postgresql 12 (AWS RDS) and pgAdmin4.
While the following statement works
explain select * from table "Commands";
The next one
explain alter table "Commands" drop column &...
0
votes
0answers
13 views
pgbackrest: Is it possible to have repo and hot standby on the same node?
I am trying to use 2 nodes:
Node A as primary node.
Node B as standby + REPO node.
Is it possible to do this?
My configuration looks like this right now:
Primary node - pgbackrest.conf
[global]
...
-1
votes
0answers
15 views
How to design a database for search filters?
We have a normalized relational database with lots of tables. We plan on adding search filters that can be combined together. We don't know which filters are going to be used the most at this time, ...
0
votes
1answer
23 views
Postgres: Insert all valid data into a table while logging errors
I am attempting to devise a method that will enable me to load data into a table which will load all valid data and log the invalid records. Ideally, I'd like to do this without looping through the ...
0
votes
1answer
19 views
Get number of deleted pages in PostgreSQL table/index?
How can I get the number of deleted pages in a table or index?
My purpose in asking is to modify an index bloat query that otherwise doesn't take into account which pages are deleted (and therefore ...
0
votes
2answers
41 views
Vacuum on large table sometimes takes very long
In a PostgreSQL 11 database server I have a large table (932 million rows, 2150GB, not partitioned) into which I insert about 2-3 million rows every night with a batch job. The table was created with ...
0
votes
1answer
20 views
Unable to locate and login to by PostgreSQL
I am using Ubuntu 20.04 LTS, and I have configured PostgreSQL 9.5 to work on the server. I am trying to log in but I have forgotten the password for the user postgres and therefore I tried to reset it ...
0
votes
1answer
14 views
Postgres preventing update on "events" table but insert "warning" event
I'm facing a bit of a problem on some restrictions I'm trying to implement on my postgresql database.
My dilemma is as follows: I have an event table that logs basically everything that happens on my ...
0
votes
1answer
16 views
postgres: reset pg_last_xact_replay_timestamp()
We have a pg10 replica that got promoted to master and have monitoring around the following query to monitor replication
SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) as lag
...
0
votes
1answer
20 views
Postgresql 12 Custom Hash Function
I have a large table that I would like to partition. The field I want to partition against is a string that contains a coded value, but appears as an integer stored as a string. The "number" ...
1
vote
1answer
26 views
Show all ForeignKeys which point to a particular row
How to list all Foreignkeys (table + column + pk-of-that-row) which point to a particular row in a given table.
Example: the table "user" has row with PK '1234'.
I want to see all FKs which ...
0
votes
0answers
33 views
Performance Regression Postgres 9.6 to 10.17
We recently upgraded our Aurora Postgres DB from 9.6 to 10.17
Following this upgrade we noticed an overall regression in CPU utilization i.e. an increase of ~10 percentage points (50% growth week-on-...
0
votes
0answers
13 views
How to compile a list of read-only functions from Postgres
I want to compile a read_only_function_list for load balancing with Pgpool. How may I list all read only functions from Postgres?
0
votes
0answers
25 views
Running a standalone postgres instance alongside existing servers
I'm developing an application that heavily relies on Postgres. Currently, users are required to have Postgres set up and place database credentials in a config file that my app reads.
There are a ...
1
vote
1answer
22 views
postgres `in` operator return in query order [duplicate]
I was doing query using in operator in postgres, something like:
select * from items where id in (1,2,3);
is there any guarantee that returning entries are in 1,2,3 order?
According to my ...
0
votes
0answers
44 views
PostgreSQL Convert JSONB array to string
I am trying to flatten a nested JSONB object into a string so that I can run regex on it.
To retrieve the relevant text fields, I'm using
SELECT jsonb_path_query_array(posts.content, 'strict $.**.text'...
0
votes
1answer
33 views
Postgresql: 'upserting' into two tables (vertical partitioning) using the same id with a unique constraint
I have two tables, one containing all the hot columns and one the static ones. The static table has an unique constraint. When the conflict on the unique constraint triggers only the hot columns in ...
2
votes
1answer
50 views
PostgreSQL relative update breaks unique constraint
I have unique index on column for UPSERT. When I am trying to update column using v = v + 1 expression my unique index breaks.
SQL
CREATE TABLE test(v bigint, data jsonb DEFAULT '{}'::jsonb);
INSERT ...
0
votes
1answer
26 views
Postgres changes query plan for prepared statement to something poor after multiple invocations
Postgres 13.4
I have a prepared statement that is similar to this:
PREPARE my_statement AS
SELECT * FROM products WHERE (normalized_name = $1 OR distinct_normalized_names @> array['$1']) AND ...
1
vote
1answer
20 views
Collation for accent-insensitive comparison on Postgres?
On PG 13 documentation, there are several examples of ICU collations for specialized purposes. It is also mentioned that ICU locales exist that allow creating collations to ignore accents, and that ...
-1
votes
1answer
24 views
Why isn't GIN index being used for JSONB exact match on text?
I have this index:
CREATE INDEX ginner ON public.documents USING gin (data)
And "explaining" this query:
select * from public.documents d where data::json->>'name' = 'Barbara Fritsch'
...
1
vote
1answer
33 views
Select inside select Postgres
select
t.col1,
t.col2,
a.col1,
b.somecolumn
VQ.a,
VQ.b,
VQ.e,
VQ.d,
VQ.f,
(select
t.status as a,
p.id as b,
p.permit as c,
p.des as d,
p.error_code ...
0
votes
0answers
26 views
WAL ends before end of online backup - not sure about proposed solution?
I'm facing the exact same problem described here where one of my standby cluster nodes refuses to start: postgres: WAL ends before end of online backup
I understand the analysis there just not sure ...
2
votes
3answers
43 views
Postgresql - How to interpret backup_label contents?
Postgres version 11.
I've read this: https://www.postgresql.org/docs/11/app-pgbasebackup.html
But can't really find what I'm looking for.
Performing a basebackup creates 2 files for me: base.tar.gz ...
2
votes
1answer
38 views
Why is my Postgres 9.6 Create Index Concurrently INVALID?
Am trying to create an index concurrently on production. However when doing so the index when viewing via \d says INVALID.
Why is this the case? Never seen it happen before.
Have tried re-creating but ...
0
votes
2answers
24 views
Using Postgres COMMENTs for tagging tables and fields for auto-discovery
I am looking to tag fields in postgres tables with something similar to Golang struct tags to facilitate field discovery for a data ingestion pipeline. With tags, I could query to find tables and ...
0
votes
0answers
31 views
timescaleDB insertion speed issue
I’m trying to use pandas to interact with timescaleDB. I initiate my DB in DBeaver with the following
CREATE TABLE db_009a005a_df_downloaded_grand (
symbol VARCHAR(32),
timestamp TIMESTAMP ...
0
votes
1answer
26 views
UPSERT unless the date is the same
There's a table
CREATE TABLE IF NOT EXISTS table00 (
userid int8 PRIMARY KEY,
save date NOT NULL,
value int4
);
where I want to upsert values IFF the date is not the same ...
1
vote
1answer
16 views
Using the array result of a select statement returns ERROR: pq: operator does not exist: integer = integer[]
The query in question is the following:
SELECT * FROM options WHERE id = any(SELECT option_ids FROM workshops WHERE id=3)
ERROR: pq: operator does not exist: integer = integer[]
Basically the ...
0
votes
3answers
30 views
How can I return multiple columns from an array of ids?
I have a two tables like the following:
workshops table
+----+--------------+------------+
| id | name | option_ids |
+----+--------------+------------+
| 1 | Conversation | {2,3} |
+----...
-1
votes
1answer
12 views
How to temporarily disable foreign keys in Amazon RDS Aurora PostgreSQL? [duplicate]
Hi I am using DMS to load data from physical server to AWS Aurora PostgreSQL, but I am getting error for foreign key constraint.
I want to disable using any sp / function. Could you please help me?
