Questions tagged [postgresql-13]
Specifically for PostgreSQL version 13
17
questions
2
votes
2answers
110 views
Update a column to same value for all rows in a group of the group by
I have the following table:
name
key
uuid
na1
k1
NULL
na2
k2
NULL
na3
k1
NULL
If two or more rows have the same key value then I want to update the uuid value to the same uuid for those rows. If ...
0
votes
0answers
10 views
Most efficient method to import bulk JSON data from different sources in postgresql?
I need to import data from thousands of URLs, here is an example of the data:
[{"date":"20201006T120000Z","uri":"secret","val":"1765.756"},{...
1
vote
0answers
22 views
postgresql - Advance replication slot as part of a transaction
Let me illustrate my use-case, I have a Python middleware doing this data transformation on the top of a postgresql replication slot, I would like to leverage Postgresql transactional capabilities.
I ...
0
votes
0answers
16 views
Slow insert on conflict do nothing for Postgres 13
I'have rather odd case.
I have a "cache" table in Postgres and I need to insert 100K - 1M records in parallel from different sources. Sources can try to insert duplicated data (by Primary ...
0
votes
1answer
55 views
Why does pre-sorted index not help me in window function?
I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
%(max_providers_to_target_per_territory)s as max_providers,
case
...
0
votes
1answer
15 views
Equivalent of PGAdmin III Server Status Activity Window in PGAdmin 4 v5
I'm in the process of upgrading to Postgres 13.2 from a much older version. This entails an upgrade from PGAdmin III to PGAdmin 4.
PGAdmin III has a brilliantly designed feature called Server Status ...
0
votes
1answer
26 views
Query sometimes hangs (but can be executed in less than 4s when run in parallel)
I have a Flask API that has 2 endpoints.
When the user clicks a button, the front-end first calls a /calculate route, which computes an expensive query and appends the results to a table called ...
1
vote
1answer
21 views
PostgreSQL Reduce Writes on Flash Storage (TBW) [closed]
I'm currently a startup and using non server grade NVMEs/SSD on my database server.
My motherboard , CPU , DDR (ECC) etc are server grade except for the flash part.
I'm looking for ways to reduce the ...
1
vote
1answer
68 views
Upgrading Postgres 12 to 13: 'pg_upgrade.exe' fails
I'm attempting to upgrade a Postgres instance from version 12 to version 13, following the steps outlined at https://www.postgresql.org/docs/13/pgupgrade.html.
From an elevated command prompt, I'm ...
0
votes
0answers
12 views
How correctly implement and enforce a default option among a list of rows is always present in postgresql
I have a list of address, and need to set which one is the default.
This look easy:
CREATE TABLE address (
address_code TEXT PRIMARY KEY CHECK (not_empty(address_code)),
customer_code TEXT NOT ...
0
votes
2answers
28 views
RECURSIVE CTE does not use an INDEX. (Disabling seqscan forces it using an index however and it is faster)
Suppose the following relations:
match(match_id)
event(match_id, seq, gt, ...)
There are the following indexes:
match(match_id)
event(match_id, seq)
Further notes:
gt is monotonically increasing
...
0
votes
1answer
40 views
Recovery with Postgresql logical replication
I'm using Postgresql 13 on Debian 10.6 and learning about logical replication.
I've set up logical replication with one publisher and one subscriber of one table. I'm wondering what my options are for ...
1
vote
1answer
24 views
How to use NOTIFY with postgres to send previous record and new record
Say we update a record in a database table in postgres. How can I set up NOTIFY, to send a json blob of the previous version of the record and the new version of the record? I have read about NOTIFY ...
0
votes
1answer
121 views
How to conditionally create JSON array in postgres with group by clause?
I am using Postgresql 13.0 and have a table category as follows:
cat_id | cat_name | piece | weight | is_tag_stock
--------------------------------------------
4 | cat1 | 20 | 10 | ...
0
votes
1answer
213 views
PostgreSQL: pg_upgrade 9.5 to 13 failing with “connection to database failed: FATAL: database ”template1“ does not exist”
I'm trying to upgrade a 9.5 server to 13 but the pg_upgrade process is failing after the main checks. Server is running CentOS 8 on EC2 and is using the pgdg repo installs of each server.
bash-4.4$ /...
0
votes
1answer
696 views
Connect DBeaver to PostgreSQL 13
I've been using PostgreSQL 10 and 12 on Windows successfully with DBeaver in the past. Now I've set up a new development machine and installed the current PostgreSQL 13.0.1 and the current DBeaver 7.2....
0
votes
0answers
50 views
How to setup PGDATA for Postgresql13 on SUSE 15.1?
I just installed PostgreSQL 13 on a new SUSE 15-SP1 server. With postgres user, I could run initdb -D /usr/lib/postgresql13/data, and then pg_ctl start-D /usr/lib/postgresql13/data. It works fine.
...
