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…
Tag: postgresql
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…
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…
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…
Postgres recursive query – get path to root given a leaf node
I have a leaf node in a tree structure and I need a Postgres query to recursively traverse the parent nodes to get me the full path to my leaf node. My table looks like this – the problem being there is no specific root node (i.e the top-most managers end up reporting to each other) Given a user such
How to not insert two of the same values
I’m using an input to allow user’s to select files from their computer and add it to a list. I’m doing this by using a promise to load the files and creating an array of promises and adding that to my state React JS I’m then mapping through my files array to add those files to a list o…
In PostgreSQL, I’m performing an UPSERT on 1000 rows, I’m using ON CONFLICT … DO NOTHING, if 1 row fails, do the other 999 get inserted?
I’m trying to understand how the ON CONFLICT clause works. What I want, is if an insert fails for any reason, I want it to do nothing with the row, but continue processing the other items in the bulk insert. Here’s my scenario. I’m building a bulk insert query, with 1000 rows to be inserted.…
How to select rows using only number previous loaded rows?
I got a table with some rows For example: Them i load a half of rows After that i need to load all other rows, but all what i know about previous operation is that i load only 2 rows So i got only the number of already loaded rows = 2 So know i got a number or rows
Cannot figure out the issue with this SQL CASE
The issue i am facing is: “SYNTAX_ERROR: line 7:6: All CASE results must be the same type: boolean” I have also joined the table in a previous sub query casting it as a varchar, so there shouldn’t be any issues with this. Anyone have any ideas? For context i am trying to populate a field tha…
SELECT query without FROM clause with a million rows
I’m trying to generate a million rows for a database table using SQL. The logic for the columns is in place, but I’m not sure how to go about generating a large number of rows without having a FROM clause. I’m aware of how to write the SQL query without a FROM clause – select ‘a&…