Custom SERIAL / autoincrement per group of values

by

This has been asked many times and the general idea is bound to fail in a multi-user environment – and a blog system sounds like exactly such a case.

So the best answer is: Don’t. Consider a different approach.

Drop the column category_id completely from your table – it does not store any information the other two columns (id, category) wouldn’t store already.

Your id is a serial column and already auto-increments in a reliable fashion.

If you need some kind of category_id without gaps per category, generate it on the fly with row_number():

More Related Contents:

  1. Serial numbers per group of rows for compound key
  2. How to implement a many-to-many relationship in PostgreSQL?
  3. Auto increment table column
  4. Create unique constraint with null columns
  5. Reset auto increment counter in postgres
  6. Safely rename tables using serial primary key columns
  7. PostgreSQL: Which Datatype should be used for Currency?
  8. How to set auto increment primary key in PostgreSQL?
  9. Postgres constraint for unique datetime range
  10. Select (retrieve) all records from multiple schemas using Postgres
  11. Which datatype should be used for currency?
  12. Use email address as primary key?
  13. PostgreSQL Index on JSON
  14. Temporal database design, with a twist (live vs draft rows)
  15. How to create id with AUTO_INCREMENT on Oracle?
  16. Refactor a PL/pgSQL function to return the output of various SELECT queries
  17. Subqueries vs joins
  18. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  19. How to store a list in a column of a database table
  20. Is there something like a zip() function in PostgreSQL that combines two arrays?
  21. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  22. Facebook database design?
  23. SQL update fields of one table from fields of another one
  24. Postgres – Function to return the intersection of 2 ARRAYs?
  25. Can I set ignore_dup_key on for a primary key?
  26. COPY with dynamic file name
  27. Table or column name cannot start with numeric?
  28. How do I add a auto_increment primary key in SQL Server database?
  29. PostgreSQL column ‘foo’ does not exist
  30. How to use SQL LIKE condition with multiple values in PostgreSQL?

Leave a Comment