Pages

Thursday, December 22, 2011

Useful PostgreSQL Commands

Update statement joining tables
update deduction set sum=sum+1 from employee e
where e.id=deduction.employeeid and e.groupid=2;

update hall_sample h set panel_id = s.panelid 

    from (select lower(trim(address)) as email, e.panelid from email e 
    inner join (select panelid from contact where statusid = 1 and typeid = 2) AS c 
    on e.panelid = c.panelid) AS s where lower(trim(h.email)) = lower(trim(s.email))

Find duplicates when more than one unique values
select s.studyid, s.sam_no, count(*) from table_name i 

  inner join table_name s on (i.studyid = s.studyid AND i.sam_no = s.sam_no) 
  group by 1,2 having count(*) > 1

SELECT key, count(*) FROM ibmcvar.disposition GROUP BY 1 HAVING count(*) > 1; 


SELECT * FROM ibmcvar.disposition where key in (select key from (SELECT key, count(*) 

    FROM ibmcvar.disposition 
    GROUP BY 1 HAVING count(*) > 1) as t) order by key

Delete one from duplicate rows (minimum fatiguedate)
delete from ibmcvar_2012.disposition where (key, fatiguedate) in (

select key, min(fatiguedate) from ibmcvar_2012.disposition where key in
(select key from (select key, count(*)  from ibmcvar_2012.disposition
group by 1 having count(*) > 1 ) as x ) 
group by 1 order by 2 )

Find records with latest update value
SELECT * FROM table a JOIN (SELECT ID, max(date) maxDate

   FROM table GROUP BY ID) b ON a.ID = b.ID AND a.date = b.maxDate

Add sequence to existing column in existing table
First set the maximum number of the field as current value in sequence'

ALTER TABLE ccadmin.tblemployees ALTER COLUMN recordid SET DEFAULT nextval('ccadmin.tblemployees_sec'::regclass)


Select last 5 values for 2 columns as fields for selected column value(person, project)

select puin, 
max(case when (row_number = 1) then job_number else null end )  as jobnumber1,
max(case when (row_number = 1) then web_stat_type else null end )  as stat_type1,
max(case when (row_number = 2) then job_number else null end )  as jobnumber2,
max(case when (row_number = 2) then web_stat_type else null end )  as stat_type2,
max(case when (row_number = 3) then job_number else null end )  as jobnumber3,
max(case when (row_number = 3) then web_stat_type else null end )  as stat_type3,
max(case when (row_number = 4) then job_number else null end )  as jobnumber4,
max(case when (row_number = 4) then web_stat_type else null end )  as stat_type4,
max(case when (row_number = 5) then job_number else null end )  as jobnumber5,
max(case when (row_number = 5) then web_stat_type else null end )  as stat_type5
from ( WITH summary AS (
select puin, called_or_mailed_date, job_number, stat_type as web_stat_type, 
row_number() over (PARTITION BY puin order by called_or_mailed_date desc)
from reports.add_fields2_samples
where puin in ('C3CE780B-0E3F-4A0E-BA3A', 'EF9ADA63-2D5F-427C-989E')
group by 1,2,3,4 order by puin asc, called_or_mailed_date desc )
SELECT *   FROM summary where row_number < 6 ) as a
group by 1