Is id column position in Postgresql important?

Solution 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;