0

I have a very simple table where I would like to insert data from a CSV into it.

create table products(
  id integer primary key,
  images text[]
);

Here is what I am currently trying with my csv:

1,"['hello.jpg', 'world.jpg']"
2,"['hola.jpg', 'mundo.jpg']"

When I do the following, I get a syntax error from psql, with no additional information what could have gone wrong.

\copy products 'C:\Users\z\Downloads\MOCK_DATA.csv' WITH DELIMITER ',';

Does anyone know how to format my array values properly?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Dan Zuzevich
  • 3,651
  • 3
  • 26
  • 39
  • Could you please share the error message? – Frank Heikens Aug 07 '22 at 17:47
  • All it says is "Syntax error" lol. Nothing else – Dan Zuzevich Aug 07 '22 at 18:36
  • 'All it says is "Syntax error" lol. Nothing else'. Never seen that happen. Sounds like you client throws away the detailed error message. What is your client program? You say psql, but again I've never seen that. Maybe throw the client away. – jjanes Aug 07 '22 at 20:30

1 Answers1

0

If you remove the square brackets from the csv file then I would have the table like this (images as text rather than text[]):

create table products_raw
(
  id integer primary key,
  images text
);

plus this view

create view products as 
  select id, ('{'||images||'}')::text[] as images 
  from products_raw;

and use the view henceforth. Anyway I would rather have the CSV file like this, no formatting, just data:

1,"hello.jpg,world.jpg"
2,"hola.jpg,mundo.jpg"

It is also worth considering to attach the csv file as a foreign table using file_fdw. It is a bit more complicated but usually pays off with several benefits.

Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21