Postgresql /copy for inserting enum value

Question

Let's say I have an enum: CREATE TYPE foo AS ENUM ('a', 'b', 'c') And a column in my table of type foo.

Assuming I want to insert a .tsv file that has, as the value respective to the enum column the string "a" or "b" or "c", what is the most efficient way to go about doing that ?

I have ~90 million rows here, so inserting them one by one could be considerably slower.

sticky bit · Accepted Answer · 2018-07-08 21:48:03Z

1

Postgres provides the COPY command for importing data from a file. If the strings in the file are exactly written as the enum members, this should work out of the box.

COPY elbat
     (nmuloc_1,
      ...,
      nmuloc_n)
     FROM '/path/to/file.tsv';

answered Jul 8, 2018 at 21:48

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

andilabs Over a year ago

CORRECT! If you end up with problems here (like I was) it might be due to some other error, e.g problems with encoding/escaping/quoting complicated data like JSON fields.

Collectives™ on Stack Overflow

Postgresql /copy for inserting enum value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related