Postgresql drops trailing zeroes when loading time with milliseconds from csv

Question

I am importing a csv file into a Postgres Table. The file has the following format:

2019/12/13, 14:56:02, 3172.50, 3174.25, 3172.50, 3172.50, 1, 1, 1, 0

The table is defined as:

CREATE TABLE tablename (
date date,
time time,
v1 numeric,
v2 numeric,
v3 numeric,
v4 numeric,
v5 integer,
v6 integer,
v6 integer,
v7 integer,
PRIMARY KEY(date, time)
);

There is an issue with the time field. In some cases, milliseconds are added for precision:

14:56:02.1
14:56:02.9
14:56:02.10

Unfortunately, Postgres seems to drop the trailing zero, which causes it to mark below two values as duplicates:

14:56:02.1
14:56:02.10

ERROR:  duplicate key value violates unique constraint "tablename_pkey"
DETAIL:  Key (date, "time")=(2019-12-13, 14:56:02.1) already exists.
CONTEXT:  COPY input_file, line 1584

Is there a way to instruct psql not to drop trailing zeroes? I tried time(4) to enforce 4 digit precision, with no difference.

Thanks!

Answer 1

Postgres is not doing anything wrong here. It took me a moment to realize that the issue is with the data.

.1 and .10 are equal. In the data, the timestamp was used creatively, i.e. in this case .1 means "1st record within this second" and .10 means "10th record within this second", so the millisecond component didn't make sense from timestamp's point of view.