PostgreSQL Preview

Connect Codatum to PostgreSQL to run SQL and sync table metadata. Codatum runs SQL in read-only transactions. For an overview of connections in general, see Connection.

INFO

This feature is in preview. To request access, contact Codatum support.

Preparing PostgreSQL

Setting up the network

1. Make your database reachable from the internet at a public IPv4 address (Network).
2. If you restrict connections by IP address, allow connections from Codatum's outbound IP addresses.
3. If TLS isn't enabled on the server, enable it (TLS and authentication).

Creating a role

Create a role with the LOGIN attribute for Codatum, and grant it USAGE on the schemas and SELECT on the tables you want to use. If you've revoked CONNECT on the database from PUBLIC, also grant CONNECT on the database to the role. Don't grant write or administrative privileges to the role, because a read-only transaction doesn't prevent every operation.

We recommend setting a connection limit on the role for Codatum with ALTER ROLE <role_name> CONNECTION LIMIT <connections>. If a connection is refused, see When the connection test fails.

Configuring Codatum

1. Open global nav > Workspace settings > Data management > Connections, then select New connection.
2. Select PostgreSQL (only shown if it's available to you).
3. Enter a Connection name and select an Access level.
4. Enter the following.

Item	Description
Host	The hostname to connect to. For hostname verification, see TLS and authentication.
Port	An integer from 1 to 65535 (default 5432).
Database	The database to connect to. You can sync and query only this database. To use multiple databases, create a connection for each database.
User	The role to connect as. Specify the role you created in Creating a role.
CA certificate	The certificate of the CA that verifies the server certificate (PEM format). Optional.
Skip hostname verification	Can be turned on only when a CA certificate is set (off by default).
Password	An Authentication item. At least one of Password and Client certificate is required.
Client certificate	An Authentication item. The certificate for certificate authentication (PEM format). Set it together with Client key.
Client key	An Authentication item. The private key for the Client certificate (PEM format).

After entering these, run Test connect, then save the connection. On the sync targets screen shown after saving, select the schemas to sync (Table metadata sync). You can also skip this step.

Network

• Codatum connects over the internet to the IPv4 addresses that Host points to.
• If Host points to an address that isn't used on the internet, such as a private IP address or a loopback address, Codatum can't connect.
• Codatum can't connect to a host that has only IPv6 addresses.
• Codatum connects from one of its outbound IP addresses.
• Codatum doesn't support connecting through an SSH tunnel, a VPN, VPC peering, or similar methods.

TLS and authentication

• TLS is required. Codatum connects with TLS 1.2 or later.
• If you don't set a CA certificate, Codatum verifies the server certificate as one issued by a public certificate authority (CA). If your server uses a certificate issued by any other CA, you need to set the CA certificate.
• If you set a CA certificate, Codatum verifies the server certificate against that CA only.
• By default, Codatum checks that the name in Host is included in the server certificate. If you enter an IP address in Host, the connection fails unless the server certificate includes that IP address.
• When Skip hostname verification is on, Codatum only checks that the server certificate was issued by the CA in CA certificate, and doesn't check the hostname. Use it for databases whose server certificate doesn't include the hostname you connect to, such as when the database uses a per-instance CA.
  • Use it only when the CA in CA certificate is dedicated to that database. If you set a CA shared by multiple servers or a public CA, certificates that the CA issued to other servers also pass verification.
• For authentication, use a Password, a pair of Client certificate and Client key, or both. At least one is required.
  • Set both Client certificate and Client key.
  • There's no field for a private key passphrase.
• After you save a connection, its Password, Client certificate, and Client key aren't shown. To change them, select Edit and enter them again.

Supported versions

• Codatum supports the PostgreSQL versions that the PostgreSQL development team supports. You can check the supported versions in the PostgreSQL versioning policy.
• You can't connect to servers older than version 14. Test connect fails for them.

Running SQL

• Codatum runs SQL in a read-only transaction. SQL that changes data fails with a PostgreSQL error.
• Each run can send only one SQL statement. Sending multiple statements fails with cannot insert multiple commands into a prepared statement.
• Codatum rolls back the transaction after each run. Settings that your SQL changes with SET or similar commands don't carry over to the next run.
• Codatum doesn't override search_path. The values set for the role or the database apply.
• The number of SQL statements that one connection runs at the same time is limited. Any additional SQL waits until other runs finish. If it can't start within a certain time, it fails.
• SQL can run for up to 15 minutes.
• The SQL editor shows the number of result rows estimated by the PostgreSQL planner as Est. rows. The estimate can differ greatly from the actual count.
• While SQL runs, Codatum sets the session's application_name to a value that starts with codatum: (Job label).

Error display

Reports, signed embeds, public links, and Codatum CLI show Query job failed instead of the error that PostgreSQL returned.

Run results

Run result limits

• Codatum cuts off a run result when it reaches either 1,048,575 rows or 100 MiB (uncompressed). Cutting off a run result isn't an error.
• Codatum doesn't show that a run result was cut off.
• If a single row is larger than 16 MiB, the SQL run fails with A row in the query result exceeds the size limit of 16 MiB..
• Downloads also write the run result cut off at these limits. These limits apply regardless of the Up to 1 GB label in the download menu.

For a list of limits, see Limits.

Run result retention

• Run results become eligible for deletion 2 days after the run. The time when the deletion completes isn't fixed.
• A range you have viewed might stay available after the 2 days.
• If you open a range you haven't viewed after the run result is deleted, Execution result has expired appears. Run the SQL again.
• When you download the run result of a SQL block in a notebook more than 24 hours after the run, Codatum runs the SQL again before writing the file.

Value representation

Codatum converts values of the following types in run results.

Type	Representation
bigint (int8), numeric	A string, to keep precision.
timestamp with time zone (timestamptz)	Converted to UTC with a trailing Z (for example, 2025-01-02T03:04:05.123456Z).
timestamp without time zone (timestamp)	No time zone (for example, 2025-01-02T03:04:05.123456).
date	YYYY-MM-DD (for example, 2025-01-02).
time without time zone (time)	No time zone (for example, 03:04:05.123456).
real, double precision	A number. NaN, Infinity, and -Infinity are strings.
json, jsonb	A JSON value. Numbers inside the JSON become strings.
bytea	Uppercase hexadecimal, without \x.
Arrays, enums, extension types, and so on	A string in PostgreSQL's text representation.

For how date and time types are handled, see Dates, times, and timezones.

Feature support

• The information that table metadata sync imports differs in part from other warehouses.
• The same table synced from different connections appears as separate tables in the catalog (Table identity).
• PostgreSQL SQL can't reference inline tables. You also can't save PostgreSQL run results as inline tables.
• Column stats in SQL blocks calculate all values exactly.
• The PostgreSQL functions list that you open with / in the SQL editor includes only commonly used functions. It doesn't cover all PostgreSQL functions.

When the connection test fails

When Test connect fails, the error appears under Error connecting to PostgreSQL. Check the following based on the error.

1. If an error that starts with Could not reach the database within the time limit. appears, check that you allow connections from the outbound IP addresses, and that Host and Port are correct. If the database is stopped, start it and try again.
2. If Connection failed. Please verify the connection settings and credentials. appears, check the following. The error is the same regardless of the cause.
   • Host points to addresses that are used on the internet (Network).
   • TLS is enabled on the server (TLS and authentication).
   • The CA certificate is the certificate of the CA that issued the server certificate (TLS and authentication).
   • When Skip hostname verification is off, Host is a name included in the server certificate.
   • User is the role you created in Creating a role.
   • Password, Client certificate, and Client key are correct.
3. If invalid connection settings appears, check that the CA certificate, Client certificate, and Client key are in PEM format, and that the Client certificate and Client key are a matching pair.
4. If an error like PostgreSQL 13 is not supported. Use PostgreSQL 14 or later. appears, check the server version (Supported versions).
5. If an error that starts with The database refused the connection because it has reached its connection limit appears, check the connection limits of the database and the role.
6. If an error that starts with The database is not accepting connections right now appears, check whether the database is starting up or shutting down, and try again later.

When extension operators don't work

If SQL that uses operators from an extension (such as hstore, ltree, or pgvector) fails with operator does not exist, check the following.

1. Check whether the schema where the extension is installed is in the search_path of the role for Codatum.
2. If it isn't, add the schema to the role's search_path, for example with ALTER ROLE <role_name> SET search_path = "$user", public, <schema_name>.