Re: On using doubles as primary keys

From: Ray Cote <rgacote(at)appropriatesolutions(dot)com>
To: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Cc: Melvin Davidson <melvin6925(at)gmail(dot)com>, Kynn Jones <kynnjo(at)gmail(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: On using doubles as primary keys
Date: 2015-04-17 18:10:38
Message-ID: CAG5tnzqNhxctLH_OaqsBdKAmoxmLEWrkuNeROrk+hhPJFR4KhQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Apr 17, 2015 at 11:56 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> ​
> MD>>
> ​
> I'm not sure what you mean by doubles. Do you mean bigint data type, or do
> you mean use two columns for a primary key? Either way it's pretty simple.
> ​
> MD>>
> ​
> If you mean a bigint, then probably best to use serial data type, which
> will default to the next value.
> ​
> MD>>
> ​
> If you mean use two columns for a Primary Key, the you just specify the
> columns.
> ​
> MD>>
> ​
> EG: CONSTRAINT PRIMARY KEY table_name_pk PRIMARY KEY (col1, col2)
>
> ​I take it the OP means "double precision" ​
>
> http://stream.boxwatch.ru/docs/9.0/static/datatype-numeric.html
>
> I'd be suspect of choosing a PK whose type definition includes the word
> "inexact".
>
> You also say you want to store the data as a double precision but you
> never actually explain what the natural key of the data is.
>
> If you are strictly storing serial (big) integers but doing so within a
> double precision typed column you may be OK - but I would advise taking
> input from someone with more expertise on the properties of IEEE floating
> point numbers.
>
> David J.
>

(Not an IEEE floating point expert, but...) I've learned the hard way to
never rely on comparing two floating point numbers for equality -- and
that's what you are doing if you join on them as primary keys. If you must
use the underlying numeric data for joining, I'd recommend you do something
like:
* output the value to a string (rounded to a specific number of digits)
* use the textual representation of the number as your primary key.

Alternatively, if the numbers are very large or very small, you may want to
try converting the binary data into a BINHEX value and use that textual
representation as the primary key.

Either way, I think you'd be saving yourself a lot of headaches using a
representation that is not 'inexact.'
--Ray

--
Raymond Cote, President
voice: +1.603.924.6079 email: rgacote(at)AppropriateSolutions(dot)com skype:
ray.cote

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Nasby 2015-04-17 18:38:27 Re: On using doubles as primary keys
Previous Message Qingqing Zhou 2015-04-17 18:10:16 Re: Waiting on ExclusiveLock on extension