178,981 questions
0
votes
0
answers
51
views
postgres function "is not unique" [closed]
I am using PostgreSQL 18.0 and I have written a function with default parameters which inserts movie into the table. However, when I add the argument which must be added, it gives error:
SQL Error [...
- 31
1
vote
1
answer
69
views
Unallowed GRANT does not raise an exception and doesn't grant the privilege either, if user already has other privileges on the object
I have a regression test for a PostgreSQL database that checks to make sure a particular user cannot self-assign additional privileges to a particular schema. I do this by logging in as the user "...
- 1,015
-2
votes
0
answers
21
views
Remove the ?schema=public from database url [closed]
I learn this on github forum, others devs are having the same problem since prisma uptade to 7.0.0 version.
Font: https://github.com/prisma/studio/issues/1363
0
votes
1
answer
42
views
Why does `.WithInitScripts` not run my SQL files?
Description
I am using a test Postgres container (from testcontainers) for more accurate integration tests. To prevent repeating code, I want to use my existing SQL migration files to setup the ...
- 67
2
votes
1
answer
47
views
Storing store time of day ranges where timezone matters
I need to store some store hours that exist in different time zones and may be interacted with in different timezones through an admin portal where it will display store time and local time.
I know ...
- 21
2
votes
1
answer
50
views
Self-signed certificate error when forcing SSL connecting to RDS PSQL
Hoping to get yalls help on the issue I am now sort of desperate about
Context:
I am deploying a docker container on EC2 Ubuntu, where it is used by two images - one for my Prisma-NestJs-based API and ...
-1
votes
1
answer
64
views
Adding CHECK Constraint Without Error and Duplication [duplicate]
I have implemented this code to add a check constraint after creating the table
ALTER TABLE recruitment_data.candidate_experiences
ADD CONSTRAINT chk_experience_dates
CHECK ( ...
- 31
4
votes
3
answers
145
views
Regex function to extract numbers from string-type values, separated by "-", to get the difference of each two values
I'm 100% new to Regex so I've been floundering about on regex101 trying to figure out how to get my desired output. I am using PostgreSQL to write a query to extract a set of values from the string. ...
2
votes
1
answer
45
views
Symfony/Doctrine: PostgreSQL Migration Issue – Undefined column error only in HTTP Kernel (CLI works)
Symfony/Doctrine: PostgreSQL Migration Issue – Undefined column error only in HTTP Kernel (CLI works)
I'm working on a Symfony 7.3 project running on PHP 8.2. I recently migrated the database from ...
- 1,698
2
votes
1
answer
80
views
Using pg_try_advisory_xact_lock for events processing with strict ordering
Suppose I am implementing a local Event-Driven service with internal events to decouple logic.
For database we use Postgres 17 and PgBouncer with transactional mode. It's just a single instance.
...
1
vote
1
answer
112
views
Postgres Recursive Query Behavior
I have a table test with two columns, each referencing the other
ID1
ID2
2
1
3
2
4
3
5
4
Given an id, I need to traverse the chain to return all the rows traversing the chain. Below is the query I ...
- 325
1
vote
1
answer
75
views
Creating Materialized view in PG 17 ignores data types in SELECT
I have the following SQL to create a materialized view and the SQL seems reasonable to me but it's unclear from Postgres docs what should happen in this case.
I am listing the columns when creating ...
- 448
1
vote
1
answer
94
views
Avoid invalid cast inside CASE expression, in a query plan not respecting JOIN
I'm trying to get partitioned tables from a postgres DB. Our partitions have the date in them in various formats which I'm extracting like this
SELECT tab.relname AS table_name,
(CASE
...
- 1,006
Best practices
0
votes
6
replies
102
views
Slow database table insert (upload) with Pandas to_sql. What is the fastest method?
End Objective
Download some data from the internet (about 5 GB in size)
Possibly convert some strings/datetimes
Upload to Postgres database
I have written some code which uploads some data to a ...
- 18.6k
Advice
1
vote
2
replies
71
views
AWS How to identify/recommend the instance type for an RDS?
I am currently working on recommendation for aws rds instance type recommendation. We have identified the instances which are underutilised.
I want to know how to recommend a better version of rds ...
- 29
1
vote
0
answers
45
views
EF Core + Npgsql: "column 'status' is of type application_status but expression is of type text"
I've been struggling for two days with a PostgreSQL enum type issue in Entity Framework Core. Despite all my configuration, EF Core keeps trying to send a string (`text`) to an enum column.
The ...
-1
votes
1
answer
38
views
Minimal Python program to upload data to Postgres using psygopg3 COPY does not appear to run the COPY operation
I have written a minimal test example to try and debug some issue I have been having where COPY does not appear to do anything when used to copy data to a postgres database.
This is my Python code:
...
- 18.6k
Advice
1
vote
3
replies
38
views
Safe to run "VACUUM FREEZE" on template1 or template0 databases in postgres?
I run periodic VACUUM FREEZE on my main database to avoid aggressive vacuuming during busy times, wondering if it's safe to run freeze on template databases too before they reach the threshold (200M ...
- 7,577
1
vote
0
answers
73
views
DB query execution hanging in golang
I don’t understand why this is happening. I’m using the Go database package to connect to a PostgreSQL database. I loop through fullmsg (88 messages), but after processing about 27 messages, ...
- 46
Best practices
0
votes
3
replies
55
views
Compare fingerprints with a set of 10*10^6 other audio fingerprints in postgres
So I have a function that I used to compare audio fingerprints with a few thousand audio fingerprints stored in a postgresql. What I did basically was:
def my_function(cur: Cursor, threshold: ...
- 11
-4
votes
1
answer
50
views
PG::InsufficientPrivilege error using Digital Ocean's App Platform and a connected dev database for a Rails 8 app
I have a Rails 8 application using an attached dev database and I am deploying to the Digital Ocean App Platform. I can successfully build the application and when I get to the deploy step I see this ...
- 4,390
-3
votes
0
answers
19
views
Why does READ COMMITTED allow Phantom Reads in PostgreSQL? [migrated]
When writing SQL queries against a large PostgreSQL database, I am seeing phantom reads despite setting the transaction isolation level to READ COMMITTED. Can you provide a concrete example of a multi-...
0
votes
3
answers
74
views
Postgresql: server does not support ssl, but it was required
I was learning postgres with flutter, and my app kept crashing when I tried to connect to the database. The database is a postgres db running on a docker container.
Here is the Dockerfile
FROM ...
- 11
0
votes
0
answers
65
views
PGLoader fails when migrating data with newline characters in text columns [closed]
I’m trying to migrate data from MS SQL Server to PostgreSQL using PGLoader, but I’m running into an issue when the source table contains newline characters in a VARCHAR column.
Here’s my test setup:
...
- 1
1
vote
2
answers
63
views
PostgreSQL: connection was terminated due to conflict with recovery on primary node when running SELECT … FOR UPDATE
I have a PostgreSQL cluster with 1 primary and 1 streaming-replica standby. Occasionally I get the following JDBC exception on the primary node, not the standby:
SELECT \* FROM orders WHERE id IN (?) ...
- 11