0

Data table structure

Hello and thank you very much in advance for taking the time to look at my question. My question is as followed, I have a large dataset in PostgreSQL, recorded in a similar fashion as it is written in the table.

The Province, District and Town column are constantly updated and new rows are added. I would like to have incremental numbers for the Province_id, District_id and Town_id automatically assigned whenever a new entry is made in one of the text columns. Preferablly, the numbers would update dynamically whenever a new Province, District or Town is entered (alphabetically).

Could I use triggers to make this automatically? I have also looked a lot at things like the dense_rank() functions (but is this a good method to follow?) I would be very happy to hear if there are alternative solutions. Thank you.

5
  • Why c-a-c is numbered 3-1-1 and not 3-1-2? Jun 25 '20 at 18:03
  • Different issue: why c-b-a is not numbered 3-2-3? Town IDs seem to restart per Province, not District. Jun 25 '20 at 18:08
  • Thanks for the edits, you are correct it should be 3-1-2, my mistake. Town IDs should restart per district, and could alphabetically. I changed that as well.
    – PGerrits
    Jun 25 '20 at 18:29
  • For a large table, renumbering a lot of rows when a new row is added won't scale well. What is the purpose of the ordering number?
    – Lennart
    Jun 25 '20 at 18:49
  • Thanks for you comment, the combination of these numbers would be used as a Primary Key and Foreign Key in other tables. I agree it isn't a great solution for scaling but unfortunately its how a lot of our data is already structured.
    – PGerrits
    Jun 25 '20 at 18:55
0

Assumption:

These ID values will only be used for output/display and will not be used as Unique Keys or in other tables (as Foreign Keys), otherwise you'll need a lot of cascade updates and loss of performance as a single row insert would result in updating a huge part of the table and many other tables as well.

Solution:

You don't have to store these values. You can use window functions, e.g. DENSE_RANK() to calculate these number when needed.

SELECT 
    province, district, town,
    DENSE_RANK() OVER (ORDER BY province)
        AS province_id,
    DENSE_RANK() OVER (PARTITION BY province
                       ORDER BY district)
        AS district_id,
    DENSE_RANK() OVER (PARTITION BY province, district
                       ORDER BY town)
        AS town_id
FROM
    tableX AS t
 ;
1
  • Thank you for your answer, it works for me and I have recreated it in my own database (after I changed GROUP BY to PARTITION BY). However, unfortunately my employer would like to have something like this as a Unique Key. I convinced him that such design isn't scalable and therefore a flawed database design. Instead of trying to have alphabetic numbering, I will use increments based on new insert. That way no previous rows need to be constantly updated. I have never worked with triggers before. Would you perhaps have any idea how I could make such an increment within a trigger?
    – PGerrits
    Jun 27 '20 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.