Search code examples
sqlpostgresqlmigrate

Migrate attributes from a table to another table

I have two tables : 

TEST1 : 
attribut1, attribut2, attribut3 

TEST2 : 
attribut4

attribut4 pattern is like "xxxxx yyyyy zzzzz"

My question is, is it possible to put in TEST1 attribut1 all the "xxxxx", in the attribut2 all the "yyyyy" and in the attribut3 "zzzzz" of the table TEST2?

I tried something like : 

insert into TEST1(attribut1) SELECT SUBSTRING(attribut4,1,5)::integer FROM TEST2;

but it said :

ERROR: null value in column "attribut2" violates not-null constraint DETAIL: Failing row contains (xxxxx, null, null).

(attribut1,attribut2) are PRIMARY KEY.

Thanks for your answers and for your time.

Solution

  • You can use split_part():

    insert into TEST1 (attribut1, attribut2, attribut3)
    SELECT split_part(attribut4, ' ', 1)::int,
           split_part(attribut4, ' ', 2)::int,
           split_part(attribut4, ' ', 3)::int
    FROM TEST2;