Specifically for PostgreSQL version 13
Questions tagged [postgresql-13]
146 questions
23
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
- 429
- 1
- 6
- 8
1
vote
0 answers
How to optimize structure of the tables?
Database
PostgreSQL 13.10
Structure
Constraints
name is "filter_test_group_id_test_group_id_test_name_id_filter_key_uniq"
UNIQUE(id_test_group, id_test_name, id_filter, key)
Indexes
name is "filter_test_group_index_id_test"
CREATE INDEX…
Oleg Karoza
- 11
- 1
1
vote
0 answers
FATAL: the database system is in recovery mode
I am getting the below errors every 2 or 3 days(when high usage of database) and also postmaster.pid file is changed automatically.
my Database version is postgresql 13
Could not get JDBC Connection; nested exception is…
Manoj
- 11
- 2
1
vote
1 answer
Got message: FATAL: expected SASL response, got message type 88
I have a strange situation. A small program working fine till these days (about half a year). The program (NodeJS) is creating a local connection to DB:
1st: creating new connection to local db.
2nd: client by websocket connected to this program,…
noszone
- 111
- 3
1
vote
0 answers
pg_stat_statements vs slow query
Now I want to query the PostgreSQL 13 slow query that take more than 1s. Some told that should open the PostgreSQL slow query log, some recommand to use pg_stat_statements, which one should I choose? what is the performance effect with each one?
Dolphin
- 775
- 4
- 18
- 34
1
vote
1 answer
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 do update both 'age' and 'status' at…
Don2
- 429
- 1
- 6
- 8
0
votes
1 answer
Postgresql showing different behavior on two environment having exactly same configuration
We have two environment staging and production. For Database service we are using AWS RDS(postgresql).
SELECT *
FROM addressfabric_v2_2022_7
WHERE ST_Intersects(ST_GeomFromText('POLYGON((-75.7891846 45.2530176,-75.2783203 45.2568822,-74.9981689…
0
votes
1 answer
is it possible to replace the window function with custom function
I have a pagination sql with PostgreSQL 13 like this:
select
*,
COUNT(*) OVER ()
from
(
select
"article"."id",
"article"."user_id",
"article"."title",
"article"."author",
"article"."guid",
…
Dolphin
- 775
- 4
- 18
- 34
0
votes
0 answers
postgres - why does ALTER COLUMN run out of space?
Why does this POSTGRES command run out of space?.....
ALTER TABLE table1 ALTER column field1 TYPE VARCHAR(250);
field1 is currently VARCHAR(200), I need to increase it to VARCHAR(250) but after running for 20 minutes it reports running low on disc…
ConanTheGerbil
- 1,155
- 4
- 26
- 43
0
votes
1 answer
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 query to search omni_queue_pid = 5
Select *
from ws a,
…
Don2
- 429
- 1
- 6
- 8