I'm implementing a dialogs search module on a messaging app. Retrieving the list of dialogs names would be as follows: select coalesce(c.custom_name, u.username) as dialog_name from user_chats uc join chats c using(chat_id) left join users u on uc.peer_id = u.user_id…
I have two tables that share IDs on a postgresql . I would like to select certain rows from table A, based on condition Y (in table A) AND based on Condition Z in a different table (B) ). For…
SQL optimization problem, which of the two solutions below is the most efficient? I have the table from the image, I need to group the data by CPF and date and know if the CPFs had at least one login_ok…
I have below price audit table in timescale db. When I get new price I should calculate minimum price in last 30 days. But our price data not daily. In every price changes I write date and price to table.…
I have a table in which one column represent a name of a table in my db. TableA | tableName | ------------- | table 1 | | table 2 | | ....... | | table n | What I need…
I need to return only the JSON from multiple rows where keys are matched. What am I missing? SELECT jsonb_object_agg(key, value) FROM jsonb_each((SELECT data from hit_count_13306823936951630992)) WHERE key IN ('8517', '1099'); ERROR: more than one row returned by a subquery…
I am using a table on a Postgres DB. I have a table with the following columns: ID STATE LOB CREATED TIMESTAMP SUBMISSION NUMBER SUBMISSION AMOUNT This table is being updated by 2 different insert queries: One where we receive…
I have 3 tables: SITES, DEVICES AND LOADS. SITES columns are (ID, default_MGR_ID); DEVICES columns are (ID, site_id (which is the foreign key of sites)) LOADS columns are (ID, device_id (foreign key of devices), site_mgr_id) I want to update LOADS.site_mgr_id…
The table below is a subset of a table from a db I have access to: CREATE TABLE trips(trip_id INT, foot BOOLEAN, bike BOOLEAN, bus BOOLEAN, car BOOLEAN, metro BOOLEAN, motorcycle BOOLEAN, train BOOLEAN, other BOOLEAN) -- sample values INSERT…
So I have 4 tables: post likes comments reads Each table has the column post_id I want to create a query, where for each post, i want to know the number of likes, the number of comments, and the number…