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
Invalid reference to FROM-clause entry for table "t1" in postgres query
I'm trying to perform a query like this...
select * from table1 as t1
left join (
select * from table2 as t2
where t2.id = t1.t2_id
) as tt2
where tt2.value = 'SOME VALUE'
& I'm getting error like this...
ERROR: invalid reference to…
Aninda Sarker
- 32
- 6
1
vote
0 answers
ERROR: there is no unique constraint matching given keys for referenced table "mail_message" Odoo Postgres
When I'm updating the base app I'm getting this error - ERROR: there is no unique constraint matching given keys for referenced table "mail_message".
for that i have execute this query too,
Query : ALTER TABLE "mail_tracking_value" ADD FOREIGN KEY…
Dipen Shah
- 2,396
- 2
- 9
- 21
1
vote
1 answer
SQL Error: 0, SQLState: 42703 with message "The column name str_id was not found in this ResultSet"
this is my first question ever in StackOverflow and as suggested, I have looked at other similar questions and attempted to use their responses for my problem. So far, no luck.
The situation is as follows:
I have a custom query in…
Usman Lakhani
- 11
- 1
- 3
1
vote
1 answer
PostgreSQL — Select column1 where MIN(column2)
NOTE: I WANT TO AVOID DISTINCT ON FOR PERFORMANCE REASONS.
NOTE 2: I WAS WRONG. USING PROPER INDEXES THE QUERY WORKED AWESOME THANKS TO @Gordon Linoff!
Having the following structure:
| id | image_url | sort | t1_id…
Zeswen
- 261
- 4
- 12
1
vote
1 answer
Postgres GENERATED AS IDENTITY column nullability
I want to create a table, which contains a nullable column having GENERATED BY DEFAULT AS IDENTITY option, therefore I run the following query:
CREATE TABLE my_table (
generated INTEGER NULL GENERATED BY DEFAULT AS IDENTITY,
data TEXT NOT…
Deedee Megadoodoo
- 843
- 8
- 20
1
vote
1 answer
Create custom hash operator for Postgres partitioning
I want to create a custom hash function that would be used by Postgres (version 13.2) to distribute rows across partitions. Problem is that with the current solution Postgres does not use partition pruning.
Here is my code:
-- dummy hash…
Michał Albrycht
- 322
- 3
- 13
1
vote
1 answer
How to retrieve the rows between two dates in PostgreSQL?
How to retrieve the row between two dates in PostgreSQL?
ID START_DATE END_DATE
1 02/03/2020 02/03/2021
2 05/04/2020 NULL
In the above example, end_date column is NULLABLE.
I want to retrieve the row…
Thirumal
- 8,280
- 11
- 53
- 103
1
vote
1 answer
My postgresql docker container is using all the ram and acting weird
I am using a docker compose on a 2GO digitalOcean server to deploy my app, but I noticed that the postgresql container was using all the ram available for him !
This is not normal and I wanted to know how to fix this problem..?
So I go in the logs…
Carlos
- 119
- 10
1
vote
2 answers
PostgreSQL: `NEW.` returning `NULL` when `AFTER TRIGGER` is fired
Description of the problem:
Hi everyone, i have this table:
CREATE TABLE COMPORDINE (
CodProdotto CHAR(5) NOT NULL CHECK(CodProdotto ~* '^[0-9]+$'),
CodOrdine CHAR(5) CHECK(CodOrdine ~* '^[0-9]+$'),
Prezzo REAL NOT NULL, CHECK(Prezzo >=…
noodles
- 13
- 4
1
vote
1 answer
How to create a Postgres Identity database for use with ASP.NET Core with dotnet-ef?
After much research and trial and error here are the steps that I have found that works:
Environment:
Windows 10
Postgres 13
ASP.NET Core 5
Steps to create:
Create the Identity database on Postgress 13.
GRANT ALL ON DATABASE dbname TO…
WCS
- 69
- 1
- 15
1
vote
1 answer
Predict partition number for Postgres hash partitioning
I'm writting an app which uses partitions in Postgres DB. This is will be send to customers and run on their server. This implies that I have to be prepared for many different scenarios.
Lets start with simple table schema:
CREATE TABLE dir (
id…
Michał Albrycht
- 322
- 3
- 13
1
vote
2 answers
Test ARRAY whether it contains ALL or NO elements of given ARRAY
Suppose we have an array:
ARRAY[1,2,3]
With the operator <@ I can query whether the left-operand is a sub-array of the right-operand:
ARRAY[1, 2] <@ ARRAY[1,2,3]
The above works greatly but now let's take the following case:
ARRAY[1, 2] <@…
Antonio L.
- 41
- 7
1
vote
1 answer
Postgres query is very slow for 1 billion rows
I have around 1 billion rows and 6 columns (id, col1, col2..col5) in my Postgres database table. I have id as my PRIMARY_KEY. I am using this as a read-only database, and every time I only need to retrieve/read 10000 rows based on a list of 10000…
Sushant
- 71
- 1
- 9
1
vote
1 answer
Returning Table with null value causes error
I have a simple function that has optional parameters. When I leave out a parameter, which should just default to null, I get an error that it is not an integer.
Here is the function:
CREATE FUNCTION rewrite(_postid integer DEFAULT NULL::integer,
…
volume one
- 6,800
- 13
- 67
- 146
1
vote
2 answers
How to return result of dynamic SELECT inside a function in PostgreSQL?
A very similar question here but not quite the same as this one.
I have a function that uses IF statements to determine what type of SELECT query to return.
How can I declare what a CREATE FUNCTION statment should return when I will never know the…
volume one
- 6,800
- 13
- 67
- 146