Getting duplicate primary key errors when using H3INDEX as a PK

[asitemade4u]

Hi Zach,

So, following the excellent article of @rustprooflabs I am trying to populate a Postgis table. Here is the table definition:

CREATE TABLE h_3.h3x
(
    ref H3INDEX NOT NULL PRIMARY KEY,
    res INT2 NOT NULL,
    geom GEOMETRY (MULTIPOLYGON, 4326) NOT NULL,
    
    CONSTRAINT ck_resolution CHECK (res >= 0 AND res <= 15)
);
CREATE INDEX h3x_hex ON h_3.h3x USING GIST (geom);

Following his advice, I am using for primary key (ref) the H3INDEX.

However, when populating the table from another table with geometry, I get an error about primary key uniqueness:

[23505] ERROR: duplicate key value violates unique constraint "tst_pkey".

And yet, I have exported the values I intend to import to a CSV file and found no duplicate in the H3 reference column ref...

So, I have two questions:

1. How come I get a unicity error when no reference is alike?
2. Do you discourage using H3INDEX as a primary key?

[zachasme]

Hi @asitemade4u, thanks for the bug report! It is my intention to allow H3Index as primary key, so this should not be happening.

I'll try looking into it myself, but would it be possible for you to provide the csv file with the values that trigger the violation?

[zachasme]

Hello again @asitemade4u! I'm having trouble reproducing the problem. It would be a big help if you could send me a sample CSV that produces the error.