Thread: Numbers
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?
Bob Pawley
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 > 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
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/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.