Let’s start with example: select title from books where name ilike ‘% of the rings’; If i create index on column title it will upgrade my performance or only if full correct string is provided in query? Answer For a standard index, Postgres should be able to use the index when the like patte…
calculating percentage on multiple sql tables
There are 2 tables mobile and web Table name: Mobile Table name: web I need to calculate percentage of users who only visited : mobile, web and both. That is, the percentage of users who are only in the mobile table, only in the web table and in both tables. The sum should be 1. I tried using union but
How can I import data from excel to postgres- many to many relationship
I’m creating a web application and I encountered a problem with importing data to a table in a postgress database. I have excel with id_b and id_cat(books id and categories id) books have several categories and categories can be assigned to many books, excel looks like this: excel data It has 30 000 rec…
Is there any method to find number of columns having data in pyspark data frame
I have a pyspark data frame that has 7 columns, I have to add a new column named “sum” and calculate a number of columns that have data (Not null) in the sum column.Example a data frame in which yellow highlighted part is required answer Answer This sum can be calculated like this: Hope this helps…
Sql syntax error while doing left outer Join in SQL
I’m using oracle database and trying to delete duplicate records from that. For the same I’ve written a below query but getting this error Its giving red indicator under outer keyword in Left outer join in query. Query: Answer Oracle does not permit JOINs in DELETE queries. This is clear if you lo…
How to update database after receiving PayPal IPN
I am trying a simple listener to update my user info database (like setting their account type from FREE to PREMIUM) after receiving the PayPal IPN. Upon checking the IPN history, the IPN is successfully sent to my listener but can’t seem to update my database. I wanted to update the user’s accoun…
Deduplicate table SQL with nested rows (type STRUCT)
I have a SQL table (in BigQuery) with possible duplicated rows. The table has over 20 columns, some of them are nested (data type “STRUCT)”. I want to deduplicate the table. I can’t simply query SELECT DISTINCT * because I get an error Query error: Column options of type STRUCT cannot be use…
Run UPDATE query while the INSERTs are running
I have a DB table which size is around 100,000 rows. I Need to run an UPDATE query on that table (affecting every row) which runs for approximately 25 minutes. During this 25 minutes, there will be approximately 300 INSERTs to the same table. I’m wondering whether those INSERTs will run? Will they be bl…
What happens when a file on a remote disk is not reachable while inserting records?
Suppose I have database with two filegroups A and B. filegroup B contains a single file on a remote server. What will happen when I am inserting records in a table defined on filegroup B, while the …
Calculating bounding box from latitude and longitude pairs
Google returns the current lat and lon, but the lower left lat and lon and upper right lat and lon are calculated. I have not been able to figure out how it’s calculated. Does anyone know if it’s a static distance? a percentage difference? How is it calculated? Chicago, IL New York UPDATE: Knowing…