sql - Is id column position in Postgresql important?

Question

I was testing a migration that deletes a primary key column id (I wanted to use a foreign key as primary key). When I ran and reverted the migration, I saw that the state of my table is the same, except that id column is now last one.

Will it change the behaviour of my database in any way and should I bother to restore the column order in the migration revert code?

Answer 1

In theory everything should be fine, but there are always scenarios when your code could fail.

For example:

a) blind insert:

 INSERT INTO tab_name
 VALUES (1, 'b', 'c');

A blind insert is when an INSERT query doesn’t specify which columns receive the inserted data.

Why is this a bad thing?

Because the database schema may change. Columns may be moved, renamed, added, or deleted. And when they are, one of at least three things can happen:

1. The query fails. This is the best-case scenario. Someone deleted a column from the target table, and now there aren’t enough columns for the insert to go into, or someone changed a data type and the inserted type isn’t compatible, or so on. But at least your data isn’t getting corrupted, and you may even know the problem exists because of an error message.

2. The query continues to work, and nothing is wrong. This is a middle-worst-case scenario. Your data isn’t corrupt, but the monster is still hiding under the bed.

3. The query continues to work, but now some data is being inserted somewhere it doesn’t belong. Your data is getting corrupted.

b) ORDER BY oridinal

SELECT *
FROM tab
ORDER BY 1;