sql - Postgresql按乱序排序

Question

我有一个数据库，我需要按照表中填充的相同顺序检索数据。表名是 bible 当我在 psql 中输入 table bible; 时，它会按照填充的顺序打印数据，但是当我尝试检索它时，有些行总是乱序的在下面的例子中:

表格圣经

-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------
id      | 1
day     | 1
book    | Genesis
chapter | 1
verse   | 1
text    | In the beginning God created the heavens and the earth.
link    | https://api.biblia.com/v1/bible/content/asv.txt.txt?passage=Genesis1.1&key=dc5e2d416f46150bf6ceb21d884b644f
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------
id      | 2
day     | 1
book    | John
chapter | 1
verse   | 1
text    | In the beginning was the Word, and the Word was with God, and the Word was God.
link    | https://api.biblia.com/v1/bible/content/asv.txt.txt?passage=John1.1&key=dc5e2d416f46150bf6ceb21d884b644f
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------
id      | 3
day     | 1
book    | John
chapter | 1
verse   | 2
text    | The same was in the beginning with God.
link    | https://api.biblia.com/v1/bible/content/asv.txt.txt?passage=John1.2&key=dc5e2d416f46150bf6ceb21d884b644f

一切都井井有条，但是当我尝试使用例如查询相同的东西时:select * from bible where day='1' 或 select * from bible where day=' 1' order by day 或 select * from bible where day='1' order by day, id;，我总是在选择的那一天得到一些乱序的行(这里 1 ) 或任何其他日期。我一直在使用 Django 来干扰 Postgres 数据库，但自从我发现这个问题后，我尝试使用 SQL 进行查询，但没有，我仍然得到乱序的行，尽管它们都有我用 select 验证的唯一 ID count(distinct id), count(id) 来自圣经；

- [ RECORD 1 ]------------------------------------------------------------------------------------------------------
id      | 1
day     | 1
book    | Genesis
chapter | 1
verse   | 1
text    | In the beginning God created the heavens and the earth.
link    | https://api.biblia.com/v1/bible/content/asv.txt.txt?passage=Genesis1.1&key=dc5e2d416f46150bf6ceb21d884b644f
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------
id      | 10
day     | 1
book    | Colossians
chapter | 1
verse   | 18
text    | And he is the head of the body, the church: who is the beginning, the firstborn from the dead; that in all things he might have the preemine
nce.
link    | https://api.biblia.com/v1/bible/content/asv.txt.txt?passage=Colossians1.18&key=dc5e2d416f46150bf6ceb21d884b644f
-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------
id      | 11
day     | 1
book    | Genesis
chapter | 1
verse   | 2
text    | And the earth was waste and void; and darkness was upon the face of the deep: and the Spirit of God moved upon the face of the waters.
link    | https://api.biblia.com/v1/bible/content/asv.txt.txt?passage=Genesis1.2&key=dc5e2d416f46150bf6ceb21d884b644f

正如您在上面看到的那样，如果您注意到，id 顺序为 1、10、11。

我的 table

                              Table "public.bible";
Column  | Type | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+------+-----------+----------+---------+----------+--------------+-------------
id      | text |           |          |         | extended |              | 
day     | text |           |          |         | extended |              | 
book    | text |           |          |         | extended |              | 
chapter | text |           |          |         | extended |              | 
verse   | text |           |          |         | extended |              | 
text    | text |           |          |         | extended |              | 
link    | text |           |          |         | extended |              | 
Access method: heap

id 字段是文本类型，因为我使用 pandas 的 to_sql() 方法来填充 bible 表。我尝试删除 id 列，然后使用 ALTER TABLE bible ADD COLUMN id SERIAL PRIMARY KEY; 再次将其作为 PK 添加，但我仍然无法按顺序返回数据。

无论如何，我是否可以通过使用 id 排序来检索数据，而不会使某些行完全乱序？提前致谢!

Answer 1

你应该将你的 id cast 为整数以将其排序为数字。

SELECT * FROM bible ORDER BY cast(id AS integer);