Postgresql SET DEFAULT value from another table SQL

Question

I'm making a sql script so I have create tables, now I have a new table that have columns. One column has a FOREIGN KEY so I need this value to be SET DEFAULT at the value of the value of the original table. For example consider this two table

PERSON(Name,Surename,ID,Age);

EMPLOYER(Name,Surname,Sector,Age);

In Employer I need AGE to be setted on default on the AGE of Person, this only if PERSON have rows or just 1 row.

ID is Primary key for person and Surname,Sector for employer and AGE is FOREIGN KEY in Employer refferenced from Person

Example sql :

CREATE TABLE PERSON(
    name VARCHAR(30) ,
    surename VARCHAR(20),
    ID VARCHAR(50) PRIMARY KEY,
    Age INT NOT NULL,
);
CREATE TABLE EMPLOYER(
    name VARCHAR(30) ,
    Surename VARCHAR(20),
    Sector VARCHAR(20),
    Age INT NOT NULL,
    PRIMARY KEY (Surename,Sector),
    FOREIGN KEY (Age) REFERENCES Person(Age) //HERE SET DEFAULT Person(Age), how'??
);

Have the person id as foreign key in Employer table and have a join. I hope it makes sense. — SASIKUMAR SENTHILNATHAN
– SASIKUMAR SENTHILNATHAN, Commented Mar 14, 2017 at 9:48
AGE is FOREIGN KEY in Employer refferenced from Person -- What? That doesn't make any sense. At all. — pozs
– pozs, Commented Mar 14, 2017 at 9:49

Gurmokh · Accepted Answer · 2017-03-14 11:30:37Z

6

Taking away the poor design choices of this exercise it is possible to assign the value of a column to that of another one using a trigger.

Rough working example below:

create table a (
 cola int, 
 colb int) ;

create table b (
 colc int, 
 cold int);

Create or replace function fn()
returns trigger 
as $$ begin
if new.cold is null then
  new.cold = (select colb from a where cola = new.colc);
end if;
return new;
end; 
$$ language plpgsql; 

 CREATE TRIGGER
   fn
 BEFORE INSERT ON
   b
 FOR EACH ROW EXECUTE PROCEDURE
   fn();

answered Mar 14, 2017 at 11:30

Gurmokh

2,09127 silver badges28 bronze badges

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

Comments

Chris Travers · Accepted Answer · 2017-03-14 11:16:01Z

1

Use a trigger rather than a default. I have done things like this (useful occasionally for aggregated full text vectors among other things).

You cannot use a default here because you have no access to the current row data. Therefore there is nothing to look up if it is depending on your values currently being saved.

Instead you want to create a BEFORE trigger which sets the value if it is not set, and looks up data. Note that this has a different limitation because DEFAULT looks at the query (was a value specified) while a trigger looks at the value (i.e. what does your current row look like). Consequently a default can be avoided by explicitly passing in a NULL. But a trigger will populate that anyway.

answered Mar 14, 2017 at 11:16

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

Postgresql SET DEFAULT value from another table SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related