Get last record of a table in Postgres

Question

If under "last record" you mean the record which has the latest timestamp value, then try this:

my_query = client.query("
  SELECT TIMESTAMP,
    value,
    card
  FROM my_table
  ORDER BY TIMESTAMP DESC
  LIMIT 1
");

you can use

SELECT timestamp, value, card 
FROM my_table 
ORDER BY timestamp DESC 
LIMIT 1

assuming you want also to sort by timestamp?

Easy way: ORDER BY in conjunction with LIMIT

SELECT timestamp, value, card
FROM my_table
ORDER BY timestamp DESC
LIMIT 1;

However, LIMIT is not standard and as stated by Wikipedia, The SQL standard's core functionality does not explicitly define a default sort order for Nulls.. Finally, only one row is returned when several records share the maximum timestamp.

Relational way:

The typical way of doing this is to check that no row has a higher timestamp than any row we retrieve.

SELECT timestamp, value, card
FROM my_table t1
WHERE NOT EXISTS (
  SELECT *
  FROM my_table t2
  WHERE t2.timestamp > t1.timestamp
);

It is my favorite solution, and the one I tend to use. The drawback is that our intent is not immediately clear when having a glimpse on this query.

Instructive way: MAX

To circumvent this, one can use MAX in the subquery instead of the correlation.

SELECT timestamp, value, card
FROM my_table
WHERE timestamp = (
  SELECT MAX(timestamp)
  FROM my_table
);

But without an index, two passes on the data will be necessary whereas the previous query can find the solution with only one scan. That said, we should not take performances into consideration when designing queries unless necessary, as we can expect optimizers to improve over time. However this particular kind of query is quite used.

Show off way: Windowing functions

I don't recommend doing this, but maybe you can make a good impression on your boss or something ;-)

SELECT DISTINCT
  first_value(timestamp) OVER w,
  first_value(value) OVER w,
  first_value(card) OVER w
FROM my_table
WINDOW w AS (ORDER BY timestamp DESC);

Actually this has the virtue of showing that a simple query can be expressed in a wide variety of ways (there are several others I can think of), and that picking one or the other form should be done according to several criteria such as:

• portability (Relational/Instructive ways)
• efficiency (Relational way)
• expressiveness (Easy/Instructive way)

If you accept a tip, create an id in this table like serial. The default of this field will be:

nextval('table_name_field_seq'::regclass).

So, you use a query to call the last register. Using your example:

pg_query($connection, "SELECT currval('table_name_field_seq') AS id;

I hope this tip helps you.

The last inserted record can be queried using this assuming you have the "id" as the primary key:

SELECT timestamp,value,card FROM my_table WHERE id=(select max(id) from my_table)

Assuming every new row inserted will use the highest integer value for the table's id.