Data Types for PostgreSQL

Overview of Data Types

Every column in a table has a name and a data type. The data type tells PostgreSQL how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about PostgreSQL data types, null and default values, and data conversions.

For more information about PostgreSQL data types and to determine which data types are available for your version of PostgreSQL, see your PostgreSQL documentation.

PostgreSQL Null Values

PostgreSQL has a special value called NULL. A PostgreSQL NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a PostgreSQL NULL value, it interprets it as a SAS missing value.

You can define a column in a PostgreSQL table so that it requires data. To do this in SQL, you specify a column as NOT NULL. This tells SQL to allow a row to be added to a table only if a value exists for the field. For example, NOT NULL assigned to the CUSTOMER field in the SASDEMO.CUSTOMER table does not allow a row to be added unless there is a value for CUSTOMER. When creating a table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

You can also specify PostgreSQL columns as NOT NULL DEFAULT. For more information about using the NOT NULL DEFAULT value, see your PostgreSQL documentation.

Once you know whether a PostgreSQL column enables NULLs or the host system provides a default value for a column that is specified as NOT NULL WITH DEFAULT, you can write selection criteria and enter values to update a table. Unless a column is specified as NOT NULL or NOT NULL DEFAULT, it allows NULL values.

For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.

To control how SAS missing character values are handled, use the NULLCHAR= and NULLCHARVAL= data set options.

Working with Long Character Values in PostgreSQL

The PostgreSQL open-source driver automatically assigns a length of 255 characters to varying-length character variables, such as the VARCHAR(n) data type. Therefore, if you know that character data in a data set is longer than 255 characters, you should specify the MaxVarcharSize attribute for the CONOPTS= LIBNAME option when you connect to your PostgreSQL library. Specify a value for MaxVarcharSize that is as large as the longest column value to ensure that you retrieve all of the available data.

The following LIBNAME statement, sample tables, and SQL query would create a new data set that does not truncate the character data from the Testb data set.

libname x postgres preserve_col_names=no 
  preserve_tab_names=no database=mydb1 dbmax_text=32767 
  server='myserver' port=5432 user=pgadmin password='pgpwd' 
  conopts='MaxVarcharSize=300;';
 
/* Create example tables Testa and Testb */
proc sql; 
create table testa(idnum int, myflag int, col1 varchar(20))
create table testb(idnum int, col1 varchar(300))

insert into testa values (1, 1, 'from a 1')
insert into testa values (2, 0, 'from a 2')

insert into testb values (1, 'from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*274')
insert into testb values (2, 'from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*274')
quit;

/* Verify that data is not truncated */
proc sql;
create table work.SASTEST as 
   select case when myflag = 1 then a.col1 else b.col1 end as 
      col1 from x.testa a, x.testb b
      where a.idnum = b.idnum;

/* Select the end of col1 from position 200 to the end to show that all of */
/* the data is retrieved                                                   */
select substr(col1, 200) from work.SASTEST;
quit;

The output from the final SELECT statement appears as shown here. This statement selects a substring starting at column position 200 from the Col1 value.

rom b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*274

You can see that the end of the value does include the ‘*274’ from the end of the VARCHAR value.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to PostgreSQL assigns to SAS variables when using the LIBNAME statement to read from a PostgreSQL table. These default formats are based on PostgreSQL column attributes. SAS/ACCESS does not support PostgreSQL data types that do not appear in this table.

The following table shows the default data types that SAS/ACCESS Interface to PostgreSQL uses when creating tables. The PostgreSQL engine lets you specify nondefault data types by using the DBTYPE= data set option.