Questions tagged [postgresql]
PostgreSQL is an open-source, relational database management system (RDBMS) available for all major platforms, including Linux, UNIX, Windows and OS X. Mention your version of Postgres when asking questions. Consider dba.stackexchange.com for questions concerning the administration or advanced features.
postgresql
177,533
questions
0
votes
0
answers
6
views
Title: Improving Performance for Netting and Settlement Cron Job in Spring JPA
I am working on a netting and settlement process in the banking and finance sector. Specifically, I need to run a cron job every 4 hours and 15 minutes to retrieve data from the transaction table for ...
0
votes
0
answers
5
views
Error with pg in Jest-ts testing : Jest has detected the following 1 open handle potentially keeping Jest from exiting: ● TCPWRAP
I have been facing the problem where my Jest test is not ending gracefully. (I prefer not to use --force-exit).
this is my jest code. FYI the tests passes. This is an integration testing.
import ...
-2
votes
0
answers
19
views
What language would you reccommend for building a UI to access and manipulate large SQL databases? (Why? Benefits/Drawbacks?)) [closed]
I'm fairly new to coding and have been learning and building a personal archival database using SQL over the past few months. However, I would also like to develop a simple (at first) UI to use for ...
1
vote
1
answer
20
views
How to pass dynamic value to a FOR loop?
I can do this:
for i in 1 .. (select pays from schema.table_name where pk_id='12' and pk2_id='123')
But it only works for this particular row. How to do it for all rows in the table?
Getting below ...
- 11
0
votes
0
answers
11
views
Delete record and record associations based on multiple reference attributes
I have a join table that handles the logic of who owns what:
create_table :exercise_drills do |t|
t.references :exercise, foreign_key: { on_delete: :cascade }, null: false
t.references :drill, ...
- 312
0
votes
1
answer
18
views
Client Encoding in psycopg3 missing, yet gives error
So I see in psycopg 2, there is a set_client_encoding('UTF8') function. In the psycopg 3 documentation it reads:
client_encoding is gone
Here I create the connection:
conn = psycopg.connect(...
0
votes
0
answers
13
views
I have this error: directory "/var/lib/postgresql/data" exists but is not empty
I have this issue in context Docker when execute .yml file. this file execute
initdb: error: directory "/var/lib/postgresql/data" exists but is not empty initdb: hint: If you want to create ...
- 11
0
votes
1
answer
18
views
How to add permission to custom role in postgres to create index
I am currently creating a role for a particular database using the below sql script executed as master user (postgres).
CREATE USER customrole WITH PASSWORD 'mypassword';
-- removed CREATE ...
- 884
-1
votes
0
answers
23
views
Quickfix/n App - Kafka - Missing messages in Kafka [closed]
First of all if I need to separate the post in multiple questions please tell me how to do it by preserving the diagram and general information. thanks
I implemented a dropcopy fix reader and ...
- 91
0
votes
1
answer
26
views
Springboot JPA postgres performance is slow
I have Springboot JPA integration with 2 databases. One is Oracle and the other is postgres 42.6.
I am fetching data from rest-api with 500 records in each request and saving it in postgres. Mostly ...
0
votes
0
answers
20
views
Materialized view with Postgresql/Hibernate/Panache
I have created a panache entity in Quarkus which map existing Materialized view. When I execute a refresh view from a test it's not work.
@Entity(name = "MY_VIEW")
@Immutable
@...
- 467
0
votes
0
answers
18
views
I can't open server in 0.0.0.0 instead it is working in 127.0.0.1 in while using Docker , Django and Postgres [duplicate]
I am a newbie to this.
settings.py file is:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql',
'NAME': 'postgres',
'USER': 'postgres',
'PASSWORD':...
- 9
0
votes
1
answer
19
views
WARNING: there is no transaction in process (PostgreSQL)
I kind of ran into something interesting. I've executed a SQL script using pgAdmin as GUI. With both autocommit on and off respectably.
Executed script contains these statements as follows:
begin;
...
0
votes
0
answers
13
views
How can i generate the arrow function format typeorm migration file?
when ever i am generating migration file i am getting this kind of format
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`ALTER TABLE "transaction&...
0
votes
0
answers
27
views
How to catch the PSQLException exception? [closed]
How to catch the **PSQLException ** exception? I am using Spring boot JDBCTemplate and it can throw a PSQLException exception. Using try-catch, I get this message from IDEA: "Exception 'org....
- 1
0
votes
0
answers
18
views
Why two queries with very different EXPLAIN plan cost estimate run in the same time?
I am trying to optimize the index of my table table. For this I have created two indices index_a and index_b.
When I run EXPLAIN plan. The cost for index_a reads as:
Index Only Scan using index_a on ...
- 7,290
0
votes
0
answers
16
views
DB2 to Aurora PostgreSQL Migration issues with trailing spaces
We are migrating the data from DB2 LUW to Aurora PostgreSQL database using AWS DMS.
For few tables we have a column with data type varchar(22) and the value of the column could end up having ending ...
0
votes
0
answers
8
views
How to Properly Create the Data Architecture for a PERN Stack Application?
I'm building a web application using the PERN stack, and I'm looking for best practices on how to design the data architecture.
I just did a basic pernTODO in order to practice my postgressSQL skill a ...
- 67
0
votes
1
answer
23
views
In pgloader loading csv file and i want to skip a row from file if specific column value is null
Loading csv file in pgloader using load file and I want to skip rows based on column condition for example if specific column value is null then i want to skip that row
Not able to get the approach ...
0
votes
0
answers
13
views
Navicat 16 Backup Error: 'column d.datlastsysoid does not exist' for Postgres 15.4 Database [duplicate]
I am using Navicat 16 to back up my remote PostgreSQL database, which is running version 15.4. However, during the backup process, I encounter an error that reads:
[ERR] ERROR: column d.datlastsysoid ...
- 1
1
vote
1
answer
59
views
INSERT rows that were missing for the UPDATE
I want to write a query that, if I was using variables, would look something like (pseudocode)
records = SELECT * FROM table_1 WHERE id = x
numberOfRowsUpdated = UPDATE table_2 SET column_1 = y WHERE ...
- 95
1
vote
1
answer
46
views
How to modify a PHP class to accept PgSql\Result as an attribute
I need to find a way (if possible) to make a PHP class accept a PgSql\Result object as an attribute. PHP 8.1+ has migrated the pgsql result resource to the PgSQL\Result instance. In prior versions ...
- 31
-2
votes
0
answers
22
views
insert in to target table and avoid the rows with same set of column value from source to target [duplicate]
-- Create a temporary table with sample data
CREATE TEMPORARY TABLE temp_student (
roll_no INT,
name VARCHAR(50)
);
-- Insert some sample records into the temporary table
INSERT INTO ...
-1
votes
0
answers
26
views
Cannot connect to Postgres from my php/symfony application
I'm having issues connecting to any database from my php/symfony application
when I try to connect to pg_connect("host=localhost port=5432 dbname=dbname user=username"); the app just dies ...
- 9
-3
votes
0
answers
30
views
Where is the Postgres installer for Windows? [closed]
Stupid question, I know, but I can't find the download link
https://www.postgresql.org/download/
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
the first page takes you to the ...
- 49
0
votes
1
answer
24
views
Not able to connect to foreign server
I am getting following error on PostgreSQL:
SQL Error [08001]: ERROR: could not connect to server "regionaldb_server"
Detail: connection to server at "localhost" (127.0.0.1), ...
0
votes
1
answer
47
views
Selecting Rows By Specific Column Data in PostgreSQL
I'm trying to build a recipe finder app (PERN stack) like SuperCook but I ran into some problems with my postgres table.
Short description of the desired result. I'd like the user to select some ...
- 13
-1
votes
0
answers
16
views
Flutter and Supabase (SQL)
Can someone tell me how I can create a page in flutter with Supabase where people can create their own Community and edit the Community name and this name will be displayed on another page for people ...
0
votes
1
answer
37
views
Why AT TIME ZONE increases the hours?
In PostgreSQL, I'm trying to convert a hour to local time zone.
My machine has UTC as timezone
show timezone;
UTC
why these queries return different answers?
SELECT '2024-07-01 00:00:00' at time zone ...
- 24.4k
-1
votes
0
answers
38
views
Use EOF to execute multi-line command in PSQL
I'm trying to run a PostgreSQL anonymous block in psql in nohup mode and capture the log in an output file. I am trying to put the code inside EOF but I always get an error. Not sure how to implement ...
- 669
0
votes
1
answer
47
views
SQL using ROWS BETWEEN to aggregate 12 months, nulls included?
I'm currently working with a table with the following columns:
Supplier
itemnumber
invoice_year_numerical
invoice_month_numerical
invoice date
invoice amount
invoice qty
I'm using SUM(invoice amount)...
- 3
1
vote
0
answers
42
views
Is a service opening a lot of connections to the database correct?
I have a small application which I have build into a Docker container, run in K8S and read from a PostgresQL database with Npgsql.
I hit it with 10K req / minute on a scale of 4 pods, so in best load ...
0
votes
0
answers
27
views
Unpredictable, bad performance of vector similarity search in Postgres database with `pgvector`
Problem
I have a Postgres query in the context of a Retrieval Augmented Generation (RAQ) application that is wrapped in a database function which shows poor, unpredictable and varying performance. I ...
- 547
0
votes
1
answer
14
views
PostgreSQL: grant privileges on tables in database like MariaDB
I'm struggling granting permissions in PostgreSQL being used mostly to MariaDB.
In MariaDB there is an easy console statement to grant all permissions on all tables inside a specific database to a ...
- 1,270
-1
votes
0
answers
32
views
How to Create Triggers Between Two Databases
In my system, I have a machine on AWS with several databases. My goal is to create some triggers/scripts that continuously read from some databases and, based on the information read, generate or not ...
0
votes
1
answer
21
views
PostgreSQL merge several json list
I read a lot about json combine methods but not found my way.
Have this kind of query
WITH
products AS (
SELECT * FROM configured_products() p
WHERE p.is_active
),
products_countries ...
- 1,684
-2
votes
0
answers
15
views
Analisis de datos [closed]
estoy realizando una aplicación en Flutter (web) cómo mi frontend y nodejs(tyscript) y postgres como motor de base de datos como podría implementar el análisis de datos en esta aplicación, una guía de ...
- 11
0
votes
1
answer
18
views
SQL UPDATE/INSERT with UnityJDBC - MultiSource Plugin
On SQuirrelSQL client I have installed the plugin "*UnityJDBC - MultiSource Plugin" that allow multiple sources.
I need to an INSERT or UPDATE on a PostgreSQL, but i alway get an error, even ...
- 461
0
votes
0
answers
7
views
How to update pgAdmin without losing server configurations?
I have pgAdmin 4 v5.0 on Windows 10, which is installed in: C:\PostgreSQL\v13\pgsql\pgAdmin 4. This makes me think it was installed as part of a local installation of PostgreSQL, and not separately. ...
- 1,317
0
votes
1
answer
13
views
Manipulating a column of data type json while executing a select query in postgressql with fucntion
W.r.t. the function below, I want this function to be called for manipulating a column of type json while executing select query.
CREATE OR REPLACE FUNCTION apply_foreign_exchange(input_value json, ...
- 363
0
votes
0
answers
38
views
How can I fix Postgres errors in Minitest?
I am using Minitest and Postgres to test my Rails website. When I added a "Like" model, I started getting Postgresql deadlock errors
Minitest::UnexpectedError: ActiveRecord::Deadlocked: PG::...
- 5
0
votes
0
answers
24
views
Pandas to_sql takes forever with Google Cloud SQL
I'm attempting to insert some data into Google Cloud SQL (running postgres) and it takes forever.
It takes roughly 1 minute to insert 10 rows.
I am not doing anything fancy, just initializing the ...
- 930
0
votes
1
answer
51
views
Postgres Join base table with other tables and show all records without duplicate
I have three tables:
Base table:
parent_id
parent_name
location
P1
Mr. Smith
New York
Child table:
s_id
child_name
parent_id
C1
Jane
P1
C2
John
P1
School table:
school_id
school_name
parent_id
...
0
votes
0
answers
12
views
Filtering on empty resources not working as expected on postgREST
I have a table of posts within a group. Every person in that group can interact with the post, star it, rate it, etc.
The structure is something like
Table User {
id;
groupId;
}
Table Post {
...
- 3
0
votes
0
answers
22
views
Building Knowledge Graph
I’m a total noob to building knowledge graphs so sorry in advance. I’ve been provided a large dataset with pretty unorthodox data about industrial machines, and I’m trying to create a scheme and ...
0
votes
1
answer
22
views
How to return JSON objects without explicit aliasing in postgresql
If I have two tables and I join them, can I return one of the values a JSON object, without having to specify all the fields?
For example, the following works:
SELECT u.*,
json_build_object(
'name', ...
- 3
1
vote
1
answer
30
views
How do I decrypt data originally encrypted with `pgp_pub_encrypt` with the `gpg` command line?
For reasons, I have data that was originally encrypted with the pgp_pub_encrypt function in PostgreSQL, using public/private keys that I have, like this:
INSERT INTO testtable(testdata)
VALUES(...
- 529
0
votes
1
answer
28
views
psql error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
I was installing postgres on macos sonoma (apple M1 chip) and after installing, then starting the service and finally running psql in the terminal I ran into this error
psql: error: connection to ...
- 555
0
votes
1
answer
38
views
SQL full outer join, default value to column in other table
I'm attempting to do a full outer join in Postgres to see how data changes between the past 24 hours and the 24 hours before that ("today" and "yesterday" euphemistically). Some ...
- 1,280
0
votes
0
answers
50
views
Wrong index is chosen when data are not random
I created a simple table:
CREATE TABLE logs (
user_id int4 NOT NULL,
create_time timestamptz NOT NULL DEFAULT now()
);
Inserted some random data:
INSERT INTO logs (user_id)
(SELECT RANDOM() *...
- 1,033