Hi Flutter/Dart/PostgreSQL noob here; I have an SQL statement which is running fine from a python/Flask application, I am using some variables within it which are added in at runtime to the statement before it is sent to the db.This is the python version; As I said this works fine apologises if I have not add…
Tag: postgresql
Calculate share of value per day per country
DB-Fiddle Expected Result: I want to calculate the sales share per country per day as it is done in the question here. However, since I added the column channel in the database I am not getting the right shares anymore using this query: How do I need to modify this query to get the expected results? Answer he…
Column name is ambiguous on UPSERT action
I am trying to run an UPSERT query string on a postgres db – insert into business_data_test (select * from business_data_tmp) on conflict(phone_number) do update set average_expense=(…
similar to groupByKey() in Spark but using SQL queries
I trying to make into using only SQL queries. It is kind of similar to using groupByKey() in pyspark. Is there a way to do this? Answer Just use conditional aggregation. One method is: In Postgres, this would be phrased using the standard filter clause:
Function returns row type as single value instead of one value per field
The only time I encountered a similar problem on SO is on here, but the proposed solution was not relevant for me. I call a PostgreSQL function, but it’s returning a single value instead of one value per field, which I can access. I have a table “Words”, which consists of a primary key word …
Creating primary key in postgresql takes days
I am trying to write my table to a PostgreSQL database then declare a primary key. My data is really huge (includes billions of rows and total size is approximately 150 GB), when I try to create a primary key after writing the table, it takes forever. Here is how I define the primary key; I am %101 sure about
How to compare the value of one row with the upper row in one column of an ordered table?
I have a table in PostgreSQL that contains the GPS points from cell phones. It has an integer column that stores epoch (the number of seconds from 1960). I want to order the table based on time (epoch column), then, break the trips to sub trips when there is no GPS record for more than 2 minutes. I did it
adding many to many relations is producing an empty queryset
I have a model called Occurrence with a number of foreign key and manytomany relationships (i’ve only shown a few fields here). I am having issues when attempting to create any manytomany relation to an Occurrence instance after I have loaded bulk data to my postgres (postgis) database using the sqlalch…
Compare two arrays in PostgreSQL
I have a table in postgres with a value column that contains string arrays. My objective is to find all arrays that contain any of the following strings: {‘cat’, ‘dog’} The following query uses ANY() to check if ‘dog’ is equal to any of the items in each array and will corr…
Get rid of all empty strings values in jsonb | Postgres
I have rather abstract question on PostgreSQL jsonb data. For example I have a table called… table_one, where I have a column: In 100% cases in contains flat json structure like example: might be different length or null but always flat. My goal is like that. Whenever I select this column I need to somehow co…