I’m writing some query tests for dbt and I’d like to check that two queries return the same thing and that neither is empty. Since it’s a dbt test, I need to return a row if either of these conditions is false. So far I’ve come up with the following: Theoretically, this should return a row with 1 if either
Tag: postgresql
I am unable to upload a .csv file in postgresql database because I don’t know how to put date structure in sql query
I downloaded a csv file for practising where date format is of two types as shown in picture. the picture is here I tried to change the format to yyyy-mm-dd in excel but it is not happening. and also, I can’t upload the file in database in my postgresql. I used the data type “date” but it says I need
First row of all groups in Flask SQLAlchemy
I want to group the database by gauge_id and then get the row having maximum time_col. This is my current code: But I am only able to get both gauge_id and time_col. When I add another column (reading), like this: it gives this error. How do I return the top row of each groups? Thanks in advance. Answer One way
Output array type column
I have a table like so – How do I output this result – Answer For an array, you would use array_agg(): If you want the value as a comma-sseparated string:
How to iteratively use INSERT INTO in PostgreSQL
I have Table1 with rows I want to iterate over, and insert values from those rows of data into another table. So basically pseudo is: Just looping through Table1, adding in one of its values every time. Answer I think you just want an INSERT INTO … SELECT here: Most SQL operations are inherently set based, meaning if you want
Get all ancestors of a child in postgres recursive query
I am trying to get all related ancestors of a child with more info by joining with another table. I’m kind of new to backend so recursive cte is hard to understand. Find SQL Fiddle here Data I have : product_id,user_id Data I need : user_id master_id cost id(john_snow) null 4 id(bran_stark) id(john_snow) 6 id(arya_stark) id(bran_stark) 8 id(sansa_stark) id(arya_stark) 10
How to determine the order of the result from my postgres query?
I have the following query: This is giving me the result: Sometimes the order changes of the result changes from SOH -> SOC or from SOC -> SOH. I’m trying to modify my query so I always get SOH first and than SOC.. How can I achieve this? Answer You have two times that are identical. The order by is
query has no destination for result data, select functions names from schema
I am trying to turn select into function, but keep getting error: “query has no destination for result data”. Select returns 5 columns so I am trying to return table with 5 columns. I can not figure out what I am missing, please help! Answer As the error message suggests, you have to tell the function what to return. To
Return false if no rows for select
I have next query: It works fine, if we have some records in new_date table, but if it is empty for some user, query just returns empty string. How I can return false, if there are no records also? Answer You could just selects an EXISTS expression here: This would always return just a single record, containing a boolean value
JOIN is MUCH slower than a UNION, even with indexes
I have a query: All tables have indexes. Why is this query taking 4.5s across 6000 rows? Explain analyze output below: I have a UNION query that does the same thing, but is much quicker (0.001s). I want to know more about why my JOIN query is so much slower: Answer OR is always difficult for the optimiser(s). Since you