I want to filter in subquery with aliased field from main query: select a.created_at::date as creation_date, a.column as col1, (select count(*) from table b where a.col1 = b.col1 and a.created_at::date = b.created_at::date) as count_one from table a group by creation_date,…
I want to know if there is an equivalent to the pg_stat_progress_vacuum() on general queries. For example, in my use-case there is a query that I know it is suppose to perform a seq_scan and I would like to know…
I have a table where the same ID can have multiple entries with start time and end time. How would I calculate the total time spent by the ID. The result I would want for the table below would be…
When i try to execute native query in my Spring JPA i give an error in my code ? import javax.management.Query; import javax.persistence.EntityManager; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import lombok.AllArgsConstructor; import lombok.extern.slf4j.Slf4j; import java.util.ArrayList; import java.util.List; import java.sql.SQLException; import…
I have two columns: column1 with two distinct values (0, 1) and column2 with 3 distinct values ('A', 'B', 'C'). Value in column1 is always 0 for 'A' and 'B' in column2, but if the value in column2 is 'C'…
I have this struct in a JSONB column named services of my PostgreSQL database: [ { "status": "ACTIVE", <another attributes> }, <another JSON objetcs> ] I want to make this select: select * from entity en where en.owner_id = '???'…
I have troubles creating certain aggregations. I'm using this table in Postgres: CREATE TABLE public.customer_courier_chat_messages ( sender_app_type character varying(255), customer_id integer, from_id integer, to_id integer, chat_started_by_message boolean, order_id integer, order_stage character varying(255), courier_id integer, message_sent_time timestamp with time zone );…
I have the following in tables in PostgreSQL: Relationship: ID: int Status: string RelatedParty: RelationshipID: int PersonID: int Role: string The idea is that a relationship has two related parties, each of which has a role in the relationship. Only…
In the query I shared below, I use 3 recursive loops to cumulatively retrieve debit, credit and budget values. Can I do this using 1 recursion instead of 3 recursions? Also, can it be done in this format without changing…
I am using Postgres, and would like to write SQL to do the following. I have this so far: select a.id, string_agg(concat(c.fname), ', ') as approvers from autoapprovalapproverconfig a left join autoapprovalapproverconfigmandate a2 on a2.autoapprovalaconfigurationid = a.id left join person…