1,695 questions
-1
votes
1
answer
65
views
PostgreSQL Pagination Issue: (createdon, id) > (...) Condition Not Skipping Duplicates
I am implementing pagination in PostgreSQL using the (createdon, id) > (...) condition to fetch the next batch of records. However, I am facing an issue where records with the same createdon ...
- 2,605
1
vote
1
answer
101
views
Update every N rows with an increment of 1
I am an SQL server developer working on a project in a PostgreSQL environment. I am having some PostgreSQL syntax issues. I am working off version 9.3.
In a given table, I am trying to set every 10 ...
- 1,943
-1
votes
1
answer
47
views
To account for empty date periods
Please help me with a difficult task
I have a table that contains payments by date, for example:
date sum
2023-11-01 1
2023-12-01 2
2024-02-01 1
I need to find the minimum amount for the last 2 ...
- 21
0
votes
1
answer
77
views
Postgres sql with regex expression
Trying to run sql : https://dbfiddle.uk/VlKz_7fg
When I am running sql on fiddle its working fine, however when I am trying to run the same on my database it is throwing - ERROR: invalid regular ...
- 287
0
votes
0
answers
91
views
Postgres sort order results in different orders for different clients
Azure DB for Postgres flexible server
I have Azure DB for Postgres flexible server. I have created a database in this server with en_US.utf8 collation. In this database I have created a table with ...
- 1
0
votes
1
answer
36
views
Postgres, unable to usedistinct in json_aggr
I'm not so good with SQL I have three tables agencies, modules, documents. Modules and documents belongs to company.
I want to select a company with all the information and aggregate by status:
select ...
- 896
0
votes
2
answers
306
views
Need help configuring PostgreSQL connection to behave like MySQL: Connect to server without auto-selecting default database
In mysql when I enter the following
mysql -u root -p
Password: enter password successfully
I then get into my local server and from here I can view all my databases using SHOW DATABASES. But when I ...
- 29
1
vote
1
answer
36
views
Indicate a schema within an execution format
I combined a script to create tables with the same columns and lengths in a loop. The problem is that I want to create them in a specific schema, but I can't pinpoint it. They are currently being ...
- 11
0
votes
1
answer
880
views
pgadmin 4 connection failed Issue. Connection to server at "localhost (::1), port 5432 failed
I am having this issue very frequent now. The temp fix is to delete everything and install again. But as soon as i close pgadmin this issue comes again. Anyone knows a solution to this in macos?
- 59
0
votes
0
answers
22
views
Issue with accessing tableau dashboards whenever we trigger a batch job which uses pg_advisory_xact_lock function
The issue with accessing tableau dashboards whenever we trigger a batch job that uses the pg_advisory_xact_lock function
I have a parent schema named "Customer", every day a batch job gets ...
- 21
0
votes
1
answer
177
views
Issue with connecting Postgresql with flask
While I was trying to connect my flask app with postgresql I was getting the following error which says I don't have the libpq.5 the error log as follows
Traceback (most recent call last):
File "...
- 84
0
votes
1
answer
719
views
How to get the max value of an alphanumeric column
I Have a PostgreSQL database, there's a table named "labels".
Inside labels there's a column named "label" with a lot of values with the format 3 letters "GCS" and then ...
0
votes
0
answers
183
views
Is there any way to identify the unused Column in PostgreSQL?
I need to identify the table and particular columns which are not used and not updating by the app. based on the analysis we need to migrate db.
we tried to run queries but not getting the result that ...
0
votes
1
answer
131
views
1
vote
1
answer
2k
views
Does sqlalchemy ORM locks tables while update/insert operation with postgresql
Does sqlalchemy ORM locks tables while update/insert operation with postgresql.
I am trying to make sure that if i run a cron script which is based on python. I am not using 'begin()' with 'with ...
0
votes
0
answers
99
views
connect ECONNREFUSED 192.168.32.2:5432 docker-compose --env-file ./docker.env up
I am trying to dockerize my app, so I build a Dockerfile with the script below:
FROM node:18.12.1
WORKDIR /usr/src/app
COPY package*.json ./
RUN npm i -g npm@latest
RUN npm install
# If you are ...
1
vote
1
answer
1k
views
Delete in Postgres citus columnar storage alternatives
I am planning to use citus to store system logs for upto n no of days after which they should be deleted.Citus columnar store looked like the perfect database for this until I read
this where its ...
- 11
0
votes
0
answers
362
views
Query for store data from array in multiple row with same id in postgres Nest.js
I want a query for storing the data from an array in multiple rows with same id using typeorm postgres in nestjs. Note: I'm using one-to-many, many-to-one relationship for storing data. like a user ...
1
vote
0
answers
3k
views
Getting Postgres Exception : cannot commit while a subtransaction is active
We have the update query inside the transaction block with the exception block. Shown below:
do $$
begin
update dbo.myTable set name = 'Table Name' where id = 47;
commit;
exception ...
- 85
0
votes
1
answer
951
views
Need to improve performance of select query in postgres for 300Mil records
The context is we are migrating a large amount of data from a single tenant to a multi-tenant, so the need for querying.
We want to fetch the data in batches so we can pass it to the destination ...
1
vote
2
answers
292
views
How to get the sum of each branch in a Tree structure?
I have the next Tree Structure :
This structure is represented in the database with the next Table:
--------------------------------Employees-------------------------------------------
--------------...
user19031918
1
vote
2
answers
156
views
Where does PostgresSql localtime come from?
I've inherited a PostgreSQL database backup that was created with a version around 9.3. During the restore I'm getting the following error: "[archiver (db)] could not execute query: ERROR: ...
- 833
0
votes
1
answer
60
views
Can I usemultiple return values of INSERT...RETURNING in another INSERT or multiples insert?
I need take the id of anexo an then the result id list insert in another table
with new_id as(INSERT INTO
clienteproveedor.anexo
(
codigo,
estado,
facturar,
...
0
votes
0
answers
679
views
How to load a dataframe to postgresql 14
i have tried to load my dataframe to postgresql and especially to the server postgresql 14( i have 2 servers postgresql 9.3 running on port 5434 , and the other one is postgresql 14 running on port ...
3
votes
0
answers
1k
views
Check if enum type exists and add value
I'm new to PostgreSQl. I'm trying to add new values to type if type exists:
ALTER type ProcessStatus ADD VALUE IF NOT EXISTS 'Processing';
Above query will add 'Processing' to type ProcessStatus, but ...
- 101