CODEWITHSUNDEEP
postgresql
wykopowiedz1
wykopowiedz1

Reputation: 103

How to change a default separator for postgresql arrays?

I want to import csv with Postgres' arrays into a Postgres table.

This is my table:

create table dbo.countries (
    id char(2) primary key,
    name text not null,
    elements text[]
    CONSTRAINT const_dbo_countries_unique1 unique (id),
    CONSTRAINT const_dbo_countries_unique2 unique (name)
);

and I want to insert into that a csv which looks like this:

AC,ac,{xx yy}

When I type copy dbo.mytable FROM '/home/file.csv' delimiter ',' csv; then the array is read as a one string: {"xx yy"}.

How to change a deafault separator for arrays from , to ?

Upvotes: 0

Views: 757

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45885

You cannot to change array's separator symbol. You can read data to table, and later you can run a update on this table:

UPDATE dbo.countries
  SET elements = string_to_array(elements[1], ' ')
 WHERE strpos(elements[1], ' ') > 0;

Upvotes: 1

Related Questions