Import CSV in postgresql without duplicate rows

Question

My query duplicates rows when I import CSV file in to table:

+----------+----------------+---------------------+
| fullname | formattedvalue | recordTime          |
+----------+----------------+---------------------+
| text1    | 170.01346      | 09/02/2020 21:45:00 |
+----------+----------------+---------------------+
| text2    | 24.153432536   | 09/02/2020 21:45:00 |
+----------+----------------+---------------------+
| text3    | 3.583432424    | 09/02/2020 21:45:00 |
+----------+----------------+---------------------+
| text1    | 170.01346      | 08/02/2020 21:45:00 |
+----------+----------------+---------------------+
| text2    | 24.153432536   | 08/02/2020 21:45:00 |
+----------+----------------+---------------------+
| text3    | 3.583432424    | 08/02/2020 21:45:00 |
+----------+----------------+---------------------+

And the Query:

CREATE TEMP TABLE tmp_x
(
"fullname" varchar,
"formattedvalue" double precision,
"recordtime" timestamp
);

COPY tmp_x FROM PROGRAM 'more +1 "D:\MEAS_20200308x.csv"' (FORMAT csv, DELIMITER ',');

--UPDATE tmp_x
--SET    formattedvalue = ROUND( CAST(formattedvalue as numeric), 3 );

insert into meas_kanal select * from (
select x.*
from tmp_x x
left outer join meas_kanal t on t.fullname = x. fullname AND t. recordtime = x. recordtime
where t. fullname is null AND  t. recordtime is null
) as missing;


DROP TABLE tmp_x;

My logic is to check for duplicates on cloumn combination: fullname + recordtime

When I start the query again, it is inserting the same rows again.

Any idea where I am wrong?

EDIT 2:

I tried and this with the same problem:

INSERT INTO meas_kanal
SELECT x.*
FROM tmp_x x
    LEFT OUTER JOIN meas_kanal t ON (t. fullname = x. fullname AND t. recordtime = x. recordtime)
WHERE t.fullname IS NULL AND t. recordtime IS NULL;

EDIT 3: One more fail.

INSERT INTO meas_kanal
SELECT *
FROM tmp_x
WHERE NOT EXISTS(SELECT * 
                 FROM meas_kanal 
                 WHERE (tmp_x.fullname=meas_kanal.fullname and
                       tmp_x.recordtime=meas_kanal.recordtime)
                 );

I think that the problem is somewhere else.

Edit 4: Possible solution

BTW I forgot to mention. I don't have a primary key. Now I make two:

CREATE TEMP TABLE tmp_x
(
"fullname" varchar,
"formattedvalue" double precision,
"recordtime" timestamp,
UNIQUE (fullname, recordtime)
);

And this insert:

insert into meas_kanal(fullname, formattedvalue,recordtime) 
    SELECT fullname, formattedvalue,recordtime FROM tmp_x x
ON CONFLICT DO NOTHING;

For now it's working like I expect. I will write this solution as an answer if no one gives a better solution.

Is the table in your question how you raw table looks before any query? Can you tell me what columns are found in tmp_x? I'm assuming there is an id column, correct? — SILENT
– SILENT, Commented Mar 12, 2020 at 7:57

Hristian Yordanov · Accepted Answer · 2020-04-29 10:29:44Z

2

I make two primary keys:

CREATE TEMP TABLE tmp_x
(
"fullname" varchar,
"formattedvalue" double precision,
"recordtime" timestamp,
UNIQUE (fullname, recordtime)
);

And this insert:

insert into meas_kanal(fullname, formattedvalue,recordtime) 
    SELECT fullname, formattedvalue,recordtime FROM tmp_x x
ON CONFLICT DO NOTHING;

For now it's working like I expect.

answered Apr 29, 2020 at 10:29

Hristian Yordanov

6981 gold badge7 silver badges30 bronze badges

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

Comments

SILENT · Accepted Answer · 2020-03-12 17:23:54Z

0

You could use GROUP BY or DISTINCT

Assuming your current query results with the table you displayed

INSERT INTO meas_kanal
SELECT DISTINCT x.fullname, x.formattedvalue, x.recordtime
FROM tmp_x x

Update 1

Based on your recent comments, if you want the latest recordtime to be inserted, then use GROUP BY

INSERT INTO meas_kanal
SELECT x.fullname, x.formattedvalue, MAX(x.recordtime) recordtime
FROM tmp_x x
GROUP BY x.fullname, x.formattedvalue

edited Mar 12, 2020 at 17:23

answered Mar 10, 2020 at 16:50

SILENT

4,2967 gold badges45 silver badges67 bronze badges

Collectives™ on Stack Overflow

Import CSV in postgresql without duplicate rows

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related