Numbers

Bob Pawley <rjpawley@shaw.ca> writes:

My initial tables have columns containing values such as 12 feet.

I want to perform calculations.

Is there a method in Postgresql to separate the 12 from the unit feet or
am I forced to make two columns to separate the feet from the 12?

Are you storing '12 feet' as a string? That seems awfully unstructured
for data that you'd like to do calculations on.

I seem to recall that someone had come up with a datatype that would
store numbers with units attached, which seems like what you want here.
Check the PG list archives, and/or poke around on pgfoundry and gborg.

regards, tom lane

Hi Tom

I found a datatype called 'interval' which seems to separate time from its
unit.

Is that what you were thinking of??

Bob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, January 22, 2006 11:25 AM
Subject: Re: [GENERAL] Numbers

On Sun, Jan 22, 2006 at 02:47:51PM -0800, Bob Pawley wrote:

I found a datatype called 'interval' which seems to separate time from its
unit.

Is that what you were thinking of??

I think Tom might be referring to Martijn van Oosterhout's tagged
types:

http://archives.postgresql.org/pgsql-general/2005-12/msg00850.php

--
Michael Fuhr

On Sunday 22 January 2006 02:47 pm, Bob Pawley wrote:

Hi Tom

I found a datatype called 'interval' which seems to separate time from its
unit.

Is that what you were thinking of??

Bob
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Sunday, January 22, 2006 11:25 AM
Subject: Re: [GENERAL] Numbers

Bob Pawley <rjpawley@shaw.ca> writes:

My initial tables have columns containing values such as 12 feet.

I want to perform calculations.

Is there a method in Postgresql to separate the 12 from the unit feet or
am I forced to make two columns to separate the feet from the 12?

Are you storing '12 feet' as a string? That seems awfully unstructured
for data that you'd like to do calculations on.

I seem to recall that someone had come up with a datatype that would
store numbers with units attached, which seems like what you want here.
Check the PG list archives, and/or poke around on pgfoundry and gborg.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

I believe this is what Tom is talking about--
http://archives.postgresql.org/pgsql-general/2005-01/msg01646.php
--
Adrian Klaver
aklaver@comcast.net

On Sun, Jan 22, 2006 at 02:25:33PM -0500, Tom Lane wrote:

I seem to recall that someone had come up with a datatype that would
store numbers with units attached, which seems like what you want here.
Check the PG list archives, and/or poke around on pgfoundry and gborg.

Hmm, I only just noticed this thread, but it might work for him. OTOH,
if feet are the only units he's interested in then it's slight
overkill. Also, it doesn't deal with alternate spellings (1 foot,2
feet). If you decided you would only use abbreviations that wouldn't
matter ofcourse.

http://svana.org/kleptog/pgsql/taggedtypes.html

Quick example:

test=# create table physics_units ( name text, abbrev text );
CREATE TABLE
test=# insert into physics_units values ('feet','ft');
INSERT 2205045 1
test=# insert into physics_units values ('metres','m');
INSERT 2205046 1
test=# select create_tagged_type( 'physics_type', 'float', 'physics_units' );
NOTICE: type "physics_type" is not yet defined
DETAIL: Creating a shell type definition.
NOTICE: argument type physics_type is only a shell
create_tagged_type
--------------------

(1 row)

test=# select create_tagged_operator('physics_type','+','physics_type','physics_type');
NOTICE: +(physics_type,physics_type) => physics_type maps to +(double precision,double precision) => double precision
create_tagged_operator
------------------------

(1 row)

test=# select create_tagged_operator('physics_type','*','float','physics_type');
NOTICE: *(physics_type,double precision) => physics_type maps to *(double precision,double precision) => double precision
create_tagged_operator
------------------------

(1 row)

test=# select '10 feet'::physics_type + '22 feet'::physics_type;
?column?
----------
32 feet
(1 row)

test=# select '10 feet'::physics_type * 5;
?column?
----------
50 feet
(1 row)

test=# select '10 feet'::physics_type + '3 metres'::physics_type;
ERROR: Using tagged operator +(physics_type,physics_type) with incompatable tags (feet,metres)

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/