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
How do I get the first match from regexp_match?
I'd like to update a column with just the domain part of an email address.
update
users
set
email_base_domain = regexp_match(email, '(\w+\.\w+)$')
However, regexp_match returns a text[]. If email is example@foo.com the above sets…
Schwern
- 153,029
- 25
- 195
- 336
1
vote
1 answer
Postgres performance and query plan difference when migrating to a new version
We're upgrading a database from Postgres 9.6 to 13.4. During the upgrade, we've observing much slower query performance on the new 13.4 postgres instance.
It makes some sense that performance would be slower on a new instance - however, we're seeing…
mattnedrich
- 7,577
- 9
- 39
- 45
1
vote
1 answer
postgresql FUNCTION: If Select returns nothing then do another Select
Please help me to create postgresql FUNCTION.
I have two queries:
If the first one does not return a value
SELECT * FROM event e
WHERE e.organizer_id IN (SELECT u.id FROM "user" u WHERE u.school_id = 9)
OR e.id IN (SELECT i.event_id FROM…
Romillion
- 101
- 1
- 3
1
vote
2 answers
Streamlined solution for division on similar subqueries
I have a table in PostgreSQL 13 that looks like this (modified for the purpose of this question):
SELECT * FROM visits.visitors_log;
visitor_id | day | source
--------------+------------------------+----------
9 |…
alphaomega
- 137
- 1
- 15
1
vote
1 answer
PostgreSQL strange behaviour with array_to_json
I wanted to use array_agg to eliminate nulls before converting to JSON but the null reappears in the JSON output.
Here is a minimal example demonstrating the behaviour:
select id, array_agg(alias), array_to_json(array_agg(alias))
from (values (1,…
Marcel Joss
- 43
- 4
1
vote
2 answers
Prevent daterange column from inserting 'empty'
In my show_instance table, I have a column called dates with type daterange.
CREATE TABLE public.show_instance (
id integer NOT NULL,
show_id integer NOT NULL,
dates daterange NOT NULL
);
One of the values for dates is both inclusive…
trouble_bucket
- 184
- 3
- 12
1
vote
1 answer
How to write jsonb inside WHERE without JSON Processing Functions
This is my query and it works. I store the list of dictionaries inside my jsonb column.
SELECT
items.title
FROM
items
WHERE
jsonb_path_exists(items.types::jsonb, '$[*] ? (@.target == "discount")')
Is there any way to write this without…
Ulvi
- 965
- 12
- 31
1
vote
1 answer
ON CONFLICT DO UPDATE cannot affect row a second time, what can i do?
Look, i have this table:
CREATE TABLE voter_count
(id SERIAL, name VARCHAR NOT NULL, birthDate DATE NOT NULL, count INT NOT NULL, PRIMARY KEY(id), UNIQUE (name, birthDate))
I need to add to it several values, and there will be a duplicate of unique…
Богдан Иманкулов
- 50
- 1
- 6
1
vote
1 answer
UPDATE table column with latest related id from the same table
I have this table in PostgreSQL 13:
CREATE TABLE candles (
id serial primary key,
day integer,
minute integer,
"open" integer,
high integer,
low integer,
"close" integer,
volume integer,
id_d1 integer,
);
CREATE…
koral
- 2,807
- 3
- 37
- 65
1
vote
1 answer
Docker - Cannot delete files inside container via script
On this Postgres Docker image, I'm copying a couple of files over to configure the container.
(1) init.sh - copies to Docker entrypoint
(2) data.txt - containing sensitive information
At the end of init.sh I want to delete data.txt, but the file…
pete19
- 333
- 1
- 3
- 17
1
vote
1 answer
How to import this JSON data into a postgresql table?
i have the following JSON:
{
"ticket":{
"ticket":"61086762bb68d90001148fe9",
"timestamp":"2021-08-02T18:45:06.581-0300"
},
"serie":{
"measurement":{
"nemo":"RT",
"description":"Real Time"
},
…
Lautaro Aguilera
- 53
- 5
1
vote
0 answers
ThreadAbortException: System Error Reading the results of a Postgres function (table returning) into a EF Enumerable
I'm getting the following error trying to load the results of my Postgres function into a EF entity.
Error:
Microsoft.EntityFrameworkCore.Query: Error: An exception occurred while iterating over the results of a query for context type…
Sam Ware
- 131
- 9
1
vote
1 answer
Problem with server upgrade from 12 to 13 and 32-bit applications
I have a 64-bit Postgres server and 32-bit Windows applications. This worked perfectly until I upgraded Postgres from 12 to 13. The new server works well but applications raise an error authentication method 10 not supported on login.
I edited…
Adam Piotrowski
- 2,945
- 3
- 20
- 23
1
vote
1 answer
How select a row and count row in single query in postgresql?
I have a table in postgresql as follow:
id | chat_id | content | time | read_times
----+---------+-----------+------+-------------------------------------------------------------------------
1 | chat_10 |…
Morteza Malvandi
- 1,656
- 7
- 30
- 73
1
vote
1 answer
Postgres Docker container is broken after changing Postgres config
I am fairly new to docker and I was changing some settings in postgres.conf inside my timescaledb container running on WSL2. I promptly restarted the container to apply my changes and it appears that I have added an unnecessary comma and the…
Zach
- 35
- 1
- 7