删除列不会完全删除列引用-Postgresql [英] Drop column doesn't remove column references entirely - postgresql

问题描述

我有一个表，其中包含1600列，并希望在其中添加更多字段，但是根据数据库规则，由于达到了更高的限制，不允许创建更多字段。

I have a table that contained 1600 columns and would like to add more fields in that but as per the database rule the more fields not allowed to be created because the higher limit reached.

所以我决定从表中删除一些不需要的字段，然后做到了。再次，我尝试在该表中添加几个字段，但它会引发相同的错误，即那里有1600列您不能添加更多。

So I decided to drop few unwanted fields from the table and I did it. Again I tried to add few fields in that table but it's raise the same error that 1600 columns are there you can't add more.

我浏览了其他Postgresql表 pg_attribute ，所有这些字段都在其中，并且删除参数为True。

I gone through other tables of postgresql "pg_attribute" and all those fields are there and having delete parameter = True.

我做什么到目前为止已经尝试过的

将约束放到另一个表中截断表
重新创建约束将数据重新复制到主表中。

Drop Constraints Take table data into another table Truncate Table Re-Create Constraints Re-Copy data to the main table.

但是pg_attributes表中仍然有删除的列。

But still the dropped columns are there in pg_attributes table.

我还尝试从 pg_attribute ，但随后
却给了我这样的错误。

I also tried to remove that records from pg_attribute but then it gives me error like that.

ERROR:  catalog is missing 1 attribute(s) for relid 208996

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

ERROR: catalog is missing 1 attribute(s) for relid 208996
SQL state: XX000

W hy，我们有这么多列，原因是

我们有odoo-magento（odoo使用的PostgreSQL数据库）集成和magento中的
超过60000种产品并具有3000个独特的
属性，因此在连接器中，所有这些属性都被创建为product表中的
字段，并且我已经同步了magento-> odoo产品，并且达到了
1600个限制。现在，我不允许从该
表中删除字段。

We have odoo-magento (postgresql db in used by odoo) integration and in magento there are more than 60000 products and having 3000 unique attributes, so in connector all these attributes are created as fields in product table and I already synced magento -> odoo products and 1600 limit reached. Now it won't me allow to drop fields from that table.

我已修复了magento-odoo连接器中的该问题，它将仅
个同步所需的属性，但是对于已同步的
字段我该怎么办？我可以删除它吗？

I have fixed that issue in magento-odoo connector and it will only sync required attributes but what should I do for already synced fields how can I remove it ????

即使丢掉桌子也不是正确的方法，因为它是巨大的而且非常重要的
表，因此我可以冒险。

Even dropping a table is not a proper way because it's huge and very important table so I can take any risk with that.

我想做什么

我只想从表中删除这些列，并且需要

I want to simply remove those columns from the table and will need to be added few other columns instead.

此问题还有其他可能的解决方法吗？

Is there any other possible solution for this issue ?

任何帮助将不胜感激。

推荐答案

永远不要弄乱 pg_attribute 直接。如果您这样做了，可能是时候从备份还原了。

Never mess with pg_attribute directly. If you have done so, it is probably time to restore from a backup.

删除列后，PostgreSQL实际上不会删除它，而是会更改名称和标记。

When a column is dropped, PostgreSQL does not actually remove it, but changes the name and marks it as dropped.

CREATE TABLE testtab (
   id integer PRIMARY KEY,
   dropme text NOT NULL,
   val text NOT NULL
);

ALTER TABLE testtab DROP dropme;

SELECT attname, attnum, attisdropped
FROM pg_attribute
WHERE attrelid = 'testtab'::regclass
   AND attnum > 0
ORDER BY attnum;

┌──────────────────────────────┬────────┬──────────────┐
│           attname            │ attnum │ attisdropped │
├──────────────────────────────┼────────┼──────────────┤
│ id                           │      1 │ f            │
│ ........pg.dropped.2........ │      2 │ t            │
│ val                          │      3 │ f            │
└──────────────────────────────┴────────┴──────────────┘
(3 rows)

所以我想删除的列仍会计入列限制。

So I guess that dropped column still counts towards the column limit.

我可以想到一种摆脱这种情况的方法：

I can think of one, not very comfortable, way to get rid of that:

BEGIN;
CREATE TABLE testtab_2 (LIKE testtab INCLUDING ALL);
INSERT INTO testtab_2 SELECT * FROM testtab;
DROP TABLE testtab;
ALTER TABLE testtab_2 RENAME TO testtab;
COMMIT;

这篇关于删除列不会完全删除列引用-Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！