So I have this function: create function report (varchar) returns table ( id bigint, src_data_system_id int8 ) language plpgsql as $$ begin return query select id, src_data_system_id from process_log_objects plo where src_data_system_name = ‘somecondition’||$1; end $$ If in returns block I am using the same column names as in select query in a function body,… Read More How to return columns with the same name as original table when creating function?

I want to know if is possible if this kind of make queries has a name and which is the better option? OPTION 1 select * from users, tasks where users.id = tasks.user_id and user.name=’Lucas’ OPTION 2 SELECT * from task where user_id in (select id in users where user.name=’Lucas’) OPTION 3 SELECT * FROM… Read More which SQL query choose?

I want to get my sales for each day which is located in my orders_summary table. orders_summary table columns: id, date, amount, sku_id products table columns: id, sku Currently Im getting my daily sales like this: SELECT MAX(CASE WHEN os.date = ’01/01/2022′ THEN COALESCE(amount,0)::INT ELSE 0 END) AS orders_1, MAX(CASE WHEN os.date = ’01/02/2022′ THEN… Read More Does it matter to filter results when doing aggregation?

I’m doing a simple request to a database and trying to retrieve all the table data. However, i get this error: error: bind message supplies 1 parameters, but prepared statement "" requires 0 const { Client } = require(‘pg’); const client = new Client({ //connection details }); const query = async() => { await client.connect();… Read More postgress-node error: bind message supplies 1 parameters, but prepared statement "" requires 0

I have a table in Postgres as below : Key Value 1234 QAB009 1234 QAB009 1234 QAB010 1235 QAB011 1236 QAB012 1236 QAB012 1236 QAB013 I want output something similar to this , only values which are repeated more than once for a certain key Key Value 1234 QAB009 1236 QAB012 Appreciate any help! >Solution… Read More SQL to retrieve only duplicated values for a certain key

select sum(case when oi.status = ‘LOADING’ or (oi.status = ‘SORTING’ and oi.type = ‘RELEASE_OF_GOODS’) then oi.release_qty else 0 end) as nonLoadedQty ,sum(case when oi.status = ‘LOADING’ or (oi.status = ‘SORTING’ and oi.type = ‘RELEASE_OF_GOODS’) then (oi.release_uom_value/oi.release_qty * oi.release_qty) else 0 end) as "nonLoadedUomValue" from tmp_data rih left join pallets plt on plt.id = rih.reusable_pallet_id inner… Read More Referring alias from select sql query

I have the following DB structure. id projectname number filename type unq count 8 prj1 2 a t1 888389f661e117 1 9 prj1 2 a t1 888389f661e117 2 10 prj1 2 a t1 888389f661e117 2 11 prj1 2 a t2 816418549711c3d33 6 12 prj1 2 a t2 816418549711c3d33 7 13 prj1 2 a t2 816418549711c3d33 1… Read More Getting ratio of summed columns

What does this mean in PostgreSQL columnName citext NULL Im working on some SQL change, there is data in the database, but doesn’t show in the front end. Wonderig if this NULL makes the values go into null state? >Solution : The part you have shown means nothing and won’t work as it is. This… Read More What does citext NULL mean in PostgresSQL

I am learning SQL using Postgres. Here I want to get the employeeID where the employee does not have any cell phone, type ‘C’, that is the answer should be 3, 4, 5 because those employees don’t have cell phone. I tried the following query but it doesn’t give me the right answer instead it… Read More How to get ID in PostgreSQL?

I have this table: CREATE TABLE #Data ( Event_Date date, Person_ID varchar(1), Event_flag varchar(1) ) INSERT INTO #Data (Event_date, Person_ID, Event_flag) VALUES (‘20190910’, ‘A’, ‘X’), (‘20190911’, ‘A’, ‘X’), (‘20190912’, ‘A’, ‘Y’), (‘20190913’, ‘A’, ‘X’), (‘20190914’, ‘A’, ‘X’), (‘20190915’, ‘A’, ‘X’), (‘20190916’, ‘A’, ‘Y’) How to get result using row_number or loop, to get same row_numbers… Read More Group by event using row_number