attribute 51 of type users has wrong type

Question

I'm trying to import data to a pg table from a csv file. The error I get is:

$ 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

How do I know which attribute is it about? For that matter an INSERT also fails:

$ 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.

I tried to count according to the \d users output, and to the attnum value:

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

I'm running PostgreSQL 12.7.

Answer 1

The number corresponds to the pg_attribute.attnum value. I followed the following advice to reorder columns. It appears indexes refer to columns using attnum values. In other words, after reordering columns, they started to refer to other columns, which led to the error.

To reproduce the issue (script), create a table:

> 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

Reorder columns:

> 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

Try to insert a row:

> 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)

As you can see t1_f2_idx now refers to f3 (in the table it's an integer, in the index a string).

At least in this case it's reversible:

> 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