主页 / dba / 问题 / 307768
Accepted
x-yuri
Asked: 2022-02-22 02:49:02 +0800 CST

用户类型的属性 51 类型错误

  • 772

我正在尝试将数据从 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。

postgresql insert

1 个回答

  1. Best Answer

    数字对应于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
    • 0

相关问题