# Importing array values into Postgres from CSV

tags: postgres

I want to import from a CSV file some array values into a Postgres table, but I didn’t find examples. There are many examples with INSERT statements, but I already know how to do that (because there are examples).

Postgres allows a column to hold multiple values (or almost anything, really). In text, you put array values in braces and separate them with commas. There are plenty of examples of this:

No big whoop. Now I have this file in import_test.csv:

It’s as easy as I thought it would be. That column in the CSV uses the same syntax I used in the INSERT:

What about elements with commas in them? This works too:

An empty field was harder. Two commas in a row ("{rat,,mouse}") doesn’t work, but quotes around the empty field work:

Single quotes work around an array element work too:

What if I swap those quotes around so the single quotes are on the outside? This is a bit silly because most CSV tools are going to use “, but I figured I’d try:

The default quote is a “ so this won’t work:

I can change the quote but it’s a bit odd. At first I thought that I could quote the quote, like "'", but that doesn’t work. Nor does "\'" nor various things like that. Inside the single ticks, I double up the single tick:

But Postgres also has dollar quoting, where I form the quote delimiter with double $ on each side, with an optional tag between the $: