PostgreSQL multiple upsert without duplicates rises an error

Question

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).

@GordonLinoff 1507804800 and 1507804680 ?? Please explain it — Anton
– Anton, Commented Oct 12, 2017 at 11:30
First and second are the same, once cast to real: SELECT 1507804800::REAL = 1507804740::REAL result is TRUE — 404
– 404, Commented Oct 12, 2017 at 11:37
@eurotrash indeed. But why? How to fix it? They are not the same! — Anton
– Anton, Commented Oct 12, 2017 at 11:39
I've never used that data type so don't know. Why are you using real anyway? If you stored it as a BIGINT you wouldn't have this problem. — 404
– 404, Commented Oct 12, 2017 at 11:40
I Think timestamp should not be called "timestamp", its data type should be integer or bigint, or maybe even timestamp !! Using a floating point data type as a primary key is ... scrary, at least. — joop
– joop, Commented Oct 12, 2017 at 11:48

klin · Accepted Answer · 2017-10-12 11:40:59Z

1

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.

answered Oct 12, 2017 at 11:40

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

IMSoP · Accepted Answer · 2017-10-12 11:53:40Z

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.

Collectives™ on Stack Overflow

PostgreSQL multiple upsert without duplicates rises an error

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