Question 182540 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search