postgresql Questions

Calculate duration between Phase – Postgresql

December 15, 2022
executable
2 Answers

I have the following history table (record user action): ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | parent_id | property_names | changed_property | time_c | outcome | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 123456 | {PhaseId,LastUpdateTime} | {"PhaseId":{"newValue":"Fulfill","oldValue":"Approve"},"LastUpdateTime":{"newValue":1671027321749,"oldValue":1671027321170}} | 1671027321749 | success | | 123456 | {PhaseId,LastUpdateTime,ApprovalStatus} | {"PhaseId":{"newValue":"Approve","oldValue":"Log"},"LastUpdateTime":{"newValue":1671011168777,"oldValue":1671011168043},"ApprovalStatus":{"newValue":"InProgress"}} |…

VIEW QUESTION

PostgreSQL interval returned in days

December 15, 2022
Ewald
2 Answers

I have this basic PostgreSQL query: SELECT AGE('2021-01-21', '1942-11-20'); Which returns an interval in days: output: 28531 days, 0:00:00 I am using PostgreSQL version 14, according to the docs, AGE() should return a symbolic result in years, months and days…

VIEW QUESTION

Catch any of the errors in psycopg2 without listing them explicitly – Postgresql

I have a try and except block where I would like to catch only the errors in the psycopg2.errors and not any other error. The explicit way would be: try: # execute a query cur = connection.cursor() cur.execute(sql_query) except psycopg2.errors.SyntaxError,…

VIEW QUESTION

Checking if today's date is in between the given start date and end date – Postgresql

December 15, 2022
Javeria
2 Answers

The code is written in postgresql and run in pgadmin 4. I am trying to get the id from the pricing table if the current date lies in between the given start date and end date. I am trying to…

VIEW QUESTION

Postgres JOIN child as JSON – Postgresql

December 15, 2022
textual
3 Answers

I have a node server accessing a postgres database through a npm package, pg, and have a working query that returns the data, but I think it may be able to be optimized. The data model is of versions and…

VIEW QUESTION

How to ignore implicit order by in psql group by query? – Postgresql

December 15, 2022
Malavan
2 Answers

Postgres implicitly sorts row based on the group by column. In MySQL, we can change this behavior by setting the ORDER BY NULL. Is there any similar alternative way to achieve this in Postgres [Reference]?

VIEW QUESTION

Insert string contains '$$' in postgresql

December 15, 2022
Tuấn Trương Quốc
2 Answers

I have a issue when I try to insert into users table. DO $$ DECLARE newId bigint; BEGIN INSERT INTO users("email","bio","status","created_by","updated_by") VALUES ('[email protected]','❄❄Haocute$$','accepted','1','1') RETURNING id INTO newId; UPDATE synces SET "version" = "version" + 1, updated_at = now(), new_id =…

VIEW QUESTION

What is better let database throw an execption or throw custom execption – Postgresql

December 14, 2022
Mohammed Omar
2 Answers

I am developing an authentication system using express, So I have a unique email field in the database should I check the email first and if it exists throw a new custom error Or let the database throw the error?…

VIEW QUESTION

how expensive are the postgres WITH RECURSIVE? – Postgresql

December 14, 2022
kouroshtajalliepour
2 Answers

I have a product system that each category can have multiple subcategory layers if there aren't any products attached to them, and we can attach as many products as we want to a category if there aren't any sub-categories attached…

VIEW QUESTION

Implementing a filter for a SQL query in a Rails app – Postgresql

December 14, 2022
Vinicius Libero
2 Answers

This is a rails app that uses Vue as front-end. There are 2 similar models/tables, named user_notifications and seller_notifications, that have the same columns. In the user_notifications_controller there are 2 distinct actions, one that shows only user_notifications (for the My…

VIEW QUESTION