PostgreSql Function "Insert data from a table to an other"

Question

I want to make a function in PostgreSQL that would make the following:

First of all read some data from a table lets say “Select col1,col2 from table1” Then for each row of the above selection I want to make an insert to an other table lets say table2 (that contains some extra columns like date and so on).

For each insertion I want a unique key that starts from a given number and is increased in every new row. Can someone give me an example about how I can do it?

I need to be more specific I want to do what is discribed below:

    For(every row in table1)
       if(table1.col1>0)
          insert into table2 (c1,c2,c3,c4) nalues (id,table1.col1,table1.col2,'oposite',current_timestamp)
       else if(table1.col1<0)
          insert into table2 (c1,c2,c3,c4) nalues (id,table1.col1,table1.col2,'negative',current_timestamp)

id+=1

podiluska · Accepted Answer · 2012-08-21 15:08:36Z

3

insert table2(id, col1, col2)
select startingpoint-1+row_number() over (order by col1, col2), 
       col1, 
       col2 
from table1

edited Aug 21, 2012 at 15:08

user330315

answered Aug 21, 2012 at 14:54

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

2 Comments

user1392203 Over a year ago

I have some extra data so I want a for loop that will be run for every row of table1

user330315 Over a year ago

@user1392203: that statement will run for every row in table1. The statement does exactly what you asked for. If you do have a serial column, simply leave out the id column in the INSERT (and select) part. PostgreSQL will then populate it automatically.

J V · Accepted Answer · 2012-08-21 14:53:04Z

0

You can use select into to do this, just set up the second table with an autoincrementing field and it will give you your unique key.

answered Aug 21, 2012 at 14:53

J V

12.1k10 gold badges56 silver badges78 bronze badges

1 Comment

user1392203 Over a year ago

The application is using hibernate and there is a sequence that gives the unique keys in every row of the tables

Collectives™ on Stack Overflow

PostgreSql Function "Insert data from a table to an other"

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related