如何在 PostgreSQL 中返回记录数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7186207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return record count in PostgreSQL
提问by Дамян Станчев
I have a query with a limit and an offset. For example:
我有一个带有限制和偏移量的查询。例如：
select * from tbl
limit 10 offset 100;
How to keep track of the count of the records, without running a second query like:
如何在不运行第二个查询的情况下跟踪记录的计数，例如：
select count(*) from tbl;
I think thisanswers my question, but I need it for PostgreSQL. Any ideas?
我认为这回答了我的问题，但我需要它用于 PostgreSQL。有任何想法吗？
回答by Дамян Станчев
I have found a solution and I want to share it. What I do is - I create a temp table from my real table with the filters applied, then I select from the temp table with a limit and offset (no limitations, so the performance is good), then select count(*) from the temp table (again no filters), then the other stuff I need and last - I drop the temp table.
我找到了一个解决方案，我想分享它。我所做的是 - 我从我的真实表中创建一个临时表并应用过滤器，然后我从临时表中选择一个限制和偏移量（没有限制，所以性能很好），然后从临时表（再次没有过滤器），然后是我需要的其他东西，最后 - 我删除了临时表。
select * into tmp_tbl from tbl where [limitations];
select * from tmp_tbl offset 10 limit 10;
select count(*) from tmp_tbl;
select other_stuff from tmp_tbl;
drop table tmp_tbl;
回答by Garett
I haven't tried this, but from the section titled Obtaining the Result Statusin the documentationyou can use the GET DIAGNOSTICScommand to determine the effect of a command.
我还没有尝试过，但是从文档中标题为的部分Obtaining the Result Status，您可以使用该命令来确定命令的效果。GET DIAGNOSTICS
GET DIAGNOSTICS number_of_rows = ROW_COUNT;
From the documentation:
从文档：
This command allows retrieval of system status indicators. Each item is a key word identifying a state value to be assigned to the specified variable (which should be of the right data type to receive it). The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine, and RESULT_OID, the OID of the last row inserted by the most recent SQL command. Note that RESULT_OID is only useful after an INSERT command into a table containing OIDs.
此命令允许检索系统状态指示器。每个项目都是一个关键字，用于标识要分配给指定变量（应该是正确的数据类型以接收它）的状态值。当前可用的状态项是 ROW_COUNT，最后一个发送到 SQL 引擎的 SQL 命令处理的行数，以及 RESULT_OID，最近的 SQL 命令插入的最后一行的 OID。请注意，RESULT_OID 仅在对包含 OID 的表执行 INSERT 命令后才有用。
回答by user137717
Depends if you need it from the psql CLI or if you're accessing the database from something like an HTTP server. I am using postgres from my Node server with node-postgres. The result set is returned as an array called 'rows' on the result object so I can just do
取决于您是从 psql CLI 需要它还是从 HTTP 服务器之类的东西访问数据库。我正在使用带有 node-postgres 的 Node 服务器上的 postgres。结果集作为结果对象上名为“行”的数组返回，所以我可以这样做
console.log(results.rows.length)
To get the row count.
获取行数。
