使用者類型的屬性 51 類型錯誤

我正在嘗試將數據從 csv 文件導入 pg 表。我得到的錯誤是：

$ psql -U postgres -c "COPY users (first_name) FROM '/users.csv' (FORMAT csv)"
ERROR:  attribute 51 of type users has wrong type
DETAIL:  Table has type integer, but query expects character varying.
CONTEXT:  COPY users, line 1

我怎麼知道它是關於哪個屬性的？就此而言， INSERT 也失敗了：

$ psql -U postgres -c "INSERT INTO users (first_name) VALUES ('first_name')"
ERROR:  attribute 51 of type record has wrong type
DETAIL:  Table has type integer, but query expects character varying.

我試圖根據\d users輸出和attnum值進行計數：

SELECT a.*
FROM pg_attribute a
   JOIN pg_class c on a.attrelid = c.oid
   JOIN pg_namespace n on c.relnamespace = n.oid
WHERE n.nspname = 'public'
   AND c.relname = 'users'
   AND attnum >= 1
ORDER BY attnum

我正在執行 PostgreSQL 12.7。

數字對應於pg_attribute.attnum值。我按照以下建議對列重新排序。似乎索引是指使用attnum值的列。換句話說，在重新排序列之後，他們開始引用其他列，這導致了錯誤。

要重現問題（腳本），請創建一個表：

> CREATE TABLE t1 (f1 varchar, f2 varchar, f3 integer)
CREATE TABLE

> CREATE INDEX ON t1 (f2) WHERE f2 IS NOT NULL
CREATE INDEX

> \d t1
                     Table "public.t1"
Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
f1     | character varying |           |          | 
f2     | character varying |           |          | 
f3     | integer           |           |          | 
Indexes:
   "t1_f2_idx" btree (f2) WHERE f2 IS NOT NULL

重新排序列：

> UPDATE pg_attribute SET attnum = 30001 WHERE attname = 'f2' AND attrelid = 16384
UPDATE 1

> UPDATE pg_attribute SET attnum = 30002 WHERE attname = 'f3' AND attrelid = 16384
UPDATE 1

> UPDATE pg_attribute SET attnum = 3 WHERE attname = 'f2' AND attrelid = 16384
UPDATE 1

> UPDATE pg_attribute SET attnum = 2 WHERE attname = 'f3' AND attrelid = 16384
UPDATE 1

> \d t1
                     Table "public.t1"
Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
f1     | character varying |           |          | 
f3     | integer           |           |          | 
f2     | character varying |           |          | 
Indexes:
   "t1_f2_idx" btree (f3 COLLATE "default" text_ops) WHERE f3 IS NOT NULL

嘗試插入一行：

> INSERT INTO t1 (f1) VALUES ('f1')
ERROR:  attribute 2 of type record has wrong type
DETAIL:  Table has type integer, but query expects character varying.

> SELECT a.attname, a.attnum
FROM pg_attribute a
   JOIN pg_class c on a.attrelid = c.oid
   JOIN pg_namespace n on c.relnamespace = n.oid
WHERE n.nspname = 'public'
   AND c.relname = 't1'
   AND attnum >= 1
ORDER BY attnum
attname | attnum 
---------+--------
f1      |      1
f3      |      2
f2      |      3
(3 rows)

如您所見t1_f2_idx，現在指的是f3（在表中它是一個整數，在索引中是一個字元串）。

至少在這種情況下它是可逆的：

> UPDATE pg_attribute SET attnum = 30001 WHERE attname = 'f2' AND attrelid = 16384
UPDATE 1

> UPDATE pg_attribute SET attnum = 30002 WHERE attname = 'f3' AND attrelid = 16384
UPDATE 1

> UPDATE pg_attribute SET attnum = 2 WHERE attname = 'f2' AND attrelid = 16384
UPDATE 1

> UPDATE pg_attribute SET attnum = 3 WHERE attname = 'f3' AND attrelid = 16384
UPDATE 1

> INSERT INTO t1 (f1) VALUES ('f1')
INSERT 0 1

引用自：https://dba.stackexchange.com/questions/307768