[PostgreSQL] row_number() 행번호

-- row_number()로 row별 1번~순서 매겨서 tmp1 테이블에 저장

 

create table tmp1  as

select (row_number() over()) as rn, col3

from

(

     select distinct(b.col3) as col3

     from table1 a

             left join 

             table2 b

     on a.col1 = b.col2

      where a.col2='Y'

      and b.col3 is not null

);

 

 

result 
select * from tmp1;

rn	col3
1	data1
2	data2
3	data3