Data succesfully inserted, but table remains empty

Im currently working with SQL, and im currently using postgreSQL, sepcifically pgAdmin4. i have this table i called 'acetami' created with the following query:

CREATE TABLE ACETAMI (
  PRIMARYID BIGINT,
  DRUG VARCHAR(3000),
  Gender VARCHAR(3),
  AGE FLOAT,
  Adverse_event VARCHAR(100),
  ROLE_COD VARCHAR(2)
);

it supposed to contain data with other table, i inserted primaryid, drug, and adverse_event with the following query:

INSERT INTO acetami (PRIMARYID, DRUG, Adverse_event)
SELECT PRIMARYID, DRUG, Adverse_Event
FROM drug_adverse_reactions_pairs
WHERE DRUG = 'acetaminophen' AND Adverse_Event = 'Nausea';

it works just fine.

But then i tried to insert age and gender from demographics table where the primary id matches with acetami, here is the query i used:

INSERT INTO acetami (gender, age)
SELECT demographics.gender, demographics.age
FROM demographics
WHERE demographics.primaryid IN (SELECT primaryid FROM acetami);

it returns:
INSERT 0 41493

Query returned successfully in 2 secs 251 msec.

im not really sure what i means but when i checked the acetami table, all age and gender is still NULL

here is how i create the demographics table:

DROP TABLE IF EXISTS DEMOGRAPHICS;
CREATE TABLE DEMOGRAPHICS (
  caseid BIGINT,
  primaryid BIGINT,
  caseversion INT,
  fda_dt INT,
  I_F_COD VARCHAR(1),
  event_dt INT,
  AGE FLOAT,
  Gender VARCHAR(3),
  COUNTRY_CODE VARCHAR(2),
  Period VARCHAR(50)
);

I thought the problem is due to all the primaryid not having data in the demographics table, but when i checked with specific primaryid in acetami, it does have gender and age.

im not sure what im missing here, query wise it's correct as far as i know. or maybe im missing something im not aware of. Does anyone know what i might be doing wrong here?

If you want to Update the Age & Gender of the existing primaryid then you need to use UPDATE statement, not INSERT statement. Insert statement is used to insert new rows.

You already Inserted rows using INSERT statement from drug_adverse_reactions_pairs table. In the second step you are trying the update the column value of Age & Gender.

Try UPDATE statements as below

UPDATE acetami SET Gender = b.gender, Age = b.age 
FROM acetami a INNER JOIN DEMOGRAPHICS b ON a.PRIMARYID = b.primaryid

There's no value assigned to column primaryid in acetami when inserting from demographics, and there's no default for that column, so any rows inserted would have NULL for primaryid. It appears that you indended to update existing rows in acetami with values from demographics. This can be achieved with the following:

UPDATE acetami
   SET gender = demographics.gender,
       age = demographics.age
  FROM demographics
 WHERE acetami.primaryid = demographics.primaryid;

Neither acetami nor demographics have primary key constraints/indexes, which would improve the query execution times.

As already pointed out, these are both inserts of new sets of records. The second one won't update the ones previously placed in the table. You can reduce them to a single insert:

insert into acetami 
      (   primaryid,    drug,   gender,   age,    adverse_event)
select ar.primaryid, ar.drug, d.gender, d.age, ar.adverse_event
from drug_adverse_reactions_pairs as ar,
     demographics as d
where ar.primaryid = d.primaryid --implicit join
and   ar.drug = 'acetaminophen' 
and   ar.adverse_event = 'Nausea';

Here's documentation on joining tables and why explicit joins can help:

insert into acetami 
      (   primaryid,    drug,   gender,   age,    adverse_event)
select ar.primaryid, ar.drug, d.gender, d.age, ar.adverse_event
from drug_adverse_reactions_pairs as ar
join demographics as d on ar.primaryid = d.primaryid --explicit join
where ar.drug = 'acetaminophen' 
and   ar.adverse_event = 'Nausea';

PostgreSQL 15 also offers merge, which does what you expected: it combines an insert with an update. If no matching record is found in the target table, the payload is inserted as new. If a matching record is found, the payload is used to update it.

If your primaryid was an actual primary key, or at least a unique value, you could use an insert...on conflict do update construct to get a similar effect.