attribute 51 of type users has wrong type



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



  • The number corresponds to the pg_attribute.attnum value. I followed https://stackoverflow.com/a/656841/52499 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 ( https://gist.github.com/x-yuri/8c207b1ab7a3cbc81b40550a55acecd2 ), 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2