Mehdi Monzavi Mehdi Monzavi - 1 month ago 16
SQL Question

how to get last value of any attribute in postgresql pivot table

I have a table like this

id u_id attr_key attr_value process_id insert_time
------|-------|----------|------------|--------------|--------------
1 1 name john 1 1
2 1 family smith 1 2
3 2 job clerk 2 3
4 1 name sarah 3 4
.............


I have to find two things:


  1. I have to create a view by tablefunc(crosstab) to fetch a group of data for any of u_id ..so it's simple

  2. I have to find (realtime) last value of any key of any u_id (like Hbase database) so I don't have any good solution



this is what i need

id u_id attr_key attr_value
------|-------|----------|------------
4 1 name sarah
2 1 family smith


Any idea or function?
(its possible to add a column in my data model )

Answer

The most efficient solution to problems in Postgres is to use distinct on ():

The following will retrieve that for a single u_id

select distinct on (attr_key) id, u_id, attr_key, attr_value
from the_table
where u_id = 1
order by attr_key, insert_time desc;

An index on (u_id, attr_key, insert_time) should help for performance