SELECT from one table, INSERT into two other tables based on condition

1
votes

I have a Postgres database with 3 tables, say A, B and C. I want to select data from table A and loop through each row checking the value in one of the columns and then insert the data into table B or table C based on the condition.

How can I do this, can some one please post a sample script please. I prefer plpgsql (using PGAdmin3).

3
sqlpostgresqlsql-insert

3 Answers

2
votes

You don't need a cursor for this, you don't need plpgsql, you don't even need a data-modifying CTE which would allow you to do that in a single SQL statement.

Just run two plain INSERT statements. Put them in a transaction if you want to make sure all or nothing is applied:

BEGIN;

INSERT INTO B (col1, col2)
SELECT col1, col2
FROM   A
WHERE  col_cond = 'something';

INSERT INTO C (col1, col2)
SELECT col1, col2
FROM   A
WHERE  col_cond IS DISTINCT FROM 'something';

COMMIT;
0
votes

User cursor for select statement on table A, see this link

Inside the cursor you can check condition and run insert statements on B or C

For code example see this link

Cheers !!

0
votes

Type the following commands:

begin;
insert into table_name as select * from table_name2 where Name="?";
commit;