Postgres COPY into hstore field with double quote in value

Question

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!

Mike Organek Mike Organek · Accepted Answer · 2020-06-27T15:56:03

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.

Postgres COPY into hstore field with double quote in value

1 Answers