Questions tagged [postgresql-13]

Specifically for PostgreSQL version 13

Filter by
Sorted by
Tagged with
0
votes
1answer
26 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
16 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
83 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
112 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
297 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
106 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
91 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
30 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
175 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
196 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
497 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
225 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
40 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
440 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
68 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
66 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
245 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
290 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
581 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
137 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 ...