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
2
votes
1 answer
Speeding up a group by operation on large table
I have two large tables, tokens (100.000s of entries) and buy_orders (1.000.000s of entries) that I need to efficiently join and group by.
As seen below, tokens uniquely identified by a contract address (a 20 byte hex string) and an id (a 256 byte…
George R.
- 311
- 2
- 11
2
votes
1 answer
How to fix "relation does not exist" ERROR even when using 'IF EXISTS-THEN' pgsql block?
We are using Postgres 13.0 version with Spring-Boot .sql file as an initial step.
I need to run an UPDATE script but only if the table itself already exists.
After some effort to understand what is the correct syntax I came with the following…
Nimrod
- 1,100
- 1
- 11
- 27
2
votes
2 answers
Temp table inside a Procedure in Postgres
I am trying to create and populate a temp table inside a procedure to save some intermediate state of the data I am working with.
I have created an example code to explain what I am trying to do:
CREATE OR REPLACE PROCEDURE…
Antjes
- 70
- 1
- 8
2
votes
1 answer
how to calculate occupancy on the basis of admission and discharge dates
Suppose I have patient admission/claim wise data like the sample below. Data type of patient_id and hosp_id columns is VARCHAR
Table name…
AnilGoyal
- 25,297
- 4
- 27
- 45
2
votes
1 answer
Custom aggregate function parallel = safe produces syntax in postgres 13.3
This causes a syntax error at parallel
CREATE AGGREGATE public.first (
sfunc = public.first_agg,
basetype = anyelement,
stype = anyelement,
parallel = safe
);
Maybe because of…
Henrietta Martingale
- 771
- 8
- 27
2
votes
1 answer
Postgresql doesn't reestablish delayed replication
I'm running master & replica on PG 13.3. I decided to use delayed replication (30 minutes configured in recovery_min_apply_delay parameter). On top of that, WAL archiving is configured and working well.
When load on master is very high for a long…
Marcin Krupowicz
- 536
- 6
- 16
2
votes
3 answers
How to compress postgres database backup using barman
We are backing up 1TB+ of postgres database using barman. Barman is backing up full database daily. Every day we are dumping 1TB+ database. So it's consuming huge space.
We have following configuration in barman:(broad - level…
SAKA RAM DEWASI
- 39
- 5
2
votes
0 answers
Why do Postgres hash indexes take so long to be created?
Self-explanatory question. I have tested on a very large PostgreSQL table (+500M rows) where a B-TREE index takes around ~30 minutes to be created on a column which contains UUID or email-sized strings, while a hash index addition operation hasn't…
Z. M.
- 329
- 5
- 13
2
votes
1 answer
Creating a docker container for postgresql with laravel sail
I created a docker container using the standard "image: postgres:13", but inside the container it doesn't start postgresql because there is no cluster. What could be the problem?
Thx for answers!
My docker-compose:
version: '3'
services:
…
Archi
- 27
- 1
- 6
2
votes
4 answers
PostgreSQL 13 + Python 3.7.9 + plpython3u: 'psql: server closed the connection unexepectedly.' + 'The application has lost the database connection.'
I have added all of the details I could find, with all of the links, and there is no way to get plpython3u to work on Windows in PostgreSQL 13, it seems.
OLD, since the accepted answer shows that v3.7.0 solves it:
Better do not read through this…
questionto42
- 7,175
- 4
- 57
- 90
2
votes
4 answers
PostgreSQL COPY pipe output to gzip and then to STDOUT
The following command works well
$ psql -c "copy (select * from foo limit 3) to stdout csv header"
# output
column1,column2
val1,val2
val3,val4
val5,val6
However the following does not:
$ psql -c "copy (select * from foo limit 3) to program 'gzip…
Jivan
- 21,522
- 15
- 80
- 131
2
votes
1 answer
PostgreSQL: even read access changes data files disk leading to large incremental backups using pgbackrest
We are using pgbackrest to backup our database to Amazon S3. We do full backups once a week and an incremental backup every other day.
Size of our database is around 1TB, a full backup is around 600GB and an incremental backup is also around…
cdaller
- 63
- 6
2
votes
1 answer
Disable logging of logical replication statements in Postgres 13.1
I have a simple process that is reading logical replication messages from postgres. This process runs every second and generates a lot of messages in the postgres logs like:
2021-02-15 20:35:11.032 UTC [35] STATEMENT: SELECT * FROM…
Paul Johnson
- 1,329
- 1
- 12
- 25
2
votes
0 answers
why SQL queries not executed and tables not created spring boot crud rest api project with postgresql?
I am running a spring boot crud rest api with postgresql and spring data jpa.
The application starts fine, but I am not getting any SQL executed - no tables are created, and it doesn't show me no errors on the console.
here is my pom.xml ,…
Héla SOUISSI
- 71
- 8
2
votes
1 answer
PostgreSQL: how do I format the output of a query to show 'million' instead of zeros?
How do I format the output of a query to display a value in number of millions (i.e 'million' appended. e.g: 1 million instead of 1000000) using psql?
Example:
SELECT city, population
FROM cities
WHERE state = 'California';
Actual Output:
city…
babakahn
- 25
- 1
- 5