2

I used csvsql to generate CREATE statements, and it looks like it assumed trimmed fields though the data has some spaces padding the field:

ERROR:  value too long for type character varying(11)
CONTEXT:  COPY example, line 1314, column state: " South Korea"

I'm not entirely sure why the data has padding -- it definitely shouldn't but about half the values in the state column have some padding around them.

I could use ALTER to make more space in the field, and then UPDATE example SET state=trim(state); after I COPY the csv in, I'm wondering if there's a clean way to just trim the padding as I import the data?

2
  • Does adding --skipinitialspace help? Also, I'd just pick the CREATE statements, adjust them to my needs (for example, one could remove the limit from varchar - see the tip under postgresql.org/docs/10/static/datatype-character.html) and then just copy the data (not fully sure how it's done with csvsql).
    – dezso
    Mar 5, 2018 at 9:56
  • --skipinitialspace doesn't help, unfortunately.
    – Amanda
    Mar 5, 2018 at 19:32

1 Answer 1

1

Possibly the --no-constraints option to csvsql may solve that problem simply.

Otherwise COPY has a PROGRAM clause that can pre-process the data. It receives the lines it on its standard input and must output the filtered result on its standard output.

https://www.postgresql.org/docs/current/static/sql-copy.html

PROGRAM

A command to execute. In COPY FROM, the input is read from standard output of the command, and in COPY TO, the output is written to the standard input of the command.

When called from psql with \copy tablename FROM PROGRAM ... the program refers to a client-side command to launch by psql. When COPY is used, it's a server-side command run by the backend.

But the trimming itself is non-trivial. For instance, see How to manipulate a CSV file with sed or awk? and Trim leading and trailing spaces from a string in awk.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.