Обсуждение: Sequences part 2
Hi again,
Looking more at sequences, I have some sequences that start with ID 1, incrementing each record by 1.
So I have e.g. 1 Spain 2. Germany 3. France 4. Ireland 5. Norway
Now I want to insert more countries, between France and Ireland. And also alter the increment. That latter part might be easy as I understood it? But can I achieve this:
1 Spain 2. Germany 3. France 4. Greece 5. Latvia 6. Cyprus 7. Ireland 8. Norway
That is inserting records in the middle of the records, maybe even
1 Spain 2. Germany 3. France 4. Greece 5. Ireland 6. Norway 14. Latvia 24. Cyprus
//Martin S
============================================================================================================================
Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindra policy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html externally http://tim.techmahindra.com/tim/disclaimer.html internally within TechMahindra.
============================================================================================================================
Karl Martin Skoldebrand schrieb am 02.07.2019 um 12:44: > Looking more at sequences, I have some sequences that start with ID 1, incrementing each record by 1. > > So I have e.g. 1 Spain 2. Germany 3. France 4. Ireland 5. Norway > > Now I want to insert more countries, between France and Ireland. That is a wrong assumption - there is no "between" for rows in a relational database. Why do you think you need that? A sequence is used to generate a unique number. The only job of that number is to be unique. You should never rely on PK values to be "gapless" nor should you rely on them defining any kind of ordering. If you need to sort your countries in a non-default way, add a specific sort_order column to your table which you can usewhen selecting from the table.
Karl Martin Skoldebrand schrieb am 02.07.2019 um 12:44: > Looking more at sequences, I have some sequences that start with ID 1, incrementing each record by 1. > > So I have e.g. 1 Spain 2. Germany 3. France 4. Ireland 5. Norway > > Now I want to insert more countries, between France and Ireland. That is a wrong assumption - there is no "between" for rows in a relational database. Why do you think you need that? A sequence is used to generate a unique number. The only job of that number is to be unique. You should never rely on PK values to be "gapless" nor should you rely on them defining any kind of ordering. ------ Yes, I'm fairly aware of this. However the application the database table belongs to seems to rely on a specific order inthe database. I.e. if I just add value to the table they end up, possibly due to how the application is coded in an undesireablespot on the web page. That is why I wanted them to be inserted with the previous records. //Martin S ============================================================================================================================ Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindrapolicy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html>externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html>internally within TechMahindra. ============================================================================================================================
On Tue, Jul 02, 2019 at 11:20:42AM +0000, Karl Martin Skoldebrand wrote: > Yes, I'm fairly aware of this. However the application the database table belongs to seems to rely on a specific orderin the database. I.e. if I just add value to the table they end up, possibly due to how the application is coded inan undesireable spot on the web page. > That is why I wanted them to be inserted with the previous records. rename the table install a view with the name of the table and appropriate triggers and/or rules between app and database enforcing whatever questionable assumptions the app thinks it needs -- if at all logically possible Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
> Yes, I'm fairly aware of this. However the application the database table belongs to seems to rely on a specific orderin the database. I.e. if I just add value to the table they end up, possibly due to how the application is coded inan undesireable spot on the web page. > That is why I wanted them to be inserted with the previous records. rename the table install a view with the name of the table and appropriate triggers and/or rules between app and database enforcing whateverquestionable assumptions the app thinks it needs -- if at all logically possible -------------- Hmm, that could be a possible avenue. The true horror of the database table is that it contains mixed data, so it could possibly be "easier" to just remodel thedatabase table (split it into several) for this function. If things are in fact the way they look to us at the moment.Either way it's not a quick fix for someone fairly inexperienced with PostgreSQL and having a couple of days to holiday. //Martin S ============================================================================================================================ Disclaimer: This message and the information contained herein is proprietary and confidential and subject to the Tech Mahindrapolicy statement, you may review the policy at http://www.techmahindra.com/Disclaimer.html <http://www.techmahindra.com/Disclaimer.html>externally http://tim.techmahindra.com/tim/disclaimer.html <http://tim.techmahindra.com/tim/disclaimer.html>internally within TechMahindra. ============================================================================================================================
Karl Martin Skoldebrand schrieb am 02.07.2019 um 13:20: >>> Now I want to insert more countries, between France and Ireland. >> >> That is a wrong assumption - there is no "between" for rows in a >> relational database. >> > > Yes, I'm fairly aware of this. However the application the > database table belongs to seems to rely on a specific order in the > database. I.e. if I just add value to the table they end up, possibly > due to how the application is coded in an undesireable spot on the > web page. That is why I wanted them to be inserted with the previous > records. There is no "order of rows" in a relational database. Even _if_ you squeeze the IDs of the new rows between existing ones, you still have no guarantee whatsoever on the order of the rows returned by a SELECT statement. The only(!) way to get a guaranteed sort order is to use ORDER BY. Thomas