PostgreSQL-按存储顺序检索项目 [英] PostgreSQL - retrieving items in order they were stored

问题描述

伙计们，我有下表：

CREATE TABLE IF NOT EXISTS users(
 userid           CHAR(100)         NOT NULL,
 assetid          text              NOT NULL,
 date             timestamp         NOT NULL,
 PRIMARY KEY(userid, assetid)
);

在我运行一些插入查询后，例如：

After I run a few insert queries such as :

INSERT INTO users (userid, assetid, date) VALUES ( foo, bar, now() );

我想检索记录，以便将它们存储在数据库中。但是，我似乎无法按顺序找回记录。

I would like to retrieve records in order they were stored in the database. However, i seem to be getting back records not in order.

我应该如何修改检索语句？

How should I modify my retrieve statement?

SELECT * FROM users WHERE userid=foo;

我希望对结果进行排序，以便存储它们：）

I would like the result to be sorted in order things were stored :)

谢谢！

推荐答案

假设您的日期列通过使用 ORDER BY 子句为每个项目保留不同的时间戳：

Assuming your date column holds different timestamps for each item, by using the ORDER BY clause:

SELECT * FROM users WHERE userid=foo ORDER BY "date";

但是，如果在单个事务中插入了大量记录，则 date 列的值对于所有这些列都可能是相同的-如果是这样，则无法（从给定的信息中）得知首先插入的是哪个。

However, if you inserted a large number of records in a single transaction, the date column value will probably be the same for all of them - if so, there is no way to tell which was inserted first (from the information given).

这篇关于PostgreSQL-按存储顺序检索项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！