I'm told today is Pi day, in celebration of our friendly transcendental constant and because today is the 14th March, which is written as 3.14 in some locales....

So I thought I'd celebrate with a look at Postgres' mathematical musculature.

postgres=# select pi();

pi

------------------

3.14159265358979

Cool! That matches on all the digits, so we're rocking. So what datatype is this?

postgres=# select pg_typeof(pi());

pg_typeof

------------------

double precision

and as the manual says, we support at least 15 digits for this datatype. Even better. So let's flex those long dormant trigonometry muscles:

postgres=# select sin(90);

sin

-------------------

0.893996663600558

Oh no! Surely sin() of 90 degrees is 1.0? Perhaps the default is radians and I just forgot. Of course, doh! The manual doesn't actually say, which seems like a flaw. Here comes a doc patch. So lets supply radians instead.

postgres=# select sin(radians(90));

sin

-----

1

Phew! That works. Now lets try some advanced stuff. There are 2*pi() radians in a circle, so pi() radians is half way round. So the sin() of that should be 0, and the cos() should be 1.

postgres=# select sin(pi()), cos(pi());

sin | cos

----------------------+-----

1.22464679914735e-16 | -1

Hmmm. That is somewhat strange, but I guess that pi is transcendental so any representation with a fixed number of digits will always be slightly wrong by exactly that number of digits. That makes sense, but I guess I was expecting sin(pi()) to return 0.

Just to put this in perspective, in comparison to the diameter of the earth that is a precision of about one millionth of a millimetre. So that is very accurate for most applications.

Let's quickly check "e", another well known transcendental. We don't provide a function for e as we do for pi, but its easy to calculate.

postgres=# select exp(1);

exp

------------------

2.71828182845905

That looks good. So lets play some games with that also.

postgres=# select ln(exp(1));

ln

----

1

Thank goodness for that. All good. I notice in the docs that exp() takes either a double precision or a numeric input, so which one did we just try there? Let's see

postgres=# select ln(exp(1::float));

ln

----

1

and lets also try

postgres=# select ln(exp(1)::numeric);

ln

--------------------

1.0000000000000018

Hmmm. Looks like Postgres is being irrational. Or should that be insufficiently irrational? It's been a long day. I can't wait for "sqrt(2) day".

Subscribe to:
Post Comments (Atom)

it works better like this:

ReplyDelete# select ln(exp(1::numeric));

ln

--------------------

1.0000000000000000

(1 row)

Some more fun:

ReplyDelete=# select to_date('2010.' || pi()::text, 'YYYY.MM.DD');

to_date

------------

2010-03-14

(1 row)

=# select to_timestamp('2010.' || pi()::text, 'YYYY.MM.DDSSSSUS');

to_timestamp

-------------------------------

2010-03-14 04:25:26.535897+01

(1 row)