topic Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number in SAS Data Management

SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

bheinsius — Fri, 04 Nov 2016 17:06:31 GMT

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

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

Patrick — Sat, 05 Nov 2016 01:44:09 GMT

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.

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

LinusH — Sat, 05 Nov 2016 17:19:29 GMT

Try IGNORE_READ_ONLY_COLUMNS=YES libname option.

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

bheinsius — Sun, 06 Nov 2016 00:06:49 GMT

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?

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

Patrick — Sun, 06 Nov 2016 21:49:00 GMT

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.

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

LinusH — Sun, 06 Nov 2016 06:11:05 GMT

Re: SAS DI Studio 4.901 Table Loader to load Postgres table with serial number

bheinsius — Tue, 08 Nov 2016 16:25:32 GMT

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;