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
1 answer
How to create a Postgres table based on data defined in points from another table?
I have a table with data
id | location | data |
-----------------------
0 | point 0,1 | "Dachshund"
1 | point 0,0 | "Dog Breeds"
2 | point 1,0 | "Quantity"
3 | point 0,1 | "Rhodesian"
4 | point 1,1 | "2"
5 | point 1,2 | "1"
Am looking to…
user1805218
- 15
- 4
0
votes
2 answers
How can I replace this correlated subquery within a function call?
Given the following tables
buckets
metric_id|start_date |bucket
------------------------------------
a |2019-12-05 00:00:00|1
a |2019-12-06 00:00:00|2
b |2021-10-31 00:00:00|1
b |2021-11-01…
Dan
- 3
- 1
0
votes
2 answers
UPDATE column with previous value
Imagine a sample table
CREATE TABLE mytable (myid integer, myval double precision);
I want to update myval to the previous non-zero value, ordered by myid, if myval is equal to 0.
For that to work properly, the query has to start updating from the…
Héctor
- 399
- 3
- 16
0
votes
1 answer
PostgreSQL Log Rotation Size Reached File Limit
I have configured the following settings in postgreSQL 13.
logging_collector = on
log_rotation_size='100MB'
log_truncate_on_rotation = on
log_filename ='postgresql-%Y-%m-%d.log'
My issue is when the log file size reached 100MB, it will…
xxestter
- 441
- 7
- 19
0
votes
1 answer
Postgres FDW join query on local and foreign table is slow with ORDER BY and JSONB data
I have two tables the local table debtors and the foreign table debtor_registry. I'm using PostgreSQL v13.
My problem here is whenever I try the following query, It takes 14secs to get the 1000 records.
SELECT
debtors.id,
debtors.name,
…
Shift 'n Tab
- 8,808
- 12
- 73
- 117
0
votes
3 answers
How can I update the time in a column without affecting the date?
In my database I have this column of type DATE with a lot of dates stored in it. I converted it to DATETIME using timestamptz. But the time is 00:00:00.
I want to change all the time to be 09:00:00 i tried a lot with update and set but I just get…
H. Motsu
- 87
- 5
- 13
0
votes
0 answers
psql could not connect to server after complete reinstallation [Ubuntu 21.10 / PostgreSQL 13]
After experiencing issues related to the existence of two different PostgreSQL versions installed on the same machine, I decided to remove --purge postgresql* and reinstall postgresql-13. Now the output of psql --version is psql (PostgreSQL) 13.4…
Mapper
- 133
- 6
0
votes
1 answer
I'm trying to cross database with insert query in plpgsql, please help me
CREATE OR REPLACE FUNCTION "public"."cross_insert"("p_name" varchar, "p_detail" varchar)
RETURNS SETOF "pg_catalog"."varchar" AS $BODY$
BEGIN
SELECT * FROM public.dblink(
'
host=10.10.10.53
port=5432
…
0
votes
1 answer
Enumerate JSON Array Elements in Postgresql
Is there a way to query jsonb array items with their order enumerated in postgresql?
For example:
SELECT * FROM jsonb_array_elements('[1,2,[3,4]]'::jsonb)
Results:
value
1
2
[3, 4]
I would like these results:
key value
0 1
1 2
2 [3,…
Paul
- 3,634
- 1
- 18
- 23
0
votes
1 answer
Use DISTINCT to display unique items
I have 2 tables which I would like to query and display data differences:
CREATE TABLE order_splits_config (
id INT,
pair_id INT
);
CREATE TABLE active_pairs (
id INT,
pair …
user1285928
- 1,328
- 29
- 98
- 147
0
votes
0 answers
Remote Postgresql is very slow
I run .py files using Django ORM, that connected to Postgresql server on another server.
Both servers working on Ubuntu 20.04
when i run the same file it takes the following time:
2-3 seconds on server with postgresql
8-12 seconds on another…
Фёдор Яковлев
- 9
- 2
0
votes
1 answer
Can pg_dump set a table's sequence while also excluding its data?
I'm running pg_dump -F custom for database backups, with --exclude-table-data for a very large audit table. I'm then exporting that table data in a separate dump file. It isn't referentially integral with the main dump.
As part of my restore…
spume
- 1,704
- 1
- 14
- 19
0
votes
1 answer
Reorganize ID of tables in postgreSQL so that the ID starts again at 1?
Hello i am currently try different data automation processes with python and postgreSQL. I automated the cleaning and upload of a dataset with 40.000 data emtries into my Database. Due to some flaws in my process i had to truncate some tables or…
black_hole_sun
- 908
- 11
- 41
0
votes
0 answers
How to add "55" in the beginning of a string, in case there isn't already?
I have a column with phone numbers that are in string format due to the fact that there might be special characters in the middle. I already cleaned it up, and now I would like to add "55" to the beginning of the string in case there isn't…
Erick
- 103
- 6
0
votes
1 answer
Optimize updating first, last, and second to last ranked value
I need to cache the first, last, and second to last time a thing happened per user. The history table I'm querying has hundreds of millions of rows (we're caching so we can truncate it), and the table I'm updating has dozens of millions.
Currently…
Schwern
- 153,029
- 25
- 195
- 336