I want to insert data into one table from another table. In some of the columns I don't have data, so I want to set column to null. I don't know how I should do this?
This is the SQL:
INSERT INTO _21Appoint(
PCUCODE,PID,SEQ,
DATE_SERV,APDATE,
APTYPE,APDIAG,D_UPDATE,CID
) SELECT (
NULL,NULL,NULL,
treatment_date,appointment_date,
typeap_id,appointment_id,NULL,patient_id
) FROM cmu_treatment,cmu_appointment
WHERE cmu_treatment.treatment_id LIKE cmu_appointment.treatment_id;
Your insert is essentially correct. Just don't put the column list in parentheses:
INSERT INTO _21Appoint
(PCUCODE,PID,SEQ,DATE_SERV,APDATE,APTYPE,APDIAG,D_UPDATE,CID)
SELECT NULL,NULL,NULL,treatment_date,appointment_date,typeap_id,appointment_id,NULL,patient_id
FROM cmu_treatment,cmu_appointment
WHERE cmu_treatment.treatment_id LIKE cmu_appointment.treatment_id;
In Postgres (unlike other DBMS) putting a column list in parentheses makes the result a single "record", rather then individual columns. And therefore the select only returns a single column, not multiples and thus it doesn't match the column list for the insert
another option is to simply leave out the columns completely:
INSERT INTO _21Appoint
(DATE_SERV,APDATE,APTYPE,APDIAG,CID)
SELECT treatment_date,appointment_date,typeap_id,appointment_id,patient_id
FROM cmu_treatment,cmu_appointment
WHERE cmu_treatment.treatment_id LIKE cmu_appointment.treatment_id;