179,040 questions
0
votes
0
answers
2
views
Copying initial table data into a file using Primary key or ctid in postgresql
Im trying to copy initial table data around 100 to 200 GB to a file in text format.I created a connection-1 and started a transaction with isolation level REPEATABLE READ and exported a snapshot to ...
- 306
0
votes
0
answers
3
views
Refresh Report Is failing in PowerBI when processing from Amazon RDS PostgresQL
I have a Power BI Premium plan and an RDS PostgreSQL instance running on AWS. Additionally, I have an EC2 server hosting a Power BI instance. We have connected Power BI to the RDS PostgreSQL through ...
- 73
0
votes
0
answers
4
views
How to install extenions from source in postgres or supabase using docker and docker-compose?
I am extending the supabase image to install apache age but when I run my migration to add the extension I get: extension "age" is not available
dbmate-1 | Applying: ...
- 277
0
votes
0
answers
22
views
ERROR: column "business_license_number" of relation "business" does not exist
Why do I get this?
ERROR: column "business_license_number" of relation "business" does not exist
insert into business
(
business_cust_id,
business_license_number,
...
0
votes
0
answers
11
views
Unable to call stored procedure from java which accepts postgres complex type as input
Hi I am working in java application and i am new to java. I have below sp.
CREATE OR REPLACE PROCEDURE integration.process_(IN p_data integration.mycustom_type[])
LANGUAGE plpgsql
SECURITY DEFINER
...
- 331
0
votes
0
answers
21
views
Hosted service cannot connect to Postgres database through Entity Framework Core, but API endpoints in the same application can
I have this code within a BackgroundService based hosted service:
await Task.Delay(TimeSpan.FromSeconds(30), stoppingToken);
while (!stoppingToken.IsCancellationRequested)
{
// Delete older ...
- 38.9k
1
vote
0
answers
43
views
Why does the adding of a COUNT(DISTINCT ...) affect the result of a SUM()?
I have a simple aggregation query that works as expected:
SELECT report_date, SUM(col1) AS sum_col1
FROM my_table
GROUP BY report_date
ORDER BY report_date;
However, when I add another aggregation ...
- 833
0
votes
1
answer
11
views
Docker-compose fails to create table on startup for Spring WebFlux/Postgresql
My application is running but the docker-compose is not creating the tables for me in my postgres docker container. At a loss for this at this point. Below is my relevant code
docker-compose
version: '...
- 117
-1
votes
0
answers
18
views
Postgresql query not finishing in PROD, but is for DEV and TEST [closed]
I think this is an optimization issue since the query works in 2 environments and doesn't in 1. There isn't that much data returned. latLog returns 12 rows, wrapProduction returns 249 rows. The ...
- 568
0
votes
0
answers
11
views
Function count associated entries with Sequelize return 1 instead 0
I try count foreign table rows with Sequelize
this.publicationModel.findAll({
attributes: {
include: [
[Sequelize.cast(Sequelize.fn('COUNT', 'comments.id'), 'int' ), '...
0
votes
1
answer
26
views
Recursive loops - HIBERNATE
I have a model for Category and Im using an adjacency list structure. When I'm trying to list all categories I'm receiving an infinite recursive loop. How can I fix it?
public class Category {
@Id
@...
0
votes
0
answers
48
views
Why does using `LIMIT` in this CTE give different results compared to using `LIMIT` in a subquery that refers to that CTE?
We have this query:
WITH
childObject AS (
SELECT
JSONB_BUILD_OBJECT(
'childObjectId',
(
SELECT
values_table.value::text
FROM
...
0
votes
1
answer
19
views
Hibernate 6.x - How to register a PostgreSQL function returning a table for use in JPQL?
i'm trying to register a PostgreSQL custom function in Hibernate 6.x so that I can use it directly in JPQL, without relying on native queries.
Scenario
I have a PostgreSQL SQL function that returns a ...
0
votes
1
answer
39
views
How can I take the difference in values between the output of two SQL queries?
The following PostgreSQL query:
SELECT REFERENCE_DATE, MIC, INSTRUMENT_TYPE, COUNT(INSTRUMENT_TYPE) as counter1
FROM reference
WHERE
reference_date = CURRENT_DATE() -1 and
--AND
MIC = 'TWEM' and ...
0
votes
1
answer
17
views
how can i write raw SQL in a NextJS project with nice UI?
i'm currently working on a project using NextJs with the VSCode editor and using Postgres for the DB with raw sql queries. I cant find a way to write my sql queries other than only with a single line, ...
0
votes
0
answers
18
views
Using pgBouncer on DigitalOcean with Node.js pg Pool and Kysely – Can They Coexist?
I'm running a Node.js application that connects to my PostgreSQL database using Kysely and the pg Pool. Here's the snippet of my current DB connection logic.
I have deployed my database on ...
- 1,081
0
votes
1
answer
45
views
Postgres Fixed 7-day window shifting data
I am trying to write a query to generate fixed non-overlapping windows and counting the number of records within each window. The next window starts on the next date of record after the previous ...
- 48
2
votes
1
answer
37
views
jooq trigger creating invalid function in postgres db
I try to create a trigger on one of my tables via jooq, the trigger does get created and also fires when inserting data into the table but fails with a syntax error.
The trigger is created like this:
...
- 286
0
votes
1
answer
13
views
how does postgres pg_rewind work internally
Are my assumptions correct regarding the pg_rewind?
Using the specific terminology from pg_rewind documentation to describe
source (new primary)
target (old primary)
pg_rewind only needs the timeline ...
0
votes
0
answers
10
views
can a sql script containing tables/schemas/roles be termed as IAC , or it is only limited to deployments ( docker/k8s/CF/terraform) [closed]
If someone say sql script containing tables/schemas/roles as IAC, will it be wrong ?
considering the db can be restored with snapshot
- 89
0
votes
0
answers
24
views
How to set role in Azure postgreSQL
I am new to PostGreSQL, and am trying to set up a user/database access to a new user, but am not able to, as I keep getting an error.
I create a new user with the following command
CREATE USER ...
- 3,608
0
votes
1
answer
37
views
Oracle style global temporary table in PostgreSQL
While migrating database from Oracle to PostgreSQL, I came across "Global Temporary Table" which I need to migrate equivalent to PostgreSQL.
Document say's
Optionally, GLOBAL or LOCAL can ...
- 7,270
0
votes
1
answer
26
views
SQLAlchemy always fails with `invalid connection option` error
I am trying to connect with CockroachDB from Python using SQLAlchemy with SSL.
In the below code I have shown various combinations I tried.
I am getting error for all the combinations.
Program
import ...
- 1,880
-1
votes
1
answer
37
views
HTTP GET strange behaviour - Spring boot [closed]
I mapped a table with pg admin Table "Prodotti"
In spring in the table, def I've got this
@Entity
@Table(name = "PRODOTTI")
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
...
0
votes
0
answers
33
views
PostgreSQL database isn't created automatically in kubernetes deployment
I want to deploy my postgreSQL database with kubernetes but the database is not created...I put POSTGRES_DB in env of the deployment.
the kubernetes deployment of postgres work very well but no "...
- 1
0
votes
1
answer
45
views
The table I created in pgAdmin is not found in psql
I created a table in pgAdmin 4 and connected to it asyncpg in my Python code.
When I use the \dt command in psql, it gives the error "Did not find any relations."
But I created the table in ...
- 11
0
votes
0
answers
21
views
PostgreSQL function , query disappear after long time [migrated]
We are facing an issue in production environment using PostgreSQL v14, we have a function which is selecting datas from a table then aggregate these data and then insert these aggregated data in ...
2
votes
2
answers
37
views
What happens when application thread shuts down while having a row lock in postgresql?
If application has acquired row lock and is performing transaction, when shut down (eg. power outage) what will happen to the locked row? Is the lock turned off and whole transaction is rolled back?
- 31
0
votes
1
answer
22
views
Postgres authentication messeges in log
I'm using in Postgres 17.1 on Ubuntu using docker.
I get a lot of messeges in the log:
2025-03-05 07:33:40.707 UTC [57] FATAL: password authentication failed for user "postgres"
2025-03-05 ...
- 31
0
votes
0
answers
19
views
Issue with Firebase DataConnect breaking on query with limits of 100 fields
When building simple select queries in GraphQL (really simple ones), the query is being transformed to a function, sending the values through as parameters.
We ran into instances where the select for ...
- 316
0
votes
1
answer
35
views
How can I get current log in username in postgresql?
I need to run a script like:
GRANT ALL ON "my_table" to ${select current_user};
The problem is it complains about the ${select current_user}. What is the right way to get current login user ...
- 42.6k
-3
votes
0
answers
37
views
How can I get data from booking.com to postgreSQL using presentation integration method? [closed]
can anyone help me see the code to be able to get data into postgreDB, I am having problems getting data from booking.com to my side. This is the code of the integration method application. I am ...
- 1
1
vote
1
answer
33
views
Why can db owner GRANT schema privileges on only some schema in the same database?
Anyone know why GRANTing ALL TABLE access to 1 schema would fail while succeeding for another schema in the same db? This failure happens when run as the database owner (either from Atlas or from the ...
- 409
1
vote
1
answer
60
views
REPLACE FROM in SQL Server vs babelfish
I get different results in PostgreSQL/Babelfish on AWS Aurora than I do with SQL Server for the following:
DECLARE @template NVARCHAR(MAX) = ' {@a} and {@b} and {@c}';
DECLARE @params AS TABLE
(
...
- 317
0
votes
2
answers
51
views
Integer PK but a GUID as FK
In our current PostgreSQL DB, primary keys are Ints. However, in almost all of the communications between services another candidate key is used, a GUID. While I am not the original author of the ...
- 35
0
votes
1
answer
42
views
Cannot install PostgreSQL 15/16/17 on Windows 11
I've tried all the standard things: running as admin, setting UTF-8, creating different users, shutting down firewalls, etc. I cannot even get the Install window to appear. Most of the time, the ...
- 419
0
votes
0
answers
17
views
Re-Using schema across two projects in NODEJS SAPUI5 CAP FIORI
I am new to nodejs sapui5 fiori cap applications and I am trying to create two applications that use the same database schema, they are connected to the same database as it is part of a multi-step ...
- 386
1
vote
1
answer
47
views
Problem with cleaning csv data before Importing in PostgreSQL
I started studying SQL a month ago. I'm trying to import a .CSV file but I have a problem.
In the that file, there are numbers written in quotes and where decimals are divided by commas.
In Excel, if ...
-1
votes
1
answer
21
views
High number of daily WAL generation
Our PostgreSQL database generates approximately 4 TB of WAL files daily. However, on the DR (Disaster Recovery) side, the network and disk performance are not sufficient to process this amount of WAL ...
- 39
2
votes
1
answer
55
views
PostgreSQL json_value() boolean behavior
I find it a little weird that
select json_value(jsonb '[true]','$[0]' returning jsonb)::text
returns true, but
select json_value(jsonb '[true]','$[0]')::text
returns t.
Is that a bug in PostgreSQL? ...
- 16.5k
0
votes
0
answers
45
views
C# PostgreSQL enum conversion [duplicate]
I have a PostgreSQL database with a bunch of user defined types, for example I have the following instructions:
CREATE TYPE joint_type AS ENUM ('Type1', 'Type2');
CREATE TABLE welding(
id SERIAL ...
- 1
0
votes
0
answers
14
views
.Net Aspire 9.1 - Service Discovery does not find Postgres server
These are my codes in AppHost
var postgres = builder
.AddPostgres("Postgres", port: 5432)
.WithImageTag("latest")
.WithContainerName("MyDatabase-PostgresDb-Aspire&...
- 1,627
-2
votes
0
answers
46
views
Django How to deal with database locking [closed]
I have two functions, periodic_signals(), and update_choice(), and use postgres.
update_choice() is called once users make a new choice of coordination and update the database. When users make a new ...
- 227
0
votes
1
answer
105
views
Ordering data after distinct
I distinct data by sku_id
queryset = self.filter_queryset(queryset.order_by('sku_id','id').distinct('sku_id'))
However this result is not sorted by id,
then I try to
queryset = self....
- 12.5k
0
votes
0
answers
21
views
SyntaxError: Unexpected strict mode reserved word in nestjs
package.json
{
"name": "nestjs-pinecone-api",
"version": "0.0.1",
"description": "NestJS API with Pinecone integration",
"...
- 34
0
votes
0
answers
24
views
SpringBoot+JPA+PostgreSQL10.4 How can I use postgresql's interval?
I would like to implement the following condition using SpringBoot, JPA, and PostgreSQL 10.4:
to_date(kokyaku.startdate, 'yyyy-MM-dd') < to_date('2025-01-31', 'yyyy-MM-dd') + cast(cast(1 - mprepay....
- 1
-2
votes
0
answers
82
views
Delphi query returns Null [closed]
Using a TZquery component, I'm running a query with 2 parameters. I have debugged it, evaluated every line, and everything is fine. Parameters are correct. I tried testing the query the way it shows ...
0
votes
0
answers
23
views
Connecting Apache/James to an external Postgresql databese
I will try to provide as much information to avoid misunderstanding. Please bare with me.
I am trying to run two docker containers, one running Apache/James and one PostgreSQL. The Apache/James ...
- 21
0
votes
1
answer
33
views
Postgres localhost connection timeout (but was working before)
>> psql
psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused (0x0000274D
/10061)
Is the server running on that host and accepting TCP/IP ...
- 2,130
2
votes
1
answer
60
views
Query bypassing SQLx compile-time checks for missing NOT NULL column
Query bypassing SQLx compile-time checks for missing NOT NULL column
Background
I've recently added a new required column to my database but I'm noticing that my SQLx query is still compiling ...
- 21