Reset a Postgres sequence after a bulk import

Sql

Copying rows with explicit ids leaves the identity sequence behind, so the next insert collides with an existing row.

SELECT setval(pg_get_serial_sequence('users','id'), COALESCE(MAX(id),1)) FROM users;

Download

Databases postgres migrations chris

More in Databases

Which indexes is nobody using? Databases Lists indexes that have never been scanned, which are pure write cost. Check against a long uptime before dropping any. Delete duplicate rows, keeping the oldest Databases Uses a window function to number the duplicates and removes everything but the first of each group. Group rows into a JSON array Databases Returns one row per author with their posts nested, instead of a join that repeats the author on every row. Table sizes, largest first Databases Where the disk actually went, including indexes and TOAST. Add a column without locking the table Databases On Postgres 11 and later a nullable column with a default is a metadata-only change. Adding NOT NULL afterwards is the part that needs care. Upsert a row in SQLite Databases Inserts, or updates the existing row when the unique column already holds that value. Find slow queries in Postgres Databases Ranks statements by total time spent. Needs the pg_stat_statements extension enabled.

Random picks

Search the whole history for a string Git Finds every commit whose diff added or removed a piece of text, including in files long since deleted. Remove every stopped container and dangling image Containers Reclaims disk after a week of building. The volume flag is separate because it is the one that loses data. Cache busting with a content hash Build & CI Names the built file after its contents so a deploy invalidates the cache without a version bump. Context with a timeout in Go Systems Cancels the request and everything downstream of it after two seconds. The defer is not optional. Make some properties optional TypeScript Composes the built-in helpers rather than writing a second interface that will drift from the first. Delete files older than thirty days Shell Prunes an archive directory without touching anything recent. Run it with -print first to see what would go. Prepared statement with PDO PHP Placeholders rather than interpolation. This is the whole of SQL injection defence in PHP. Run a command on every file changed in a commit Shell Lints only what a commit touched, which is usually all you need in a pre-commit hook.