title: PostgreSQL SQL Problem with Triggers and duplicates
body: <p>I have a problem and i want to eliminate non-relevant information using a SQL query and triggers</p>
  <p>Table is</p>
  <pre><code>create table z_test(id, fyear smallint, byear text);
  </code></pre>
  <p>data to be inserted .</p>
  <pre><code>insert into z_test  (fyear, byear) values (Null, '2018 2019 2020');
  insert into z_test  (fyear, byear) values (null, '2018 2019 2020');
  insert into z_test  (fyear, byear) values (null,  '2018 2019 2020');
  </code></pre>
  <p>problem definition: I want fyear to be 2018 for the first row, 2019 for the second row and
  2020 for the third row. If the number of rows i am inserting is equal to the number of years
  in byear, it should be inserted. If it is not equal, then its ok to insert Null into fyear</p>
  <p>Alternatively, i could also get data like this.</p>
  <pre><code>insert into z_test  (fyear, byear) values (Null, '2018 2019 ');
  insert into z_test  (fyear, byear) values (null, '2018 2019 ');
  </code></pre>
  <p>In this case, 2018 should be first row and 2019 the second row.</p>
  <p>What i have tried so far. Tried to create a CTE in a trigger that inserts into z_test. Inside
  the trigger, i use regexp_split_to_table to split the byear into rows. I then try to put the first
  year in the first row, second year in the second row and so on. I am using transactions and psycopg2. Unfortunately, the problem is the trigger is not able to find previously uncommitted rows in the table and i am stuck here. Please help. So all values are going as null. Is there a way to do it with an SQL Query, preferably triggers?</p>
  <pre><code>CREATE TRIGGER z_test_trigger
      AFTER INSERT
      ON public.z_test
      FOR EACH ROW
      EXECUTE PROCEDURE public.z_test_procedure();
  
  CREATE FUNCTION public.z_test_procedure()
      RETURNS trigger
      LANGUAGE 'plpgsql'
      COST 100
      VOLATILE NOT LEAKPROOF
  AS $BODY$
  BEGIN 
      
          WITH cte AS(
          select numyr::SMALLINT,byear from 
          (select  trim(regexp_split_to_table(NEW.ner_text, '\s+')) AS numyr 
           )x where is_numeric(numyr) =true and length(numyr)=4 and numyr like '20%' )
          SELECT numyr INTO NEW.fyear FROM cte  WHERE numyr NOT IN (SELECT byear FROM z_test WHERE  
           NEW.byear =  cte.byear  ) LIMIT 1  ;
      
      RETURN NEW;
  END;
  $BODY$;
  </code></pre>