I’ve got some problem with my request : This request work : This request work : But when I put an “OR” between the both requests, it doesn’t work : I’ve tried to put parentheses, but the request still doesn’t work. Do you have any idea ? Answer Becomes easier to read when y…
Tag: postgresql
How to select the most recent date for a specific condition in another column in Postgres?
I have a table with a structure like so. I’m trying to create a column with a query called most recent call which has the most recent call date for each id. Like this: I just want to have the most recent date for each id out of all the rows where the subject column equals call Answer You can
Order of cascaded deletes in postgres
I encountered a problem which makes me suspect Postgres deletes rows from dependent tables (ON DELETE CASCADE) only after it already deleted the original row. I have these tables: I created a trigger (before delete) on the function_inclusion table: Let’s say I have 2 functions and a function_inclusion: …
Updating sequence for primary key in postgres for a table with a lot of deletions
I have the following problem: I have a table with a lot of deletions and inserts of rows. Also I would like to assign to each of current rows in tables some id number. Currently I’m trying to do it with But if I understand correctly, sequences are monotonous and can’t go down when i delete some ro…
Can SQL with CASE condition use index in PostgreSQL?
When I write a SQL like SELECT * FROM table WHERE table.col && object, I could use the index I built (as it support && operator), but with It seems I can’t use the index? What is the reason of this? Is there any way to solve it? Actually I want to implement a logic like CASE WHEN a&&…
How to get the part of a string after the last occurrence of certain character?
I would like to have the substring after the last occurrence of a certin character. Now I found here how to get the first, second or so parts, but I need only the last part. The input data is a list of file directories: Unfortunately this is the data I have to work it, otherwise I could list it using
How to loop multiple form input and assign into sql query in php?
Refer to comment in the function, how to achieve that output? the idea is, from the input POST i get which over 27 fields, i just want to fill in into the $sql query i prepared as you can see. I don’t think writing each table column manually is a good way. im using Codeigniter 4 php framework + postgres…
How to aggregate json fields when using GROUP BY clause in postgres?
I have the following table structure in my Postgres DB (v12.0) id | pieces | item_id | material_detail —|——–|———|—————– 1 | 10 | 2 | [{“material_id”:1,”pieces”:10},…
How to bulk update sequence ID postgreSQL for all tables
I imported the Postgres SQL file to my server using TablePlus(SQL client), but after I insert new row I got error like this: SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint “users_pkey” DETAIL: Key (id)=(1) already exists I know it caused by sequence valu…
SQL Windowing Aggregation Over Two Consecutive Date
I am not an SQL expert and finding this a bit challenging. Imagine I have the following table but with more users: +———+——–+——–+————-+ | user_id | amount | date | …