2

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.

  • 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' Feb 13 '12 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 Feb 13 '12 at 3:56
  • Oh. length(a::bytea, 'UTF8'). – Mike Sherrill 'Cat Recall' Feb 13 '12 at 4:02
  • also 82. Not a problem there. – punkish Feb 13 '12 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 Feb 13 '12 at 4:07
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.

  • 1
    But varchar(200) means 200 characters, not 200 bytes. – Peter Eisentraut Feb 15 '12 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' Feb 15 '12 at 22:33
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy