Re: change natural column order

Using "SELECT * FROM <table_name>" from the PSQL prompt or any other interactive tool is perfectly fine.

Putting "SELECT * FROM <table_name>" into a compiled program using libpq or ESQL is a code defect. Period.

ALTER TABLE ADD COLUMN /* Most frequent defect maker for SELECT * */

ALTER TABLE DROP COLUMN /* If you didn't need the column, who cares */

ALTER TABLE RENAME COLUMN /* This will be a problem either way, but at least you will find out about it. It also shows why renaming columns is almost always a very, very bad idea after any release. */

ALTER TABLE SET WITHOUT OIDS {PG specific} /* One fewer column now, and all the column numbers are now 'off-by-one' */

DROP TABLE/CREATE TABLE /* New version may have the same name and the same number of columns, and they may even have the same data types but there is no guarantee that the meaning is the same. */

The list goes on and on.

It is a defect of equal magnitude to assume that columns are returned in any particular order unless specified in a column list (again, from a program and not interactively).

Another typical defect is to assume that columns come backed ordered by the primary key if the table is clustered on the primary key column. You can have a page split with many database systems and so there is no guarantee that data will be returned in order without an ORDER BY clause -- clustered or not.

Any of (ASSMUME NO COLUMN CHANGES/ASSUME COLUMN ORDER/ASSUME CLUSTERED KEY SORT ORDER) would cause me to fail code in a code review.

IMO-YMMV