Use this tag to indicate that your question is about PostgreSQL version 15. Questions concerning database administration should go to https://dba.stackexchange.com/
Questions tagged [postgresql-15]
112 questions
37
votes
2 answers
Postgres 15. permission denied for schema public
Can't create tables in public schema as non-superuser
postgres - super user.
What I've done:
ALTER SCHEMA public owner to postgres;
CREATE USER admin WITH PASSWORD 'my-password';
GRANT USAGE, CREATE ON SCHEMA public TO postgres;
GRANT…
Merkalov Anton
- 473
- 1
- 2
- 6
3
votes
2 answers
Why My Unique Index Doesn't Work in Postgresql?
I created a table for to store day off data of users. After, set a unique index for multi column but when exec insert sql, it duplicates datas again
Here's my DDL:
create table day_off_movements
(
id bigserial
primary…
icsarisakal
- 193
- 7
3
votes
0 answers
TimescaleDB continuous aggregation custom time_bucket function
We have a device for temperature collecting, it is configured to collect the temperature of the refrigerator box each second in the buffer (but can collect even fewer periods 10ms, 100ms) and at the end of each minute it sends an average value for…
Sanprof
- 369
- 1
- 6
- 17
3
votes
0 answers
template database "template1" has a collation version, but no actual collation version could be determined
I just upgraded my Postgres from 12 to 15 and I keep getting this error:
template database "template1" has a collation version, but no actual collation version could be determined
Here's the template1 collation info in Postgres 15:
Any ideas on how…
buddy2891
- 157
- 10
2
votes
2 answers
PostgreSQL is icu collation 'de-u-kn-true-ks-level2' deterministic?
For my database I want natural ('A2'<'A12'), case-insensitive sorting, so for testing I created a new collation with
CREATE COLLATION tomscollation (provider = icu, locale = 'de-u-kn-true-ks-level2');
My end goal is to use such a collation as the…
Tom
- 503
- 2
- 13
2
votes
1 answer
Entity Framework Core auto value generation of 'last modified' column with data annotations and Postgresql
I'm using .NET 7 with Entity Framework Core and postgresql (npgsql).
I want to add autogenerated CreateOn and ModifiedOn columns to all my tables. I want them to update to the current datetime when a row is inserted or updated respectively.
So my…
Daniel Goldberg
- 101
- 6
2
votes
2 answers
Why is PostgreSQL 1 year interval <> 365 days interval?
Hello PostgreSQL experts.
I'm trying to understand why these 2 Boolean expressions return different results.
The first returns TRUE whereas the second returns FALSE.
SELECT CAST('2019-01-01T12:00:00' AS TIMESTAMP) - CAST('2018-01-01T13:00:00' AS…
SQLRaptor
- 671
- 4
- 14
2
votes
1 answer
Spring Data queryForStream: how can it run out of Heap Space?
I have a Spring Boot application that reads from a database table with potentially millions of rows and thus uses the queryForStream method from Spring Data. This is the code:
Stream result = jdbcTemplate.queryForStream("select * from…
ulim
- 83
- 10
2
votes
3 answers
No attribute "execute" error on connecting to PostgreSQL
I have my postgres installed on PC1 and I am connecting to the database using PC2. I have modified the settings so that postgres on PC1 is accessible to local network.
On PC2 I am doing the following:
import pandas as pd, pyodbc
from sqlalchemy…
Zanam
- 4,607
- 13
- 67
- 143
2
votes
0 answers
Issue when accessing PostgreSQL 15 in pgAdmin4
Getting the following error when trying to access PostgreSQL 15 in pgAdmin 4 for the first time. Is this because I've messed up the passwords?
Error message: connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused Is…
James Newman
- 21
- 2
1
vote
6 answers
SQL: efficiently select multiple rows groups from the same table
Consider the following table:
create table user_tasks
(
id bigserial primary key,
type varchar(255) not null,
...
);
I need to select 20 rows per page, but equally spread by…
Majesty
- 2,097
- 5
- 24
- 55
1
vote
1 answer
Select all rows that fully intersect with the input array?
Consider the following table
id int
lang_codes text[]
1
{eng,fre}
2
{eng}
I need to select all the rows from the table that intersect with the input array, but only when all the items in the lang_codes are present in the input array,…
Majesty
- 2,097
- 5
- 24
- 55
1
vote
0 answers
Create table and immediately use it in function
I want to create a SQL function that looks roughly like this:
create function foo()
returns void
set search_path = 'pg_catalog, pg_temp'
as $$
create temporary table a (b int) on commit drop;
insert into a select generate_series(0, 10);
$$…
Colin Emonds
- 765
- 4
- 18
1
vote
1 answer
Securing Postgres' Logical Replication Column Lists
I'm looking into publishing a replica database for consumption by other users in my org. This database contains a lot of sensitive data, so a solution I was considering was using Postgres 15's Logical Replication Column Lists, which allow you to…
morpen
- 11
- 1
1
vote
1 answer
PostgreSQL Check if security_invoker is turned on on view
Since PostgreSQL 15 it is possible to set security_invoker on views. I was wondering how to check if my views have it enabled or not, as neither pgAdmin nor DataGrip doesn't show that.
MegaCookie
- 5,017
- 3
- 21
- 25