I have been trying for hours to populate a Postgres 12 database from a CSV file using the COPY command. The table in question has an hstore field and one of the key-value-pairs has a double quote " in the value.
The Postgres database is created like this
CREATE TABLE test (
title VARCHAR(20),
tags hstore
);
The CSV file uses tabs as delimiters and looks like this
My Title | name=>"""Paul "Butch" Newman""", job=>actor
The name value needs to be double quoted and escaped because it contains spaces.
| represents one tab character.
My import statement looks like this:
COPY test (title, tags)
FROM '/var/lib/postgresql/data/test.csv'
DELIMITER E'\t' CSV ENCODING 'UTF8';
The database is populated as expected, but when I query it with
SELECT tags->'name' as name FROM test;
pgAdmin 4 returns
Paul Butch Newman
instead of
Paul "Butch" Newman
My question is how to get the double quotes into the actual hstore value?
I found many answers regarding double quotes for the import into text fields. But here the import is into an hstore field and the value needs to be double quoted here as it contains spaces.
Thanks!
I just played with this locally.
I assume that you have control over the format of your input file.
The problem is that the CSV format converts "" found within elements enclosed enclosed in double-quotes to " when passing them up.
This worked for me to get the result you want:
My Title | name=>"""Paul \""Butch\"" Newman""", job=>actor
Once the CSV handler is done with its escaping by turning doubled double-quotes to single quotes, it passes name=>"Paul \"Butch\" Newman", job=>actor to the insert, which is how you would write it as a literal in psql or PgAdmin.
select tags->'name' as name
from test_hstor;
name
---------------------
Paul "Butch" Newman
(1 row)
Out of curiosity, I checked on importing it as text instead of csv, and the input record has to contain name=>"Paul \\"Butch\\" Newman", job=>actor to work right.