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
0
votes
0 answers
postgres ltree gist index kept growing in size
I have a gist index for a ltree field, declared like below:
path | ltree | | |
I realized this index grew quickly to 185GB, even though I didn't insert crazy amount of content. Note the size and…
Behlül
- 3,412
- 2
- 29
- 46
0
votes
1 answer
Why is my equal not working on 2 identical String
I'm using postgresql 13 and I'm trying to fetch data from a table based on one of its column.
Said table is defined as follow :
create table my_table (
my_table_id int8 not null,
value varchar(255) not null,
another_table_id int8 not…
L. Malegue
- 21
- 4
0
votes
1 answer
Set up mTLS between Jira Service Management and PostgreSQL 13.5
I am trying to set up the mTLS connection between JSM and PostgreSQL.
On the database side, I have turned on the SSL and in pg_hba.conf I added the configuration below to tell the client to use a cert for connection:
hostssl all all 0.0.0.0/0…
joker57
- 110
- 7
0
votes
4 answers
SQL query to get last record based on table relation
i have 3 tables boxes, stones and papers; each box is related to a paper through a stone so my goal is to get the last box for each paper (boxes can share papers). have tried an ActiveRecord way to go in rails but could not use an aggregate function…
jupcan
- 436
- 3
- 7
- 17
0
votes
0 answers
Create a reverse relationship to two models
I have a model User. For performance and other reasons I have to split this Model and its table into two, UserA and UserB. I decided to use materialized views (with the help of django-pgviews). Now what IS easy is to query the data of UserA which is…
Tim
- 929
- 1
- 14
- 28
0
votes
3 answers
how to delete data array on jsonb postgresql
how to update array data in jsonb column on database postgresql?
for example on table table1 i have column attribute that have value like this:
id
attribute
1
[{"task_customs": ["a", "b", "c"]}]
2
[{"task_customs": ["d", "e",…
Jazuly
- 1,374
- 5
- 20
- 43
0
votes
2 answers
No overlapping data
Being a new user of postgres, I have created a database in postgres 13. It contains tables
including 4 Fields
ID integer (PK)
HoleID varchar(20)
From numeric NOT NULL CHECK (From>=0)
To numeric
Cat varchar (20)
I want to create a constraint that…
Lamethode
- 3
- 2
0
votes
1 answer
Trigger function to refresh Postgres materialized view and capture refresh end time?
I am trying to build a summary table in our Postgres database that contains information about when various materialized views were refreshed. I would also like this table to trigger the actual refreshes.
The desired format for the table is as below,…
mikstravaganza
- 27
- 8
0
votes
1 answer
Get min and max value from a SQL JSON object to update fields?
I have a table in Postgres 13 that looks something like:
Column | Type |
----------------
id bigint
ts_begin timestamp with time zone
ts_end timestamp with time zone
data jsonb not null '{}'::jsonb
Unfortunately the data is an array of…
GSP
- 3,763
- 3
- 32
- 54
0
votes
0 answers
Case sensitivity of PostgreSQL linked tables in MS Access
I have an MS Access database of fishes. I also have a search form in MS Access where you can search for any species of fish, and the results will be displayed in a separate form. My database is getting bigger and I decided to covert it to postgresql…
0
votes
1 answer
How can I merge rows of a table that records history of changes in order to obtain what a row looked like at a specific moment?
I have the following 2 SELECTs:
SELECT * FROM public.app_user WHERE id = 'e31b55bf';
+--------+----+-----------+-----+-----+--------------------------+
|id |name|email |role |bio |created_at …
BlueSialia
- 101
- 1
- 13
0
votes
1 answer
How to use trunc() function in postgresql
I'm a little confused about using trunc() function in postgresql.
As far as I know, if I want to trunc date, I need to use the date_trunc() function in posgresql.
It looks like this: select date_trunc('month',now()).
But I found that there's a…
Xie Steven
- 8,544
- 1
- 9
- 23
0
votes
1 answer
dense_rank with setval on serial column (query in PSQL 13)
I'm trying to insert data in a table named test defined by the following schema:
CREATE TABLE test (s INTEGER, p INTEGER, o INTEGER, id SERIAL NOT NULL)
Then I run this query to set the start value for the column id: SELECT setval('test_id_seq',…
Nelly Barret
- 144
- 1
- 17
0
votes
1 answer
Multi column order by kills query performance even when the time range does not contain any records
I have a fairly small table of 26 million records.
CREATE TABLE t1
(
cam varchar(100) NOT NULL,
updatedat timestamp,
objid varchar(40) NOT NULL,
image varchar(100) NOT NULL,
reader varchar(60) …
mohit
- 4,968
- 1
- 22
- 39
0
votes
0 answers
PostgreSQL: Problem with query syntax for materialized view
I'm trying to generate a materialized view, where I want to calculate the mean value of a specific interval in a column for several columns.
CREATE TABLE decade_2000_cg_data_view AS
SELECT tdl.id, tdl.grid_id, tdl.name, depth1.cg, depth2.cg,…
Lila
- 1
- 1