On Fri, Apr 17, 2015 at 11:56 AM, David G. Johnston < david.g.johns...@gmail.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://www.postgresql.org/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: rgac...@appropriatesolutions.com skype: ray.cote
