Request rows by creation time?

Question

I have a table that does not contain fields related to date or time. However, I want to query this table based on when the records / rows were created. Is there a way to do this in PostgreSQL?

I prefer to answer this directly in PostgreSQL. But if this is not possible, can hibernate do this for PostgreSQL?

Answer 1

Basically: no . PostgreSQL does not have an automatic timestamp for rows.

I usually add a column like this to my tables (ignoring time zones):

ALTER TABLE tbl ADD COLUMN log_in timestamp DEFAULT localtimestamp NOT NULL; 

As long as you are not manipulating the values ​​in this column, you have received a creation timestamp. You can add a trigger and / or restrict write permissions to avoid a vacation with values.

Second class options

• If you have a consistent column, you could at least say with some probability which order lines were entered. This is not 100% reliable, because values ​​can be changed manually, and applications can get values ​​from a sequence and INSERT not in order.

• If you created your table WITH (OIDS=TRUE) , then the OID column may be some sign - if your database is not very written and / or very old, then you may have passed OID wrap-around, and later rows may have smaller OID. This is one of the reasons why this feature is no longer used.

  The default value depends on the default_with_oids parameter default_with_oids I quote a guide:

The parameter is disabled by default; in PostgreSQL 8.0 and earlier, this was the default.

• If you did not update your lines or did not go through a dump / restore cycle or did not execute VACUUM FULL or CLUSTER or .., a simple SELECT * FROM tbl returns all the lines in the order in which they were entered. But this is very unreliable and implementation dependent. PostgreSQL (like any DBMS) does not guarantee any order without an ORDER BY .