postgres error: a negative number raised to a non-integer power yields a complex result

Question

The given query works perfectly fine when executed with DataGrip, but returns the following error when it's submitted by lib/pq:

pq: a negative number raised to a non-integer power yields a complex result

The error is happening within postgres as it shows up in the logs

SELECT upvotes / (EXTRACT(EPOCH FROM current_timestamp-created_at)/3600)^1.8 as score, title,
FROM ideas
ORDER BY score desc

simplified schema

create table ideas
(
   title text not null,
   created_at timestamp not null,
   upvotes integer default 0 not null
)

I'm running postgres v9.2

I'd be very happy about a little hint in where to look into, as I'm out of ideas.

Check which created_at values are in the future.

melpomene
– melpomene

2018-06-02 05:35:45 +00:00
Commented Jun 2, 2018 at 5:35 — melpomene
– melpomene, Commented Jun 2, 2018 at 5:35
yeah, just resolved it, was indeed the issue, thanks man!

21st
– 21st

2018-06-02 05:37:09 +00:00
Commented Jun 2, 2018 at 5:37 — 21st
– 21st, Commented Jun 2, 2018 at 5:37

Jasen · Accepted Answer · 2018-06-02 11:23:06Z

1

So, you've got some timestamps in the future somehow. you can protect the query to ensure that there's no negative results using GREATEST

SELECT upvotes / (GREATEST(EXTRACT(EPOCH FROM current_timestamp-created_at)/3600),0)^1.8 as score, title,
FROM ideas
ORDER BY score desc

Alternatively you can exclude the bad data using a WHERE clause.

SELECT upvotes / (EXTRACT(EPOCH FROM current_timestamp-created_at)/3600)^1.8 as score, title,
FROM ideas
WHERE current_timestampn >= created_at
ORDER BY score desc

answered Jun 2, 2018 at 11:23

Jasen

12.5k2 gold badges37 silver badges50 bronze badges

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

1 Comment

21st Over a year ago

cheers! there was indeed a future timestamp in one of the rows

Collectives™ on Stack Overflow

postgres error: a negative number raised to a non-integer power yields a complex result

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related