Hi,
I have a Postgres table with a serial column (autoincrement) and want to append new data to it using the DI Studio table loader.
In the Table Loader I leave the column mapping empty for the serial column (__screening_id) hoping that it will not be loaded so that Postgres can fill it in itself:
But that is not what is happening.
It generates a mapping for __screening_id as follows:
%put %str(NOTE: Mapping columns ...); proc sql; create view work.W5CY70T as select . as "__screening_id"n length = 8, "__screen_id"n, "__screening_job_run_id"n, "__screening_object"n, "__screening_dts"n, "__screening_result"n, "__screening_msg"n, "" as "__update_user"n length = 32 format = $32. informat = $32., . as "__update_dts"n length = 8 format = DATETIME25.6 informat = DATETIME25.6 from &etls_lastTable ; quit;
and then a proc append. This returns the following Postgres error:
ERROR: During insert: ERROR: null value in column "__screening_id" violates not-null constraint
Failing row contains (null, 5, null, WIKSTO.TAG_MEASURES_NUM_PI, 2016-11-04 17:18:00, PASSED, 0 rows passed, 0 rows failed, 0 screens passed, 0 screens failed, null, null).; Error while executing the query
How can I make this work?
Bart
I believe there is no option where you can exclude a target column from being part of the SQL, not sure though and may be there is somewhere a drop option.
What should work is to have a "duplicate" table metadata object for loading where you remove your auto increment column.
I tested a duplicate metadata registration of the Postgres table without the SERIAL column (__screening_id).
This does not help (with proc append anyway), behind the scenes SAS queries the target table and generates Postgres SQL insert code for all columns:
POSTGRES_49: Prepared: on connection 4 INSERT INTO "DQ_SCREENINGS" ("__screening_id","__screen_id","__screening_job_run_id","__screening_object","__screening_dts","__screening_result","__screenin g_msg","__update_user","__update_dts") VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? )
I tested IGNORE_READ_ONLY_COLUMNS=YES, which also did not work.
It looks like SERIAL data type in Postgres is not read-only and so the libname engine does not recognize it as such.
What I found does work is specifying the (drop=serial_column) table option on the output dataset:
The generated code is then:
proc append base = WIKCNTL."DQ_SCREENINGS"n(drop=__screening_id) data = &etls_lastTable (&etls_tableOptions) force ; run;
But then this gives a warning (unacceptable in our batch) and I can't find a way to have the table loader add the NOWARN option to PROC APPEND.
Any (other) ideas?
I believe things could work if you use "Insert" instead of "Append" together with the approach of duplicate table metadata registration of the Postgres table without the SERIAL column.
Still worth trying what @LinusH proposed and I'd be very curious if that works.
- just read that you've tried this already.
I don't like the duplicate metadata registration.
I built my own (very limited) table loader transformation to:
- skip unmapped columns
- optionally add the NOWARN option to PROC APPEND
For what it's worth, the code I currently have (which will surely evolve when I find errors in it and need additional options).
%macro x;
%let rc = 0;
%if (&loadstyle = REPLACE_DROP) %then
%do;
%if (&_OUTPUT_engine. = SAS) %then
%do;
proc sql;
drop table &_OUTPUT.;
quit;
%end;
%else
%do;
%put ERROR: REPLACE_DROP is only allowed for SAS data sets.;
%let rc = 4;
%end;
%end;
%if (&loadstyle = REPLACE_DELETE) %then
%do;
proc sql;
delete * from &_OUTPUT.;
quit;
%let rc = &sqlrc;
%end;
%if (&loadstyle = REPLACE_TRUNCATE) %then
%do;
%if (&_OUTPUT_engine. = POSTGRES) %then
%do;
data _null_;
call symput('postgres_table',strip(scan(scan(symget('_OUTPUT'),2,'.'),1,'"')));
run;
%put NOTE: &=POSTGRES_TABLE;
proc sql;
connect to postgres (&_OUTPUT_connect);
execute ( truncate "&POSTGRES_TABLE" ) by postgres;
disconnect from postgres;
quit;
%let rc = &sqlrc;
%end;
%else
%do;
%put ERROR: REPLACE_TRUNCATE is only allowed for Postgres tables.;
%let rc = 4;
%end;
%end;
%if (&rc = 0) %then
%do;
proc datasets lib=WORK nolist nowarn;
delete __LOAD;
run;
proc sql;
create view WORK.__LOAD as
select
%let first = 1;
%do i=0 %to %eval(&_OUTPUT_col_count. - 1);
%if ((&&&_OUTPUT0_col&i._exp. ne ) or (&&&_OUTPUT0_col&i._input_count. > 0)) %then
%do;
%if (&first. ne 1) %then , ;
%else %let first = 0;
%if (&&&_OUTPUT0_col&i._exp. ne ) %then &&&_OUTPUT_col&i._exp;
%else %if (&&&_OUTPUT0_col&i._input_count. > 0) %then &&&_OUTPUT_col&i._input;
as &&&_OUTPUT_col&i._name
%if (&&&_OUTPUT_col&i._type. = $) %then length=&&&_OUTPUT_col&i._length.;
%if (&&&_OUTPUT_col&i._format. ne ) %then format=&&&_OUTPUT_col&i._format.;
%if (&&&_OUTPUT_col&i._informat. ne ) %then informat=&&&_OUTPUT_col&i._informat.;
%if (&&&_OUTPUT_col&i._label. ne ) %then label="&&&_OUTPUT_col&i._label.";
%end;
%end;
from &_INPUT
;
quit;
proc append base=&_OUTPUT.(
%let first = 1;
%do i=0 %to %eval(&_OUTPUT_col_count. - 1);
%if ((&&&_OUTPUT0_col&i._exp. eq ) and (&&&_OUTPUT0_col&i._input_count. = 0)) %then
%do;
%if (&first. = 1) %then
%do;
drop =
%let first = 0;
%end;
&&&_OUTPUT_col&i._name
%end;
%end;
)
data=WORK.__LOAD
%if (%upcase(&warn) = NOWARN) %then NOWARN;
;
run;
%end;
%mend x;
options mprint;
%x;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.