179,105 questions
0
votes
1
answer
29
views
Python pg8000 query params...syntax error at or near \”$2\"
Hi…I’m trying to add a parameter to my query but I get this error:
"errorMessage": "{'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near \”$2\"', 'P': '3793', '...
- 1,265
0
votes
0
answers
20
views
Location of the connection file for dbeaver
I'm using Postgres DB ,
And the application DBeaver/pgAmin to connect to the database
My Postgres database password is an entraID token that refresh's every hour ..
It means that I need to change the ...
- 163
0
votes
0
answers
18
views
Is it necessary to carry out a Point-in-Time Recovery for both an original Azure database server and its read-only complement?
Given that:
You have an Azure subscription.
Within that subscription, you have an "Azure Database for PostgreSQL flexible server" - call this Server A.
Within the same subscription, you ...
- 732
0
votes
0
answers
27
views
Optimizing sql query [closed]
I need to write an SQL script for a denormalized data mart. However, I'm facing query optimization issues because the current SQL query doesn't execute (or executes too slowly)
This is My SQL- Query ...
- 83
0
votes
0
answers
7
views
How to generate only static library when using meson for postgres 17?
When building PostgreSQL 17.4 (or any 17.x), on Windows with meson, it always generates libpq.lib and libpq.dll as well. However, I don't want to consume shared library, but static only.
Passing ...
- 3,899
0
votes
0
answers
15
views
Alembic: Detected added unique constraint None on '('id',)
I am trying to use alembic in my FastAPI/SQLAlchemy app
I have a strange code in migration (more details below)
INFO [alembic.autogenerate.compare] Detected added unique constraint None on '('id',)'
...
- 1,902
1
vote
1
answer
42
views
Why is this PostgreSQL function throwing an exception, and how can I tell which input is?
I have a table which stores attribute value data, the majority of which are numbers but occasionally strings. When the values are numbers, we are interested in a delta. The following PostgreSQL ...
- 1,021
0
votes
2
answers
65
views
MAX() (and DISTINCT?) on an subquery
This query
SELECT
(SELECT LENGTH("PackForm")
FROM "schema"."T 2"
WHERE "ID-PrsPack_fkey" = "ID-PrsPack")
FROM
"schema".&...
- 431
0
votes
0
answers
25
views
Django [Postgres] restore single field/column
I have a model with field creation_date = models.DateTimeField(auto_now_add=True).
Doing some complex manipulations with migration file (converting model to multi-table inheritance and creating new ...
- 194
0
votes
0
answers
11
views
PostgreSQL storage cloud points or TINZ?
I’m using OSM vectors of transport infrastructure and I want to build 3D LINESTRINGZ instead of regular LINESTRING. Right now, for the third coordinate (Z), I’m using a raster, but it has a 5-meter ...
0
votes
0
answers
27
views
Using Select on a has_many_trough relationship from filamentphp on pgsql
Am working with laravel 11 with filamentphp 3 on postgres db, I have a schema for Authors, Profiles, Posts and post_has_author.
author(author_id, profile_fk,...)
profile(profile_id,...)
post(post_id,.....
- 93
1
vote
1
answer
43
views
Vapor Fluent - Composite primary key?
I have a SQL schema (PostgreSQL) that looks like this:
CREATE TABLE LinkedUserProfile (
user_profile_id int8 NOT NULL,
target_user_profile_id int8 NOT NULL,
createdDate ...
- 12k
1
vote
1
answer
40
views
PostgreSQL: is the CTE guaranteed to be evaluated before INSERT?
I need to upsert a row by primary key and return the previous value before the update.
I know I could do this with two separate queries — a SELECT followed by an INSERT ... ON CONFLICT ... DO UPDATE, ...
- 11
0
votes
0
answers
26
views
Python asyncio aiopg exception for correct combination of host and database
I have a list of 155 host + databases and I want to create a simple app in jupyter notebook that runs some query on them. Example looks as follows:
SQL = """
SELECT 1
"""
...
- 371
1
vote
1
answer
64
views
Lock a row in a trigger
I have two PostgreSQL tables: objects and metadata. Each object has a size property that represents its size in bytes and can be stored in a unique location with storage_id. In metadata, the ...
- 925
0
votes
1
answer
28
views
Django + DigitalOcean Managed PostgreS: “remaining connection slots are reserved for roles with the SUPERUSER attribute” when using connection pooling
I’m trying to configure Django to use a connection pool against a DigitalOcean Managed PostgreSQL instance, but keep running into this error:
OperationalError: connection failed: connection to server ...
1
vote
1
answer
35
views
PostgreSQL 63-Character Limit Issue in Sequelize with Multiple Includes
I’m currently working on a project where we are using PostgreSQL with Sequelize to perform complex queries involving multiple nested include relationships. However, we’ve encountered an issue related ...
- 11
0
votes
0
answers
14
views
Spring Boot + Postgres: Native Query/Projection Not Populating Array Attribute from Function/View
I’m working on a Spring Boot service with a Postgres database. I want to fetch data using a native query that leverages a view and a function, and map the results to an entity with a projected array/...
- 43
0
votes
1
answer
57
views
Join where some columns match but not all columns match
Requirements
I'm trying to write a query that helps me identify partially matched customers from two different regions.
I'm looking for a query that'll return all European customers, who's email or ...
- 502
-1
votes
0
answers
27
views
Not able to connect with Postgres database in GitHub Actions workflow
I am trying to run main.py file from GitHub Actions workflow. In main.py file, I have written some test, which require to fetch input from production database (I have already set secrets for host name,...
- 1
0
votes
1
answer
41
views
How do I limit the count of rows in a result set in Postgres without unnecessary locking?
This question is inspired by a 'general admission' variant of the common 'event ticketing' System Design interview question. Critically in this version, the user does not select a seat - they only say ...
- 31
0
votes
0
answers
49
views
PostgreSQL on Homebrew is Stuck on an Old Version and Not Connecting to PSQL [closed]
I'm encountering a very persistent and bizarre issue with Homebrew on macOS Sequoia (Intel-based). Despite multiple attempts to uninstall and reinstall Homebrew, and clearing caches, when I run
brew ...
- 107
1
vote
0
answers
20
views
Prisma cannot locate the Query Engine while deploying a NextJS-Prisma app in Vercel with Vercel's Neon PostreSQL
I'm deploying a NextJS App with prisma on Vercel with Vercel's Neon PostreSQl, Vercel doesn't give me any errors while deploying, but when it comes to making request to prisma, it always returns the ...
- 11
0
votes
0
answers
34
views
I'm trying to create docker-compose file, but I keep running into errors that are listed in the logs
This is my Dockerfile:
FROM openjdk:24
ARG APP_JAR=*.jar
COPY ${APP_JAR} app.jar
ENTRYPOINT ["java", "-jar", "app.jar"]
EXPOSE 8080
This is docker-compose:
version: '3.1'...
0
votes
2
answers
52
views
SQL row-wise comparison within group
I am trying to compute the number of events that take place within 30 minutes of one another (variable n_within_30) on the same date and in the same location.
I have tried to partition and group by ...
0
votes
0
answers
22
views
pgTAP complains about number of tests running when using SAVEPOINT and ROLLBACK TO right before finish()
I’m using Supabas/pgTAP to write tests and I’d like to isolate each assertion with a SAVEPOINT … ROLLBACK TO so data doesn’t leak between tests and also it's quite easier to understand tests when data ...
0
votes
0
answers
15
views
Supabase RLS with Supabase Javascript API SELECT not working
I am trying to read data from my profiles table. The profiles table has RLS enabled and has this policy for SELECT statements:
alter policy "Enable users to view their own data only"
on &...
- 288
0
votes
0
answers
34
views
Supabase Storage RLS Violation Despite Correct Policy Check?
I'm encountering a persistent RLS violation when trying to upload files to Supabase Storage using the JS client (supabase-js). The goal is to allow only authenticated users listed in a public.admin ...
0
votes
1
answer
65
views
Is it possible to update a view created from two views in PostgreSQL?
After searching about this issue, not sure if it is actually possible to do so, thanks for any pointers.
My view is created like this:
create or replace view OutputView as
select * from view1
natural ...
- 9,090
2
votes
0
answers
44
views
PostgreSQL Choosing Full Index Over Partial Index
I am using PostgreSQL 17.2 and working with the postgres_air Database.
I have a very simple query:
SELECT status
FROM postgres_air.flight
WHERE status = 'Canceled';
And the following indexes:
CREATE ...
- 21
1
vote
0
answers
71
views
Why I get connection timeout to Cloud SQL in GCP Cloud Functions Gen 2 randomly on some requests?
I have several Google Cloud Functions (Gen 2) functions that access a PostgreSQL database hosted in Cloud SQL, using node-postgres (pg).
These functions can be called multiple times by the same ...
0
votes
2
answers
66
views
Select rows where is_back from another table is not null or no such row exists
I have two tables cars and location_orders_cars.
A employee can order a car for a day and can bring it back so there is a flag location_orders_cars.is_back.
Of course he can order a car for some hours,...
- 73
1
vote
2
answers
56
views
postgresql cannot cast text[] to uuid
I have an array like this:
["f383224c-aa95-40c9-a4e4-fa41747f49c5","c783234c-aa95-40c9-a4e4-fa41747f49c6"]
And I want to select with any but I got an error cannot cast text[] to ...
- 73
1
vote
3
answers
83
views
SQL query to aggregate and count non-null values across multiple columns
I have the following database schema. It looks at shoppers and how many orders they have made from three websites in a network.
ID
Name
Country
website1_Orders
website2_Orders
website3_Orders
123
...
- 11
-1
votes
0
answers
68
views
Python says it has inserted row into DB, but it hasn't
I am working on a simple auth system for one of my projects.
I have a PostgreSQL database with 2 tables, "public"."app_users" and "public"."active_logins"
The ...
0
votes
2
answers
62
views
EF Core doesn't map foreign keys properly when creating new entities
I'm having trouble creating new entities with one-to-one relationships. EF Core is not assigning the foreign keys properly.
As an example, let's take two entities:
public class User
{
public int ...
- 17
0
votes
0
answers
27
views
How to set default password for a user in percona-postgresql-operator v.2.6.0
I am trying to set up PostgreSQL in a Kubernetes cluster (in my local environment with kind), using the percona-postgresql-operator. I am trying to set a default password for a user, but it doesn't ...
-1
votes
0
answers
28
views
Save pandas dataframe in postgres database
Is there a way to save pandas dataframe as a value in a postgres database column? Let's say the column was named df_metadata
The use case is that we want to save the dataframe to later run statistical ...
- 21
2
votes
1
answer
44
views
Why is timestamptz + interval considered mutable?
I tried to create a generated column that adds timestamptz and interval columns, but Postgres complains that this expression is immutable:
create table test ( ...
- 8,792
0
votes
1
answer
42
views
Spring Boot doesn't catch SQLState custom exception from PostgreSQL trigger – Angular gets 500 but Postman gets 400
I'm working on a Spring Boot + PostgreSQL project where I have a trigger function that raises custom exceptions using RAISE EXCEPTION with SQLSTATE codes.
Here is the PostgreSQL trigger function:
...
0
votes
0
answers
28
views
Podman WSL2 (windows) persistent postgres container
I am trying to get postgres container running on my windows 10 machine with podman on wsl2.
command i am using is
podman run -d --name test-postgres -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=admin -...
- 1,675
0
votes
1
answer
39
views
PostgreSQL Table Field exists in Type Field;
How do I check if fields of a table exist in a user definend Type in PostgeSQL?
The Thing is that i want to compare the attributes and create a new Table only with the existing fields of the Type (...
- 107
0
votes
1
answer
90
views
PostgreSQL simple query slow on server
I use Prisma for two really simple queries in my Node backend
const excavationSiteCount: number = (await client.excavation_sites.count({where: {owner: user}}));
const artifactCount: number = (await ...
- 291
1
vote
1
answer
46
views
How to use index to search if trim is used in search value
My Postgres 17 database has a large table with 245 columns containing an indexed column ribakood:
CREATE TABLE firma2.toode
(
...
ribakood character(20) COLLATE pg_catalog."default",
...
...
- 28k
0
votes
1
answer
42
views
The cache, users, jobs and personal_access_tokens tables are not created in Laravel's public folder
When creating a postgres database, the following tables are not automatically created:
enter image description here
As I understood they should be created in the 'public' folder, but it is empty.
I ...
- 9
0
votes
0
answers
49
views
How to implement data encryption in postgresql [closed]
In HRMS service I made employee,salary and other things for employee apis,have done secret manager implementation so that only user with specific roles can access the api now want to implement db ...
- 1
0
votes
0
answers
47
views
How to set all field names to lowercase by default
When querying Postgres, field names are case-insensitive:
select id from contact;
and
select Id from contact;
will both work.
But, when using sequelize (v6), the attribute in my model definition must ...
- 485
-1
votes
1
answer
39
views
Nested Loop Join - How to resolve [closed]
I have a query that is continually running extremely slowly due to a nested loop join. I am joining a table of customer data to a calendar table that contains dates, week, quarter and year values. The ...
- 1
0
votes
0
answers
26
views
How to use a dynamically created table name in a PL/pgSQL block (Postgres)
I have a block of code in PL/pgSQL that includes this line to update a table...
UPDATE myTable SET processedT = true, messType = 12 WHERE messId = new.messID;
The table myTable is actually a ...
- 847
-1
votes
2
answers
69
views
How to get the most value, id and timestamp for every day
Data
id
timestamp
value
225
2018-07-24 13:00:00
0
226
2018-07-24 18:33:32
196
227
2018-07-25 5:59:14
290
173
2018-07-05 8:16:14
258
228
2018-07-25 7:00:00
469
175
2018-07-07 6:00:00
0
176
2018-07-07 9:...
- 37