A rather complex (depending on standards) query running on a table with about 1M records. Creating some temporary tables and building arrays and jsonb. On localhost I get average of 2.5 seconds. On Google SQL I get 17-19 seconds. Note: Other queries, like simple selects, are faster on server than on local. As…
Tag: postgresql
Postgresql reference a variable within statement
I come across this scenario frequently in sql and I have seen it done, but would like to summarize the case when/when not to reference a new variable within a statement. This doesn’t work – but this does – How can I implement scenario 1? Answer You can use a subquery.
Select last N rows that match C condition (PostgreSQL)
I have a table such as i.id t.status 140 ‘DONE’ 140 ‘RUNNING’ 140 ‘READY’ 137 ‘FAILED’ 137 ‘DONE’ 137 ‘DONE’ 123 ‘DONE’ 123 ‘DONE’ Which is a result of this query: I want to somehow get the id 137, because it’…
PostgreSQL create index on JSONB[]
Consider a table defined as follows: How can I create an index on json key type and how can I query on it? Edit: Previously, there were no indexes on json keys and select queries used an unnest operation as shown below: The problem is, if the table has a large number of rows, the above query will not only
How to import a JSON file into postgresql databse?
I just tried to read a .JSON file in my PostgreSQL database but it is not able to read it. I did it with a .csv file, but with a .JSON file, I am not able to do it. This is how the .JSON file looks like: This is the code that I tried, I created the table first and
How avoid ambiguousness when joining tables with a where statement?
i have a query such as SELECT * FROM bookings left JOIN answers on bookings.id=answers.booking_id where id=1 since both tables have id and booking_id i am getting : column reference “id”…
running sum or cumulative frequency using subquery
Below is the output of cumulative frequency according to datewise, which I want to change into the subquery. The output is also correct but I want to do it by using a subquery. OUTPUT Answer You would use window functions: Not only is this simpler to write, but it should be significantly faster — and no…
How to add/subtract n number of days from a custom date in postgresql?
I have a database in which, I have a issue_date column, and forecast_date column, I am selecting the maximum date from the database, but I want to fetch/query/extract the N th previous day from maximum available date like (maximum date – 1 / 2 or n number of days). & As the max date is custom, so ca…
How to grouping postgresql when id is same into new add value
I have table like this: And I would like to bring value item in one row when user_input_id and question_id is duplicate. The result that I wish is this: Can anyone tell me how to querying it? Thank You Answer You can easily do taht with string_agg(value,’,’) in postgresql. Output: You can also hav…
Finding top 10 products sold in a year
I have these tables below along with the definition. I want to find top 10 products sold in a year after finding counts and without using aggregation and in an optimized way. I want to know if aggregation is still needed or I can accomplish it without using aggregation. Below is the query. Can anyone suggest …