CODEWITHSUNDEEP
databasepostgresql
GastonKhouri
GastonKhouri

Reputation: 33

PostgreSQL Fill in a field automatically based on the value entered in another table

My question is the following:

I have a PostgreSQL database with several tables, what I want to do is the following, I will give you three tables as an example.

The first table is called teachers that has attributes name, id and type where type can only be "IN" or "EX"

I have a second table called internal teachers with the ID of all teachers with the attribute type = "IN" and another table called external teachers with the ID attribute of all teachers where type = "EX"

The thing is that at the moment I have to manually add the ID to the internal teachers and external teachers table, I would like to know if there is any way to that when entering the teacher's ID and its type to the teachers table, the database automatically places its ID in one table or another depending on its type.

Without more to say a greeting and thanks!

Upvotes: 1

Views: 2548

Answers (3)

GMB
GMB

Reputation: 222482

There is no Postgres functionality that automatically creates row in a child table on insert on the parent table. Foreign key constraints enforce integrity when the data changes and come with various options, but not the one you describe.

One option is to write trigger. You could also be explicit in the query, using Postgres’ returning syntax:

with 
    t as (
        insert into teachers(name, type)
        values (?, ?)
        returning *
    ),
    x as (
        insert into teachers_internal(id)
        select id from t where type = 'IN'
    )
insert into teachers_external(id)
select id from t where type = 'EX'

I would recommend creating foreign keys too, so the proper action is taken when rows are dropped from the parent table (you probably want to delete the children row too).

Upvotes: 0

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

If you want automatically insertion in internal and external tables, you can use trigger as below:

Trigger Function

create or replace function trig_fun() 
returns trigger AS
$$
begin

if(new.type='IN') then
insert into internal values(new.id);
end if;

if(new.type='EX') then
insert into external values(new.id);
end if;

return new;

end;
$$
language plpgsql

and attach it after insert event of teacher table

create trigger trig_on_insert 
after insert on 
teachers
for each row
execute procedure trig_fun()

DEMO

Upvotes: 3

Frank Wilson
Frank Wilson

Reputation: 3250

You could implement your ‘internal’ and ‘external’ tables as separate views of the teachers table.

create table teachers as
select * from (values ( 1, 'EX')) as z(id, type);
          
create view external as (select * from teachers where type = 'EX');

create view internal as (select * from teachers where type = 'IN');

The following query:

select * from external;

gives:

(1, 'EX')

Whereas selecting from 'internal' gives an empty result (because there are no internal entries).

http://sqlfiddle.com/#!17/25a5a/1

Upvotes: 2

Related Questions