Recipe: Cloud Data Exchange (CDE) - Create a PostgreSQL RDA data service.

[#1]

Creating a new Postgres data service in the Remote Data Agent (RDA) in CDE is probably the easiest of the many datasources CDE supports. The Postgres drivers are already in place on the RDA side.
I haven't found any valid documentation for this, so I thought I'd just write this article about it, to spread the love.

1. Preconditions

1. Remote SAS Data Agent is deployed and registered to your Viya environment.

2. sas-viya CLI is installed where you run the commands, and the dagentsrv plug in is available.

3. You can authenticate to Viya with an identity authorized to administer Data Agent objects.

4. You already created the Auth-domain and credentials in this Auth-domain in Viya to be used for this new CDE Postgres Service.

2. Create the PostgreSQL data service

Here is an example to create a new service called "PSQL-service-name", using a auth-domain "ViyaAuthDomain" and the PSql database name "PSQLDB" on the server ip address "10.10.10.10" (This can also be a hostname) on port 5432.

./sas-viya dagentsrv data-services create postgres \
  --name PSQL-service-name \
  --driver postgres \
  --catalog PSQLDB \
  --database-name PSQLDB \
  --server 10.10.10.10 \
  --domain ViyaAuthDomain 
  --port 5432 \
  --client-encoding UTF-8 \
  --conopts UseDeclareFetch=1 \
  --case-sensitive-columns yes \
  --case-sensitive-objects yes \
  --max-text-len 32767

Optional operational checks immediately after creation:

# Basic connectivity test:
./sas-viya dagentsrv data-sources test --name PSQL-service-name

# List out all available databases/catalogs:
./sas-viya dagentsrv data-sources catalogs list --data-source PSQL-service-name

# List out all available schemas in the database:
./sas-viya dagentsrv data-sources schemas list --data-source PSQL-service-name --catalog PSQLDB

# List out all tables from a given schema in the database:
./sas-viya dagentsrv data-sources tables list --data-source PSQL-service-name --catalog PSQLDB --schema <schema>

Why --UseDeclareFetch=1matters:

UseDeclareFetch=1 forces the PostgreSQL driver used by SAS Data Agent to fetch query results incrementally using a server side cursor, instead of materializing the entire result set in memory at once.

This is critical for memory stability and scalability when querying large tables through CDE and Remote Data Agent.

Why --max-text-len 32767 matters:

This was introduced to me in a support ticket regarding some errors fetching columns of type TSVECTOR and Arrays (e.g. TEXT[]).This is now no longer needed to be inserted into the service creation statement, as it is fixed in version 2025.08. But it does not hurt to add it.

3. Create the CDE libref:

A normal CDE libname statement can then be used to access the Postgres database:

libname PG-LIBREF-NAME cde dataAgentName="CDE_RDA_NAME" dsn="PSQL-service-name " schema="public" preserve_tab_names=yes;