Specifically for PostgreSQL version 13
Questions tagged [postgresql-13]
153 questions
32
votes
2 answers
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 try to run below, but no luck.
update attr set attr ->> 'is_default' = false where…
Don2
- 549
- 1
- 7
- 9
12
votes
3 answers
Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?
In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do.
A simplified example looks like this:
WITH…
cis
- 499
- 6
- 19
11
votes
2 answers
Why is the serial primary key incrementing despite a using "On Conflict Do Nothing?
I have the below table in PostgreSQL 13:
table name: newtable
field type
----- ----
Seq bigserial
code varchar
Seq is the primary key (auto-increment)
Code is a unique key index
Insert Into newtable (Code)…
Don2
- 549
- 1
- 7
- 9
6
votes
1 answer
How to get slow query on PostgreSQL?
Basically I want to log those query which is taking more than 300ms to execute. I don't want to log those query which is less than 300ms. But at PostgreSQL showing all type of log whether it is below 300ms or above 300ms.
I configured below…
Sheikh Wasiu Al Hasib
- 273
- 1
- 2
- 14
6
votes
1 answer
Performance penalty for COUNT(1) OVER (PARTITION BY NULL)
In my application server, I would like to paginate a dataset using LIMIT and OFFSET, and additionally return the total count of the dataset to the user.
Instead of making two remote calls to the database:
select count(1) as total_count from…
Matthew Moisen
- 207
- 4
- 12
6
votes
1 answer
PostgreSQL – Can queries that require all partitions be blocked or rejected?
We are planning to partition a dataset of ~500million rows using list/range partitioning in PostgreSQL 13.4 on RDS. Our read access patterns should only require a very small number of partitions to be accessed for each query. We would like to…
Barry Hurley
- 161
- 2
6
votes
1 answer
How to utilize partition pruning with subqueries or joins?
I have a partition table...
CREATE TABLE erco.rtprices
(
scedtime timestamp with time zone NOT NULL,
node_id integer NOT NULL,
lmp numeric(12,6),
CONSTRAINT rtprices_pkey PRIMARY KEY (scedtime, node_id)
) PARTITION BY LIST…
Dean MacGregor
- 739
- 2
- 8
- 21
6
votes
1 answer
PostgreSQL predicate not pushed down (through join conditions)
Consider the following data model in a PostgreSQL v13 system;
Here, parent table dim contains a small set of reference data, and child table fact contains a much higher volume of records. A typical use case for these data sets would be to query all…
Michiel T
- 161
- 5
5
votes
1 answer
postgresql matching or converting utf-8 variant strings
Postgres 13
I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie vs life ) within postgresql.
I am running into issues matching variant characters, consider
-- This works…
unomi
- 153
- 4
4
votes
1 answer
Is it safe to wipe out pgsql_tmp while the database is not running?
I've had a couple of instances recently of being forced to shut down my PostgreSQL 13.10 database uncleanly, and it taking a few hours to come back up afterwards.
In both cases, strace revealed that what the process was doing was stat-ing (maybe…
Ben Millwood
- 143
- 4
4
votes
2 answers
Postgres - can't drop constraint because objects depend on it even though it is duplicate
I have a situation that I am running into after upgrading my Postgres from 10.7 to 13.6. I have flyway scripts that date over several years that build up the history of the database. The changes to the schema shuffled around PKs, and while doing so…
mvd
- 151
- 1
- 1
- 4
3
votes
1 answer
How to "merge" rows along with their foreign many-to-many relations without violating unique constraints?
Fiddle: https://dbfiddle.uk/-JLFuIrN
Table
CREATE TABLE files (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
CREATE TABLE folders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text
);
CREATE TABLE…
Biller Builder
- 288
- 1
- 12
3
votes
2 answers
trigram indexing for LIKE ALL filters
I find myself looking for a way to dynamically restrict search results so that all searches must be present:
["A TE","SOME"] search array:
"MS A TEXT B SOMETHING" --> matches
"MS A B SOMETHING" --> doesnt match
so I'm using a GIN index and LIKE…
LittleFoxyFox
- 53
- 5
3
votes
3 answers
count(*) queries are too slow even with an index only scan
We currently have a PostgreSQL table that stores audit logs similar to the following:
id
portal_id
created_at
action
caused_by_id
caused_by_type
1
1
2019-09-19 09:39:48.827924
user-logged-in
1
User
1
1
2019-09-19…
greekblog
- 139
- 1
- 3
3
votes
1 answer
Postgresql 14 Performance regression versus PG13
I migrated a database from Postgresql 13 to Postgresql 14 recently, and I saw a x2 execution time in a specific case which I can't understand.
The issue has been seen on a Postgres on Linux, and I reproduced the following example on macOS with…
whisust
- 31
- 5