Postgres Syntax inner join update and temp table

Question

I'm using pg 9.3 and am wondering how I could do this in one statement, maybe even without a temp table. This seems a little messy to me.

create temp table docUse (
docid int primary key, 
name text, cnt int, 
mindate timestamp, 
maxdate timestamp);

insert into docuse (docid,cnt) 
    select documenttypeID, count(documenttypeID) from AllDocs group by documenttype;

update docuse set name = DocName from documenttype where documenttypeid = docid;

update docuse 
set mindate = _minDate, maxdate = _MaxDate from(
     Select min(Creation_Date) _mindate, max(Creation_Date) _MaxDate, docid did
    from AllDocs inner join docuse on documenttypeid = docid group by docid
) foo where did = docid;

A sample return row looks like

761,Invoice,598236,1/1/2000 12:00:00 am, 2/19/2016 3:15:54 pm

krokodilko · Accepted Answer · 2016-02-19 21:12:44Z

1

Try:

insert into docuse (docid,cnt, mindate, maxdate, name  ) 
SELECT x.documenttypeID, x.cnt, x.mi, x.mx,
       ( SELECT DocName d from documenttype where d.documenttypeid = x.documenttypeID)
FROM ( 
    select documenttypeID, 
           count(documenttypeID) as cnt,
           min(Creation_Date) as mi,
           max(Creation_Date) as mx
    from AllDocs a
    group by documenttype
) x;

answered Feb 19, 2016 at 21:12

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Syntax inner join update and temp table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related