Postgres - Convert unix timestamp

2. February 2012 08:00

 

I am actually surprised that postgres does not have a builtin function to deal with unix timestamps. After all it does run on linux which does use unix timestamps. So it must be a common problem. Here is a quick solution.

 

 

CREATE OR REPLACE FUNCTION parseunixtimestamp(int)
	RETURNS timestamp AS 
		'SELECT TIMESTAMP ''epoch'' + $1 * INTERVAL ''1 second'''
	LANGUAGE SQL;

 

 

Now you can use it in select or insert statements. Like this

 

 

INSERT INTO TABLE (name, createdon) VALUES("a value", parseunixtimestamp(55436437))

 

If you need to you can remove it again later by running

 

 

DROP FUNCTION parseunixtimestamp(integer);
E-mail Kick it! DZone it! del.icio.us Permalink


Postgres - pg_relation_size is broken

28. January 2012 12:24

 

I started using postgres and inside my first week I amnaged to find an issue with pg_relation_size. The problem being is that SQL is mean to be non case sensitive. So I guess this is a bug since if you have an table name with an upper case character in it things seem to break, Badly.

 

to put this in context I create a table called "Attempts". It is for analyzing password attempts from a honeypot. I wrote a quick syslog parser and fired the output into sql so I could run some quries. So I have the following in postgres.

 

 

=# \d
                 List of relations
 Schema |          Name          |   Type   | Owner
--------+------------------------+----------+-------
 public | Attempts               | table    | root
 public | Attempts_AttemptID_seq | sequence | root
(2 rows)

 

So that is really simple. The following works without a problem. This will also work with a lower case "attempts"

 

 

=# select count(*) from "Attempts";
 count
-------
 24490
(1 row)

 

The following however does not.

 

 

select pg_relation_size('Attempts');

ERROR:  relation "attempts" does not exist
LINE 1: select pg_relation_size('Attempts');

 

Neither does

 

 

select pg_relation_size('attempts');
ERROR:  relation "attempts" does not exist
LINE 1: select pg_relation_size('attempts');

 

Or this.

 

 

select pg_relation_size("attempts");
ERROR:  column "attempts" does not exist
LINE 1: select pg_relation_size("attempts");


select pg_relation_size("Attempts");
ERROR:  column "Attempts" does not exist
LINE 1: select pg_relation_size("Attempts");

 

Or this.

 

select tablename from pg_tables where tablename = 'Attempts';
 tablename
-----------
 Attempts
(1 row)


select tablename,  pg_relation_size(tablename) from pg_tables where tablename = 'attempts';
ERROR:  function pg_relation_size(name) does not exist
LINE 1: select tablename,  pg_relation_size(tablename) from pg_table...
                           ^

 

 

This does ...

 

 

create table tmp (id int);

select pg_relation_size('tmp');
 pg_relation_size
------------------
                0
(1 row)

 

However this does work for both cases.

 

 

ALTER TABLE "Attempts" RENAME TO "attempts"


select pg_relation_size('Attempts');
 pg_relation_size
------------------
          2080768
(1 row)



select pg_relation_size('attempts');
 pg_relation_size
------------------
          2080768
(1 row)

 

 

So I guess that is broken then! This becomes a real pain now. Since I am using some orm software and the schema is defined by the classes in the code and not the other way around. I guess there is an extrac tolower somewhere.

E-mail Kick it! DZone it! del.icio.us Permalink