Update unique values/combinations of concatenated columns from another table – Postgresql
I have table2 contains around 26 columns & 2mln rows. a b c d e f g h i j k l m n o p q r s t u v w x y z 18 2 2 22…
Covering index to improve performance of a SELECT query? – Postgresql
I have following Postgres query that runs over a large table and whenever it does, I observe a high CPU load that reaches 100% : SELECT id, user_id, type FROM table ss WHERE end_timestamp > CURRENT_TIMESTAMP AND (notification_channel1 = true…
How can I improve multiple queries into one – Postgresql
I want to improve the performance of these 2 queries: select object into latitude from data where predicate = 'latitude' and subject = ( select object from data where subject = (select object from data where subject = 'url1' and…
How to fixed error 500 in postman from a post request? – Postgresql
I wonder if anyone got an insights regarding this error I got from postman below: { "timestamp": "2022-12-29T07:59:00.313+0000", "status": 500, "error": "Internal Server Error", "message": "could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement", "path":…
Update row if column value is older than 1 hour – Postgresql
I have this table in PostgreSQL: CREATE TABLE pet ( _id SERIAL PRIMARY KEY, player_id int REFERENCES player(_id), feed_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, num_poop integer DEFAULT 0, clean_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, play_time TIMESTAMP WITH TIME…
Spring Data, JPQL, Group by month of a date column(type LocalDate) – Postgresql
I have to get total price in month from a date yy-mm-dd with jpql query but I can't do it. @Query(value = "select new com.talan.food.dto.MonthIncomes( function('date_format',date,'%Y-%m'),SUM(p.price)) from Reservation p group by function('date_format',p.date,'%Y-%m')" ) public List<MonthIncomes> getIncomeByMonth(); And in the table…
Trouble with adding additional column with JOIN – Postgresql
Have two tables: Task: Make a table with fields: name of acquiring_company; name of acquired_company; price_amount; funding_total; Ignore those transactions in which the purchase amount (price_amount) and amount of investment (funding_total) is zero SELECT a.acquiring_company_id, a.acquired_company_id, a.price_amount, c.funding_total FROM acquisition…
FASTAPI Delete Operation giving Internal server error – Postgresql
I have this code for delete operation on a Postgresql DB: @app.delete("/posts/{id}", status_code=status.HTTP_204_NO_CONTENT) def delete_post(id: int): print("ID IS ",id) cursor.execute("""DELETE FROM public."Posts" WHERE id = %s""", (str(id),)) deleted_post = cursor.fetchone() <--- Showing error for this line conn.commit() if deleted_post is…
Revoke all on schema – Postgresql
I try add audit trigger for table in BD. I find example on GitHub. But I dont understand why author use this construction after create schema and table CREATE SCHEMA audit; REVOKE ALL ON SCHEMA audit FROM public; And for…
How can I retrieve the rows of all non-latest records whereby the latest is always excluded? – Postgresql
How would I get the non-latest record for each name. I want to get results such that if a name has one record, it is excluded because it is the latest. If a name has two records, I'll include the…