572 questions
0
votes
1
answer
57
views
Query performance does not improve when storing sorted data
I am trying to improve the performance of the base using the cluster command. For tests, I created copies of the original tables and sorted the data in them by foreign keys, by which the most frequent ...
- 1
0
votes
1
answer
149
views
Is there a way to create a custom collation in PostgreSQL 11?
I have a PostgreSQL 11.22 database, and I need TEXT columns in some tables to be able to store and sort strings in different languages.
The encoding is on UTF8, and it works, all different language ...
2
votes
1
answer
86
views
Why am I getting "collation attribute X not recognized" in my PostgreSQL database?
I'm running a PostgreSQL 11.22 database, and I want to create a new collation to support the Czech language, since I don't see it on the collations list when I select from pg_catalog.
Attempt 1
The ...
- 31
0
votes
1
answer
130
views
Postgres connection URL options stopped working switching from PG11 to PG14
I have a codebase that relies on a connection to Postgres via a full self-contained connection URL. The Python codebase was working while on PG11 but after switching to PG14 the options in the ...
- 14.5k
0
votes
0
answers
105
views
Do Aurora Serverless v2 supports Foreign Data Wrapper (FDW)?
Currently I'm using Aurora Serverless v1 which don't supports FDW(foreign data wrappers). I need to setup FDW for some requirement. I just want some ways to tackle this situation.
1.First approach may ...
1
vote
1
answer
1k
views
What happens with the old primary after a "pg_ctl promote" on a replica?
I have a PostgreSQL 11 streaming (ie. wal_type = replica) cascading replication scheme set up as follows:
db1 -> db2 -> db3
What happens to db1 and db3 if I execute a pg_ctl promote on db2?
...
- 2,511
1
vote
1
answer
97
views
PostgreSQL: Why "select into" is faster than "select count" from the same sourcetable?
After upgrading PG from 11 to 15, I struggeling with the following situation:
old (working) Setup:
Postgres 11 PostGIS 2.5
Setup:
Postgres 15 PostGIS 3.3
table_1 (~14.000.000 rows)
When I do select ...
- 72
2
votes
2
answers
103
views
Is there a way to update 2 values each time calculating data on each row?
I have the following table:
CREATE TABLE IF NOT EXISTS import.dre
(
idmov integer,
companynumber integer,
idproduct integer,
dtdate date,
nrtank integer,
dailybalance numeric,
...
0
votes
1
answer
262
views
Error during pg_upgrade Process: 'database is being accessed by other users'
During the pg_upgrade process, I encountered an error indicating that the "postgres" database is being accessed by other users, with one active session identified.
pg_restore: error: could ...
user19868155
2
votes
1
answer
4k
views
UPSERT (INSERT INTO ... ON CONFLICT) with multiple unique indexes - concurrency & unique constraint violation
I have the following (simplified) table examples in a PostgreSQL 11 database:
| Column | Type
| id | uuid
| core_id | character varying(255)
| name | character varying(255)
Indexes:
&...
- 2,819
0
votes
1
answer
4k
views
"fe_sendauth: no password supplied" During pg_upgrade Process
I'm encountering an error "fe_sendauth: no password supplied" while attempting to run pg_upgrade to migrate from PostgreSQL 11 to 14. I've set up a .pgpass file with the password and ...
user19868155
0
votes
1
answer
130
views
searching in multiple random named sub keys in json object in postgres
assuming I have json column which look something like:
-- first row:
{
"a": {
"x": {"name": "ben", "success": true},
&...
- 127
1
vote
2
answers
982
views
Postgres 11 not enforcing foreign key constraint?
Our PostgreSQL 11 database has tables A and B.
postgres=# \d A;
Table "public.A"
Column | Type | Collation | Nullable | ...
- 3,436
0
votes
0
answers
663
views
issues with "PGCONNECT_TIMEOUT" environmental variable
I have attempted to utilize the PGCONNECT_TIMEOUT environment variable for testing purposes. I've configured this variable to have a value of 2 seconds and subsequently executed my test cases. However,...
user19868155
0
votes
0
answers
83
views
Postgres query taking too much time on the first run
I have a a query which takes too much time on the very first run, and I need help reducing the time. It has too many large tables involved and has an index on all the columns involved in joins/where ...
- 1
2
votes
0
answers
88
views
bad performance of postgres query if on-clause of inner-join is positioned after following left-joins
Background: Hibernate 6 creates some generated queries different than hibernate 5.
In hibernate 5 a query looked like this (simplified):
select *
from document
inner join row on document.id = ...
- 43
2
votes
0
answers
548
views
Postgres Upgrade from 11.16 to 14.4
We are trying to upgrade our AWS RDS postgres version 11.16 to max allowed latest version 14.4
Now that we are skipping and jumping directly to 14. Is it a good idea? any issues we should look out for ...
- 429
0
votes
0
answers
63
views
Postgres trigger that inserts in a secondary table - sometimes seem to not work
I have a main table, let's call it A.
And a trigger.
create trigger my_trigger
after insert or update or delete
of column1, column2
on "A"
for each row
execute procedure ...
- 311
0
votes
0
answers
192
views
Taking incremental pg_dump since last restore
We are developing a large application and all our tests are based on a reference Test DB. This test DB is initially loaded by executing different scripts and you can add your own script to add more ...
- 5,504
1
vote
1
answer
197
views
Cannot use siglen parameter while creating index gist_trgm_ops in PostgreSQL 11.19 (Azure flexible DB)
When trying to create index on remote Azure server (PostgreSQL 11.19 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit) create index test on "Table" using ...
- 21
0
votes
1
answer
981
views
How to pass an array of tuples as parameter for a bulk UPSERT?
With this schema:
CREATE TABLE tag (
tag_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
tag_slug text UNIQUE NOT NULL,
tag_name text NOT NULL
);
I'm currently using Javascript to generate ...
- 4,642
-2
votes
1
answer
124
views
Why is this syntax invalid in postgresql?
I'm trying to insert some columns in a new table and set the ids in another one. I know this can be achieved using the WITH AS statement but I'm curious why it's not working this way
UPDATE "a&...
- 101
0
votes
1
answer
151
views
Finding records which might indicate fraudulent activities with SQL
I have following tables:
CREATE TABLE IF NOT EXISTS auth_user
(
id SERIAL PRIMARY KEY,
username VARCHAR(30),
first_name VARCHAR(30),
last_name VARCHAR(30)
);
CREATE TABLE IF NOT ...
- 3,039
0
votes
1
answer
29
views
How can I filter differences of datasets in a table
I am relativly new to SQL and I have a short question. I already searched for similar questions in stackOverflow but I couldn't find anything.I have created some Views. These Views change from one ...
- 19
1
vote
2
answers
2k
views
How to initialize PostgreSQL database directly within a Dockerfile?
Is there a way to initialize/start/keep running a PostgreSQL DB directly from a Dockerfile? I have the following Dockerfile.
# syntax = docker/dockerfile:1.4.0
FROM redhat/ubi8:latest
# Install a ...
- 3,119
0
votes
0
answers
121
views
Performance side effects to promoting a Postgres RR to a standalone DB?
I promoted a Postgres 11.16 RDS cluster secondary to function as a stand-alone database. This caused a significant decrease in the RR’s performance. Digging deeper, when comparing the results of ...
- 1,981
2
votes
1
answer
603
views
How to fix 'type abstime does not exist' error when upgrading PostgreSQL from 11.20 to 12.15?
Trying to upgrade PostgreSQL from 11.20 to 12.15 and hitting the below error
pg_restore: error: could not execute query: ERROR: type "abstime" does not exist
I do understand that Data types ...
- 23
2
votes
1
answer
665
views
CREATE DATABASE WITH TEMPLATE Postgres 11 -> 15 Performance
On our DEV workstations we have for test reasons to databases. Origin Database and Test Database, which is "destroyed" after testing.
In Postgres 11 we use the CREATE DATABASE with template ...
- 23
0
votes
0
answers
326
views
Query against Postgres runs forever over JDBC, but finishes in 1 Minute through pgAdmin
We have a pgsql function that inserts rows into the db based on a complex query. The database is postgres 11, on azure single server (16 cores, 32gb memory). The last problematic run would insert 6000 ...
- 5,069
-2
votes
4
answers
4k
views
Unable to locate package postgresql-server-dev-11 [closed]
I need to install postgres server 11 on ubuntu linux.
because it is recommended during the installation of postgres-11 and apache-age from source code:
i used this command in ubuntu linux
sudo apt ...
- 160
0
votes
1
answer
72
views
Conditional constraint if scheme and table exists in PostgresSQL
I am having multiple scheme defined, and while table creation I need to add conditional constraint that the constraint should only be added if the table and scheme exists in PostgresSQL. Below is the ...
- 1,583
2
votes
0
answers
1k
views
How to install libreadline.so.7 on Ubuntu 22
I have an Ubuntu 22.04 machine where I need to install PostgreSQL 11.9 and its client (psql). Psql requires libreadline.so.7 and it is not available in the Ubuntu repositories. Only libreadline.so.8 ...
- 1,179
2
votes
1
answer
143
views
How to send a NULL argument through a DirectFunctionCall?
I'm modifying the function Datum create_vlabel(PG_FUNCTION_ARGS), within Apache AGE.
But sometimes it's needed that one of the arguments to this function be NULL, and there's a check to that, like ...
- 221
0
votes
0
answers
998
views
Correct way of calling reference cursors in PostgreSQL 11 and above using JDBC driver
I need some suggestion on how to get the data through PostgreSQL JDBC driver from stored procedures using reference cursors.
Since PostgreSQL 11, it supports stored procedures with create procedure ...
- 169
0
votes
1
answer
1k
views
create a user mapping without password
I am trying to create a user mapping in PostgreSQL without a password, but I am encountering an error that says "ERROR: password is required" when I try to access a table. Here is the ...
- 125
1
vote
0
answers
2k
views
Java JDBC getting Postgres socket timeout exception for allowed connection
I have two virtual machines where one is hosting postgres DB and another application that is connecting to database. I have tried multiple configuration ways, but every time connecting I'm connecting ...
- 117
0
votes
1
answer
585
views
upgrading postgres 11 to 13 fails in consistency check
when i try to upgrade Postgres from version 11 to version 13 using this command:
/usr/lib/postgresql/13/bin//pg_upgrade
-b /postgresql/11/bin/
-B /postgresql/13/bin/
-d /data/service/postgres.1061/...
0
votes
0
answers
332
views
Unexpected sort order on postgres left outer join
Background
I'm using Postgres 11 and pgAdmin4 v5.2. The problem I describe below is on my dev machine which has both the postgres server and pgAdmin client.
Questions I've looked at on SO that deal ...
- 947
0
votes
0
answers
25
views
How can I open cmd in PostgreSQL?
I know, that it possible to write python code in postgresql with PL/Python Functions.
Is it possible to call cmd command from postgresql? I need to use pg_repack, not from terminal as usual, but from ...
- 147
5
votes
1
answer
3k
views
How to provide write permission for specific tables in PostgreSQL?
I have a user with read-only access in my PostgreSQL 11.
How can I give them access to write/edit data in some of the database tables, e.g. Table1 and Table2 only?
I don't want them to change the DB ...
- 1,884
0
votes
1
answer
376
views
use postgresql image with icu collation in circieci (cimg/postgresql, rails) [duplicate]
I am using cimg/postgres as my database image.
- image: cimg/postgres:11.12
environment:
POSTGRES_HOST_AUTH_METHOD: trust
POSTGRES_DB: XXXX
POSTGRES_USER: XXXX
Then I'd like ...
- 131
0
votes
1
answer
375
views
sequence sync option with pg_dump
I use pg_dump to backup and restore to target and manually sync (using setval function) sequences on tables. Is following an option to avoid manual sync or manual sync is always required?
pg_dump -s (...
- 67
0
votes
0
answers
336
views
old and new data directory size of postgresql does not match
I am trying to update PostgreSQL 11.2 to PostgreSQL 14.0.
My PG 11 Data directory size is 1.1 GB and my PG 14 data directory is 41 MB before the upgrade.
I am now running my below upgrade command:
&...
- 204
0
votes
2
answers
1k
views
postgreSQL - how to filter by json_agg value
I have join table between t_table and s_table.
there are many to many relationships between them.
s_table
id
s_value
1
1
2
2
3
3
t_table
id
t_value
1
100
2
200
3
300
t_id_s_id_table
s_id
t_id
1
1
1
2
...
- 97
0
votes
1
answer
232
views
Can't start server on PostgreSQL 11.17 - WSL
I have postgresql-11.17 source code on my windows, followed the installation from the documentation : PostgreSQL-11 Documentation but when I run the command ./pg_ctl -D /usr/local/pgsql/data -l ...
- 715
0
votes
1
answer
378
views
Postgres date_trunc function with timezone
Im using a C# query that automatically uses the date_trunc with timezone function, however when trying to run the function in postgres it doesnt work. I receive this error:
ERROR: function date_trunc(...
- 1,344
0
votes
1
answer
179
views
Revoke grant with dblink in Postgres
I start to use dblink on my postgreSQL 11 db and I have a problem with grant. I can create server and user mapping as superuser , but also I can delete this user mapping and server as usual user. I ...
- 147
1
vote
1
answer
2k
views
PostgreSQL log duration only on slow queries
Is there a way in PostgreSQL 11.2 to log the duration only for the slow queries?
If I set
log_duration=off
log_min_duration_statement=2000
The slow statements are logged, but I have no idea how long ...
- 71
1
vote
1
answer
1k
views
pg_restore not restoring sequences value
I have 2 databases : dbprod and dbdev, with 2 users uprod and udev.
What I'd like to do is to copy the production database to my development database.
I succeeded to restore my database, but for some ...
- 321
1
vote
1
answer
948
views
AWS RDS Postgres PostGIS upgrade problems
I have an RDS instance running Postgres 11.16. I'm trying to upgrade it to 12.11 but it's giving me errors on PostGIS. If I try a "modify" I get the following error in the precheck log file:...
- 416