Serial numbers per group of rows for compound key

by

Don’t. It has been tried many times and it’s a pain.

Use a plain serial or IDENTITY column:

CREATE TABLE address_history (
  address_history_id serial PRIMARY KEY
, person_id int NOT NULL REFERENCES people(id)
, created_at timestamp NOT NULL DEFAULT current_timestamp
, previous_address text
);

Use the window function row_number() to get serial numbers without gaps per person_id. You could persist a VIEW that you can use as drop-in replacement for your table in queries to have those numbers ready:

CREATE VIEW address_history_nr AS
SELECT *, row_number() OVER (PARTITION BY person_id
                             ORDER BY address_history_id) AS adr_nr
FROM   address_history;

See:

Or you might want to ORDER BY something else. Maybe created_at? Better created_at, address_history_id to break possible ties. Related answer:

Also, the data type you are looking for is timestamp or timestamptz, not datetime in Postgres:

And you only need to store previous_address (or more details), not address, nor original_address. Both would be redundant in a sane data model.

More Related Contents:

  1. Custom SERIAL / autoincrement per group of values
  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. PostgreSQL “Column does not exist” but it actually does
  16. Is SELECT or INSERT in a function prone to race conditions?
  17. Any downsides of using data type “text” for storing strings?
  18. Query for array elements inside JSON type
  19. Calculating Cumulative Sum in PostgreSQL
  20. Postgres analogue to CROSS APPLY in SQL Server
  21. Get the default values of table columns in Postgres?
  22. Postgres not using index when index scan is much better option
  23. How to store array or multiple values in one column
  24. convert Postgres geometry format to WKT
  25. How to return two columns with function
  26. Composite Primary Keys : Good or Bad?
  27. Prepared Statement on Postgresql in Rails
  28. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  29. Primary key/foreign Key naming convention [closed]
  30. Is there a postgres CLOSEST operator?

Leave a Comment