属性号10超过列数0 [英] Attribute number 10 exceeds number of columns 0

问题描述

此查询成功返回表中的所有行（大约850 +）：

This query returns all the rows(around 850+) from the table successfully:

select * from my_db_log
 where date_trunc('day',creation_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');

但是当我添加 count（*）并使用类似的查询，如下所示：

But when I add the count(*) with the same query like below:

select count(*) from my_db_log
 where date_trunc('day',creation_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');

然后返回我：

********** Error **********

ERROR: attribute number 10 exceeds number of columns 0
SQL state: XX000

仅供参考： creation_date 是表格的第10列。

FYI: creation_date is the 10th column of my table.

有趣的是，当我将 count（*）替换为 count（id）然后它返回我 0 ，但是我的表中有满足条件的记录。

Interestingly, when i replace count(*) with count(id) then it returns me 0 but I have records in my table those meet the condition.

编辑：：我在整个数据库上尝试了 vacuumdb 命令，但仍然对我不起作用。这是此特定表上 vacuumdb 的详细输出。

EDITS: I tried the vacuumdb command over the whole database but still it is not working for me. Here is the verbose output for the vacuumdb on this particular table.

>vacuumdb --full --analyze -h 192.168.1.10 -p 8888 -U root -W --verbose --table my_db_log my_db
Password:

输出：

INFO:  vacuuming "public.my_db_log"
INFO:  "my_db_log": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL:  0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.my_db_log"
INFO:  "my_db_log": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows

推荐答案

您的数据库中某些东西坏了。尝试

Something is broken in your database. Try

VACCUM FULL ANALYZE my_db_log;

或者，更彻底的是，从数据库服务器上的shell运行：

Or, more radically, run from the shell on your db server:

vacuumdb --full --analyze my_database

手册中的详细信息。

错误消息表明系统目录 pg_attribute 或相关索引之一。在执行其他操作之前，请阅读Postgres Wiki中的 腐败。请务必小心，不要丢失有价值的数据。

然后尝试另一件事：

The error message indicate breakage in the system catalog pg_attribute or one of the associated indices. Before you do anything else, read about corruption in the Postgres Wiki. Be very careful not to lose valuable data.
Then one other thing to try:

reindexdb --system my_database

如果没有帮助，修复明显损坏的数据库，您可以尝试 pg_dumpall 整个集群，将其删除，创建一个新集群并还原备份。还要确保找出导致数据库崩溃的原因。那通常不会发生（但是我从未发生过）。您可能会面临严重的硬件问题，在这种情况下，您需要尽快采取行动...

If nothing helps, to repair your obviously broken DB, you could try to pg_dumpall the whole cluster, drop the cluster, create a new cluster and restore the backup. Also make sure you find out what broke your db. That does not usually happen (never happened to me, yet). Chances are, you are facing serious hardware problems, in which case you need to act soon ...

这篇关于属性号10超过列数0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！