How to insert data with custom enum type from a csv into an existing PostgreSQL table

I want to insert data from a csv file into an existing table on a PostgreSQL database – let’s call the table automobile.

One of my field is a custom enum – let’s call it brand.

When I try to import records from a csv file with DataGrip built-in feature I got an error message:

conversion failed: "Ford" to brand

Yet in the following screenshot we can see that the manufacturer column is filled with the expected string value : Ford.

I guess I have to explicitly cast the string as being of brand type but how can I do this? I don’t see any options of this kind in the import options. (see below). Should I do it in the csv file directly?

Reproduction steps

CREATE TYPE brand AS ENUM ('BMW', 'Renault', 'Ford');

You can check you have declared an enum type with:

SELECT enum_range(NULL::brand);

I create a dummy table with a manufacturer column being of type brand.

CREATE TABLE automobile (
id int,
manufacturer brand,
registration_number varchar(10),
owner varchar(50));

And insert some dummy records into it:

INSERT INTO automobile (id, manufacturer, registration_number, owner)
VALUES (1, 'BMW', 'AAA-BBB-ZR', 'John'),
       (2, 'Renault', 'CCC-BWB-PU', 'Mike');

Now I try to import data from a csv file by right-clicking on my table and selecting Import data from File… The csv file I select for import contains the following 2 lines:

id, manufacturer, registration_number, owner
3,Ford,PZB-URU-LK,Jack

I select the right encoding option and click OK. Then an error message is generated:

2:3: conversion failed: "Ford" to brand

If I select the option Insert inconvertible values as Null while checking the DataGrip import option, I got a more verbose error message:

2:1: ERROR: column "manufacturer" is of type brand but expression is of type character varying   Hint: You will need to rewrite or cast the expression.   Position: 90

I tried to specify Ford::brand in my csv and reload and got the same issue.

How can I make DataGrip understand that Ford is a value of the brand enum?

I already checked DataGrip documentation on Import/Export but couldn’t find what I was looking for.

Answer

The latest DataGrip version handles enum types as expected. The issue is fixed.

Advertisement

Answer