Solution #1:
Add a column to hold the time of entry:
ALTER TABLE life ADD COLUMN (entered_at timestamp); -- time of insert
ALTER TABLE life ALTER COLUMN entered_at DEFAULT now(); -- add it automagically
Then you can sort on this column, even if you don't select it for output (order by person_id, entered_at).
Disadvantage: Takes some extra space on disk. Use "WITHOUT OIDS" when creating the table to save some space.
Solution #2:
Use the OID of the row in the ORDER BY (order by person_id, oid). The OID is incremented for every row inserted.
Disadvantages:
This is unsafe, since it will fail when oid's wrap (after 2 billion inserts). That might not be a problem other than in theory :)
You cannot use "WITHOUT OIDS".
/Mattias
----- Original Message -----
Sent: Tuesday, June 10, 2003 11:50 AM
Subject: [GENERAL] viewing the original (chrnological) order of entered records
Hi
Ive the following problem:
I have a 2-column table with columns person_id(int4) and phase(text).
When I entered the following records in a chronological fashion: <1, high school>; <1, childhood> and <2, university>;
I requested the following select-statement.
SELECT person_id, phase FROM life ORDER BY person_id
And found the tuple <1, childhood> before the tuple <1, high school>.
I want to view the chronological order of my entries, but ordered by person_id.
Is this possible in postgresql?
