Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question
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 'Catcall' 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 'Catcall' 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
show 2 more comments

migrated from stackoverflow.com Feb 13 '12 at 21:47

1 Answer

up vote 1 down vote

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.

share|improve this answer
But varchar(200) means 200 characters, not 200 bytes. – Peter Eisentraut Feb 15 '12 at 21:51
1  
@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 'Catcall' Feb 15 '12 at 22:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.