3

Any idea how this can happen? I have a table which I created by loading data into it via the COPY command. A column in it was declared as VARCHAR(200). I dumped the table, moved it to another computer, and tried to restore it but got an error

pg_restore: [archiver (db)] Error from TOC entry 4154; 0 259261 TABLE DATA foo postgres
pg_restore: [archiver (db)] COPY failed for table "foo": ERROR:  value too long for type character varying(200)
CONTEXT:  COPY foo, line 22556, column a: "สถานีอนามัยเฉลิมพระเกียรติพระบาทส?..."
pg_restore: *** aborted because of error

Update: Interestingly, when I check the length of this record, I get 82, safely less than 200.

SELECT Length(a) FROM foo WHERE a LIKE 'สถานีอนามัยเฉลิมพระเกียรติพระบาทส%'
82

And, yes, the new database is also set with UTF8 encoding.

Improve this question
7
  • Same collations and character types? What does SELECT Length(a, 'UTF8') FROM foo... return? What version of PostgreSQL? Same version on both computers?
    – Mike Sherrill 'Cat Recall'
    Commented Feb 13, 2012 at 3:45
  • hmmm... SELECT Length(a, 'UTF8') FROM foo... gives an error that the function doesn't exist. The source Pg is 9.0.4 and the target Pg is 9.1.2.
    – punkish
    Commented Feb 13, 2012 at 3:56
  • Oh. length(a::bytea, 'UTF8').
    – Mike Sherrill 'Cat Recall'
    Commented Feb 13, 2012 at 4:02
  • also 82. Not a problem there.
    – punkish
    Commented Feb 13, 2012 at 4:05
  • 1
    I think I found the problem (well, a problem). Seems like the target database encoding is SQL_ASCII instead of UTF8. Will fix that and retry.
    – punkish
    Commented Feb 13, 2012 at 4:07

2 Answers 2

Reset to default
1

I got a notification that my answer was migrated to dba.stackexchange.com. But all that migrated was the question. I'll answer again.

Make sure the character types are the same, or at least that they're compatible. An 80-character UTF8 string might be 240 bytes long.

Collation should probably be the same on both computers, too.

Improve this answer
2
  • 1
    But varchar(200) means 200 characters, not 200 bytes.
    – Peter Eisentraut
    Commented Feb 15, 2012 at 21:51
  • 2
    @PeterEisentraut: I understand that, and I understand how, say, varchar(n) accommodates UTF-8 encoding. (Loosely, by handling up to 3*n bytes.) But I'm not sure an encoding like SQL_ASCII is that accommodating.
    – Mike Sherrill 'Cat Recall'
    Commented Feb 15, 2012 at 22:33
1

I found that I had similar issues. pg_dump on one server and pg_restore on another, which produced similar errors. I discovered that all of the error messages seemed to have non-ascii characters in them, which led me to think that it might be an encoding problem.

I recreated the database with the proper encoding

dropdb <mydb>
createdb --encoding='utf-8' <mydb>
pg_restore ...

This solved it for me

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.