If you are using Laravel’s firstOrCreate() method and get a unique id violation error, there is a chance you have repopulated your database retroactively in Postgres. Postgres triggers the next id in the sequence on row creation instead of on import. So it thinks the next sequence in the id field is an existing ID … Read more
This query is to delete duplicate rows on multiple selected columns for Postgres. Just plug in the table_name and columns you want to evaluate duplicates on. This query is the fastest out of several I’ve tried, evaluating and deleting 5 million duplicate rows on 8 columns in about 20 seconds.
This is a baller gist of common useful Postgres queries.
If your query is forcing the db to search through millions of rows to fetch data, indexing a table can help improve performance. Here’s how to do it on Postgres. ELI5 Index If you’re actually five you probably haven’t seen one of those indexes in wooden box that you saw at public libraries, but it … Read more
Unsuccessful with the making columns case-insensitive framework level, I had to go deeper. Here’s how I changed my Postgres columns to be case-insensitive, and ultimately solve my model relationship problems. CITEXT Extension for Postgres You needs to provision the extension to the database. If you use Heroku (like me here’s a simple step). The citext module provides … Read more
