CODEWITHSUNDEEP
sqlpostgresqlupsert
Anton
Anton

Reputation: 450

PostgreSQL multiple upsert without duplicates rises an error

I use PostgreSQL 9.5, Ubuntu 16.04

I have an empty table:

CREATE TABLE IF NOT EXISTS candles_1m(
   timestamp      REAL PRIMARY KEY,
   open      REAL,
   close      REAL,
   high      REAL,
   low      REAL,
   volume      REAL
);

Then I try to do multiple upsert (without duplicates of 'timestamp' - the primary key):

INSERT INTO candles_1m (
  timestamp, open, close, high, low, volume
  ) VALUES 
  (1507804800, 5160, 5158.7, 5160, 5158.7, 5.40608574), 
  (1507804740, 5157.5, 5160, 5160, 5156.1, 39.03357813), 
  (1507804680, 5156.5, 5157.4, 5157.4, 5156, 33.54458319), 
  (1507804620, 5151.3, 5156.5, 5157.5, 5151.2, 19.75590599)
  ON CONFLICT (timestamp)
  DO UPDATE SET
        open = EXCLUDED.open,
        close = EXCLUDED.close,
        high = EXCLUDED.high,
        low = EXCLUDED.low,
        volume = EXCLUDED.volume;

And I received and error:

ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

I do not understand why? I don't have duplicates there! But my next step will be create a request that will add (or update) each of rows step by step (independent of duplicates exist).

Upvotes: 1

Views: 616

Answers (2)

IMSoP
IMSoP

Reputation: 97718

As others have pointed out, this is because the two values you have entered are being truncated to the same value when casting to REAL.

Why?

Because floating point numbers don't have uniform precision over their range - close to zero, they can represent very small fractions more accurately, and far from zero, they can represent very large values inaccurately. Your values are higher than the range where every integer is represented precisely, so your values are effectively rounded to the nearest representable value whenever you insert them.

Note that this isn't just a problem of duplicates, you are actually losing data every time you insert into that table.

How to fix it?

By picking a more suitable data type for your column. If your timestamps never have decimal components, a BigInt might be appropriate; otherwise, read up on the precision limits of different widths of floating point numbers. Or possibly you should be casting them to an appropriate date/time type instead, perhaps using to_timestamp.

Upvotes: 1

klin
klin

Reputation: 121594

From the documentation:

real 4 bytes variable-precision, inexact 6 decimal digits precision

You have two pairs of equal real values:

select 
    1507804800::real = 1507804740::real as r1r2, 
    1507804680::real = 1507804620::real as r3r4

 r1r2 | r3r4 
------+------
 t    | t
(1 row)

Use a type with a better precision.

Upvotes: 1

Related Questions