Questions tagged [postgresql-13]
Specifically for PostgreSQL version 13
54
questions
0
votes
1answer
34 views
Pass a variable with INSERT, UPDATE, DELETE statements
I am looking for the ways where could be possible to send some data with psql query statement. For example:
insert into mytable (id,name,site_id) values (default,'test',1);
Real structure of mytable:
...
1
vote
0answers
17 views
max_replication_slots in RDS replica
We have enabled the on-prem PostgreSQL (Source) to AWS-RDS (Target) replication by following the official guide and which is worked well. Our RDS cluster has multiple replica's and am trying the ...
0
votes
0answers
50 views
UPDATE column with previous value
Imagine a sample table
CREATE TABLE mytable (myid integer, myval double precision);
I want to update myval to the previous value, ordered by myid, if a condition is met (that myval is equal to 0).
...
1
vote
1answer
44 views
Postgres 13 Sort Performance
Sort performance on one of our query is very bad that it takes up to 14 seconds to run the query.
Here is the Query:
SELECT "stock_quant".id
FROM "stock_quant"
WHERE ((((("...
0
votes
1answer
25 views
Postgres: Generate 1-per-parent id sequences for child ids with no holes
I want a – can I call it a sub-sequence ? – to generate identity for child lines. A sequence per parent entity, starting at 1 with no holes. That is, each parent's childrens Ids should start at 1 and ...
1
vote
1answer
27 views
Postgres creating 'histogram_bounds' statistics on JSONB column
I have two database tables (A and B), each containing roughly 1.6m rows, and each having the following structure:
CREATE TABLE tablename (
identifier text NOT NULL,
geometry geometry(GeometryZ,...
0
votes
0answers
84 views
Postgres is extremely slow through localhost, for any command
Postgres 13 in my Windows 10 machine is extremely slow. Even with Postgres shell, every single command needs 22 seconds to be executed. Either it is a single query in a nearly zero-rows DB, or a ...
0
votes
1answer
113 views
Occasional incorrect values for generated columns in PostgreSQL 13
I have a PostgreSQL 13 server running in Google Cloud and one of our tables has a few generated columns and occasionally I find rows where the value saved in the generated column isn't up-to-date.
For ...
0
votes
1answer
318 views
ERROR: syntax error at or near "END" [closed]
I am trying to play around with trigger and found some samples online which I pretty much copied before except changed table name and row counts. But somehow I kept on getting syntax error and ...
0
votes
1answer
41 views
data model limit rows per user
This is regarding to postgres that I was asked to create data model which has a relationship with user.
For example a new data model called Comment M:1 User, but while creating the data model is it ...
1
vote
1answer
109 views
Why is my spatial query slower in Postgres 13 than in Postgres 11?
Postgres versions
PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
1
vote
1answer
65 views
SELECT rows from table one by one, comparing one column values with values in an array
I have an array of timestamps in a PLPGSQL code block: timestamp_array.
For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table)...
0
votes
1answer
94 views
POSTGRESQL Subtract to each row the previous row
I'm writting a function which at some step I have query result like the following one:
timestamp
Consumed Fuel
Processed Volume
23-09-2021 5:00:00
3000000
982135
23-09-2021 6:00:00
3000010
982136
...
0
votes
1answer
31 views
Selecting related data without selecting foreign key from subquery [closed]
cities
id
name
county_id
1
City1
1
2
City2
1
3
City3
2
4
City4
2
counties
id
name
1
County1
2
County2
Hello,
I would like to select all city names from related county by enetring a city name....
2
votes
1answer
176 views
postgresql materialized view ERROR: a negative number raised to a non-integer power yields a complex result
I have complex view with calculations but one line makes issue
ROUND((((latest_revenue::numeric/ oper_reve_3y_old::numeric) ^ (1/3::numeric)) -1::numeric)*100::numeric)
When I execute the query, ...
0
votes
0answers
42 views
How to view/track queries in PostgreSQL
How can I see, in real time, queries being executed on or arriving to PostgreSQL server, ideally filtered by a database?
Watching in log files using text editor is not very comfortable :)
0
votes
0answers
21 views
postgresql 13 sync replication - does primary complete flushing to local before sending wal to slaves?
In a pgpool-II 4.2 and postgresql 13 environment:
3 servers (each hosting a pgpool and a postgresql)
I understand we can set the below parameter for different durability's.
synchronous_commit = off, ...
-1
votes
4answers
84 views
Is there some way to automatically "follow"/pre-determine the release date for stable/GA PostgreSQL 14?
I currently have PostgreSQL 12.x installed. I'm putting off my Windows re-installation for the PostgreSQL 14 release, because I want to skip the entire generation 13 for superstitious reasons. Yes, ...
0
votes
1answer
12 views
psql on WSL: Not listing all databases for owner postgres
I'm trying to setup Postgres on WSL (bash) to be able to use pg_dump (as an absolute beginner).
I've followed the steps from here twice, once after purging the entire installation.
At step 3: I used
...
1
vote
1answer
37 views
PostgreSQL chooses a wrong order of Hash Join
EXPLAIN shows that PostgreSQL chooses a bad plan for hash join. it should have first iterate by tags_name_value_height2 in the table tags and use it to find corresponding transactions.id, but instead ...
0
votes
0answers
201 views
Upgrade Postgres database from 10 to 13 running in Docker
I have a Postgres database running in a docker container (https://hub.docker.com/_/postgres).
The docker image is postgres:10.0 and I would like to upgrade to postgres:13.3.
If I just change the image ...
3
votes
1answer
515 views
RDS Postgres oldest replication lag increases during inactivity even though Fivetran connector keeps synchronizing
I'm in the process of setting-up a datawarehouse using Fivetran as the ETL layer. One of the source databases is a AWS RDS Postgres instance.
I've configured the Postgres instance to perform Logical ...
0
votes
1answer
23 views
how to update more than 1 properties of jsonb field?
how to update the age and the status at once,
I have a jsonb field type, I need to update 2 properties (age and status).
I am able to update only either age and status, using the command below, how to ...
-1
votes
1answer
228 views
How to filter and get specific keys from JSON objects array
I have a JSONB field containing a value in this structure:
[
{
"type": "small",
"name": "Josh",
"greeting": "Hello ...
1
vote
0answers
43 views
How to prioritize indices in Postgres cache?
The server config:
RAM = 16 GB
#CPUs = 6
The Postgres config:
There is only 1 Postgres instance. I.e. no partioning, no clustering, ...
shared_buffers = 5GB
work_mem = 10MB
maintenance_work_mem = ...
0
votes
0answers
21 views
Postgres cast MI:SS formatted string to "interval minute to second" type
I have a data source with the time information being formatted like this: 'MI:SS'.
I am trying to coerce that to a native postgres INTERVAL or TIME type so I can leverage the equivalent operators.
I ...
0
votes
1answer
23 views
how to update a property value of a jsonb field?
I have a jsonb type named attr field that contains the following:
{
"pid": 1,
"name": "john",
"is_default": true
}
how to change is_default to false?
I ...
0
votes
1answer
42 views
Postgres metadata: get a list of all MATERIALIZED VIEW columns, with data type, INCLUDING array columns
I need to get metadata about all MATERIALIZED VIEW columns, including their data type. I can do this with this query:
SELECT
trim(leading '_' from pg_type.typname) as data_type,
pg_namespace....
0
votes
1answer
35 views
Pivot email rows into multiple columns per id
I have a table "emails":
id
email
1
a@a.a
2
g@g.g
2
d@d.d
4
c@c.c
2
k@k.k
4
o@o.o
I need this result:
id
email
email
email
1
a@a.a
2
g@g.g
d@d.d
k@k.k
4
c@c.c
o@o.o
the number ...
0
votes
0answers
447 views
PostgreSQL-13 The connection pool has been exhausted
Hope all of you are doing well!.
Our ASP NetCore 3.1 Web API runs on CentOS 8 with Postgresql-13, currently, we are facing the following error:
Npgsql.NpgsqlException (0x80004005): The connection pool ...
3
votes
0answers
192 views
Postgres - limit window function evaluation to once per partition
I have a table of daily stock quotes with ~40M rows and I'm trying to run a query that spits out the most recent date and the number of stocks that made a new one year high on that date. For example:
...
0
votes
1answer
17 views
How to query JSONB field type
I have a jsonb field named 'queues' it contains the following:
{
"call_queue_pid": [
1,
2,
3
],
"omni_queue_pid": [
4,
5,
6
]
}
I try the below ...
0
votes
1answer
70 views
How to use password with special chars in postgressql .pgpass file
How to use password with special chars like repl#stg$12 in postgressql .pgpass file?
db1:5433:replication:repl:repl#stg$12
I added entry like this in .pgpass but it's prompting for password when I ...
0
votes
1answer
58 views
Define recursive composite types
Is it possible to define recursive composite types in Postgres 13?
create type "t" as (
"a" int,
"b" t
);
I am aware that I can use a table with a parent-child ...
2
votes
2answers
92 views
Run calculation not working in sub query, "does not exist"
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
I have a query that provides the count of 2 things over 2 time periods:
time period ...
-1
votes
1answer
30 views
Why PG 13 does not allow deferring statement trigger constraints?
I noticed in PG 13 docs that only row constraint triggers can be deferrable.
Why not statement constraint triggers?
0
votes
0answers
32 views
PostgreSQL 13 - Improve huge table data aggregation
I have a huge database (current size is ~900GB and new data still comes) partitioned by Year_month and subpartition by currency.
The problem is when I try to fetch aggregation from the whole partition ...
2
votes
2answers
131 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
57 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
67 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
247 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
58 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
299 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
29 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
99 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
588 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
14 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
140 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
116 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
48 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 ...
