Use this tag to indicate that your question is about PostgreSQL version 13. Questions concerning database administration should go to https://dba.stackexchange.com/
Questions tagged [postgresql-13]
368 questions
1
vote
1 answer
Aggregating values of particular key in json array - PostgreSQL
I have a table of the similar format :
chat_id | agent_details …
Tony Stark
- 511
- 2
- 15
1
vote
1 answer
Postgres Materialized View Auto Refresh
Is there any way to write a materialized view in PostgreSQL which refreshes it self automatically by specifying something while creating it?
Suvarna
- 165
- 1
- 8
1
vote
1 answer
How do i pivot PostgreSQL table?
I have table company_representatives which looks like that:
Create table script:
CREATE TABLE IF NOT EXISTS company_representatives (
_id integer NOT NULL,
name varchar(50) NOT NULL,
surname varchar(100) NOT NULL,
…
Dmytro Sokolovskyi
- 25
- 1
- 5
1
vote
1 answer
How do i get table name and column name of the table that has foreign key referencing to master table?
so if i have a table
CREATE TABLE customers(
customer_id INT GENERATED ALWAYS AS IDENTITY,
customer_name VARCHAR(255) NOT NULL,
PRIMARY KEY(customer_id)
);
CREATE TABLE contacts(
contact_id INT GENERATED ALWAYS AS IDENTITY,
…
Triaji Setiawan
- 45
- 1
- 1
- 8
1
vote
1 answer
JDBI can't bind PGInterval to PostgreSQL INTERVAL
When attempting to update or insert a table containing an INTERVAL in PostgreSQL using JDBI, I'm getting the following error:
org.jdbi.v3.core.statement.UnableToCreateStatementException: No argument factory registered for '1 years 0 mons 0 days 0…
Binary Enigma
- 58
- 1
- 8
1
vote
1 answer
How to query jarray in jsonb field type?
I have a jsonb field type in a table, it contains the JArray:
[
{
"code": "F01",
"name": "Apple"
},
{
"code": "F02",
"name": "Orange"
},
{
"code": "F03",
"name": "Banana"
}
]
I try to query based on the code and…
Don2
- 313
- 3
- 12
1
vote
1 answer
How to select using CONTAINS instead of LIKE with JPA
I'm developing small java applications to school projects with PostgreSQL.
Using the JDBC driver I could select a field using the ~ operator to act as CONTAINING instead of concatenating the filter using LIKE.
Let me give you an example:
SELECT *…
Nickolas de Luca Alberton
- 320
- 1
- 3
- 17
1
vote
1 answer
Is SELECT DISTINCT ON (col) * valid?
SELECT DISTINCT ON (some_col)
*
FROM my_table
I'm wondering if this is valid and will work as expected. Meaning, will this return all columns from my_table, based on distinct some_col? I've read the Postgres docs and don't see any reason why this…
KayakinKoder
- 3,243
- 3
- 23
- 37
1
vote
0 answers
PG Restore is not restoring the database triggers?
I'm currently trying to restore a database using PostgreSQL. The database has up to 500 tables, and some triggers, functions and views too.
I create my .backup file using this command line:
pg_dump.exe --host localhost --port 5432 --username…
Pedro Henrique Gomes
- 55
- 7
1
vote
2 answers
How to Reduce max_connections value in Postgres Cluster?
I have three node cluster.
Now, I want to reduce the max_connections field from 300 to 100. I have changed the value in both master and replica in postgresql.conf file. I have restarted my master first than the other replica nodes. everything seems…
Emon46
- 1,506
- 7
- 14
1
vote
1 answer
Postgresql sequence: lock strategy to prevent record skipping on a table queue
I have a table that acts like a queue (let's call it queue) and has a sequence from 1..N.
Some triggers inserts on this queue (the triggers are inside transactions).
Then external machines have the sequence number and asks the remote database: give…
Thiago Sayão
- 2,197
- 3
- 27
- 41
1
vote
1 answer
Age Less than or Equal to a month evaluates to False
When evaluating intervals, postgres appears to define a month as 30 days exactly, even when there are 31 days in a month:
select age('2021-03-31 23:59:59.999', '2021-03-01'::date)
Returns: 30 days 23:59:59.999
Which in the case of March is Less…
ThatGuyTy
- 59
- 4
1
vote
2 answers
How to use text input as column name(s) in a Postgres function?
I'm working with Postgres and PostGIS. Trying to write a function that that selects specific columns according to the given argument.
I'm using a WITH statement to create the result table before converting it to bytea to return.
The part I need help…
Yoad
- 35
- 6
1
vote
2 answers
Generate a JSON array of values for each row
Assuming the following CTE:
with mytable as (
select column1 as foo, column2 as bar, column3 as baz
from (values
('a', 'b', 1),
('c', 'd', 2)
) v
)
Using array_agg() ouputs an array of values:
select
array_agg(v)
from…
Jivan
- 21,522
- 15
- 80
- 131
1
vote
2 answers
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 it goes slow. This is a report so it will be…
Pyton
- 1,291
- 8
- 19