如何在PostgreSQL中查询表中最后插入的行

针对你的需求，有几种高效的实现方式，可根据表结构选择：

1. 利用插入时间字段（推荐，适配你的表结构） #

你的表包含timeofinsertion字段记录插入时间，直接按该字段倒序排序后取第一行即可。如果数据量极大，建议给timeofinsertion字段创建索引，避免全表扫描：

SELECT *
FROM your_table_name
ORDER BY timeofinsertion DESC
LIMIT 1;

• 若存在多行同一时间插入的情况，PostgreSQL 13及以上版本可使用FETCH FIRST 1 ROWS WITH TIES替代LIMIT 1，返回所有最晚插入的行。

2. 利用自增主键（若表有自增主键） #

如果你的表有自增主键（比如定义为id SERIAL或id BIGSERIAL），由于主键值随插入递增，可通过主键倒序取第一行：

SELECT *
FROM your_table_name
ORDER BY id DESC
LIMIT 1;

• 这种方法性能优异，因为主键默认自带索引，前提是未手动修改过主键值。

3. 临时排查方案（不推荐生产环境） #

如果既无时间字段也无自增主键，可借助系统表和ctid临时查询，但ctid会在表清理（Vacuum）等操作后改变，仅适合临时使用：

SELECT *
FROM your_table_name
WHERE ctid = (SELECT max(ctid) FROM your_table_name);

示例查询结果（你的表输出） #

"timeofinsertion","selectedsiteid","devenv","threshold","ec50ewco","dose","apprateofproduct","concentrationofactingr","unclass_intr_nzccs","unclass_inbu_nzccs","vl_intr_nzccs","percentage_vl_per_total_nzccs_intr","vl_inbu_nzccs","percentage_vl_per_total_nzccs_inbu","totalvlnzccsinsite","percentage_total_vlnzccs_per_site","l_intr_nzccs","percentage_l_per_total_nzccs_intr","l_inbu_nzccs","percentage_l_per_total_nzccs_inbu","totallnzccsinsite","percentage_total_lnzccs_per_site","m_intr_nzccs","percentage_m_per_total_nzccs_intr","m_inbu_nzccs","percentage_m_per_total_nzccs_inbu","totalmnzccsinsite","percentage_total_mnzccs_per_site","h_intr_nzccs","percentage_h_per_total_nzccs_intr","h_inbu_nzccs","percentage_h_per_total_nzccs_inbu","totalhnzccsinsite","percentage_total_hnzccs_per_site","unclass_intr_zccs","unclass_inbu_zccs","vl_intr_zccs","percentage_vl_per_total_zccs_intr","vl_inbu_zccs","percentage_vl_per_total_zccs_inbu","totalvlzccsinsite","percentage_total_vlzccs_per_site","l_intr_zccs","percentage_l_per_total_zccs_intr","l_inbu_zccs","percentage_l_per_total_zccs_inbu","totallzccsinsite","percentage_total_lzccs_per_site","m_intr_zccs","percentage_m_per_total_zccs_intr","m_inbu_zccs","percentage_m_per_total_zccs_inbu","totalmlzccsinsite","percentage_total_mzccs_per_site","h_intr_zccs","percentage_h_per_total_zccs_intr","h_inbu_zccs","percentage_h_per_total_zccs_inbu","totalhzccsinsite","percentage_total_hzccs_per_site","totalunclassnzccs","totalunclasszccs","totalnzccsintr","totalnzccsinbu","totalnzccsinsite","totalzccsintr","totalzccsinbu","totalzccsinsite","totalvlinsite","percentageof_total_vl_insite_per_site","totallinsite","percentageof_total_l_insite_per_site","totalminsite","percentageof_total_m_insite_per_site","totalhinsite","percentageof_total_h_insite_per_site","total_unclass_with_nodatacells_excluded","total_unclass_with_nodatacells_included","total_with_nodatacells_excluded","total_with_nodatacells_included"
"3-2-2023 10:0:3:745762","202311011423",test,1,"3.125","0.75","75","100","0","0","0","0","0","0","0","0.0","0","0","0","0","0","0.0","0","0","0","0","0","0.0","0","0","0","0","0","0.0","0","0","0","0.0","32","91.4","32","82.1","0","0.0","3","8.6","3","7.7","4","100.0","0","0.0","4","10.3","0","0.0","0","0.0","0","0.0","0","0","0","0","0","4","35","39","32","82.1","3","7.7","4","10.3","0","0.0","0","0","39","39"

内容的提问来源于stack exchange，提问作者Amrmsmb