Repository navigation
Error in importing pls_fy2015_pupld14a #2
Description
Apparently you didn't size the text columns correctly:
postgres=# \copy pls_fy2014_pupld14a from pls_fy2014_pupld14a.csv with csv header
ERROR: value too long for type character varying(60)
CONTEXT: COPY pls_fy2014_pupld14a, line 7400, column libname: "FLORIDA MUNICIPAL ELECTRONIC LIBRARY (LUIS G. PÉREZ VÁZQUEZ)"
Time: 77.828 ms
Activity
jberkus commentedon Jun 14, 2017
Changing it to varchar(80) solves the problem, but shouldn't it just be TEXT?
anthonydb commentedon Jun 14, 2017
Hmm! Some oddness here:
libname: https://www.imls.gov/sites/default/files/fy2014_pls_data_file_documentation.pdfAny thoughts?
jberkus commentedon Jun 14, 2017
Yep.
I've been spinning up my test DBs in containers, and looking at this one, the encoding didn't set right for the guest OS, so that database is SQL_ASCII. This means that the accent characters are being stored as unicode escapes, taking up more than one character, and since there's no characters to spare ...
This would be a "just me" problem, except that I know that several OSes default PostgreSQL to SQL_ASCII if there's an issue with matching encodings on the host, both on Linux and on Mac. So if the book goes out with the column length unchanged, you will get this same error from a number of readers. Better to just change the length.
jberkus commentedon Jun 14, 2017
The alternative is guiding readers through making certain their databases are properly unicode, and do you really want to do that?
anthonydb commentedon Jun 14, 2017
That ... is something I don't want to do for this audience.
Do you recommend using
textovervarcharacross the board, even if you're dealing with, say, a two-letter code that would not contain extended characters?jberkus commentedon Jun 14, 2017
No, not for two-letter codes. Just for longer text where the length spec is fairly arbitrary and is subject to change. If you think about it, varchar(x) is an anachronism; why is the max length more significant than the minimum length? or allowed characters? or a format mask? The reason it's part of the spec is that databases back in 1989 used fixed-length storage for fields, and is thus a historical violation of the separation of logical and physical models (primary keys are another such violation, and Sybase's attempt to change this in 1995 failed).
HOWEVER, I'm not recommending that you switch to TEXT for the book, because the TEXT type is not portable across database systems. For example, MySQL's TEXT is CLOB storage, and behaves very differently than VARCHAR in that database. So (as I noted in chapter 2, I think), it's a good idea to advise readers that they might want to use TEXT across the board for variable-length fields if they;re using PostgreSQL, but don't do it for all the examples etc. for the book.
anthonydb commentedon Jun 14, 2017
OK, that's what I thought. Thanks for clarifying. And thanks for all your tremendous help in general!
anthonydb commentedon Jun 26, 2017
Columns re-sized as part of chapter edits in 6fe34ac