Ingest data from PostgreSQL table

RisingWave allows you to query PostgreSQL tables directly with the postgres_query table-valued function (TVF). It offers a simpler alternative to Change Data Capture (CDC) when working with PostgreSQL data in RisingWave. Unlike CDC, which continuously syncs data changes, this function lets you fetch data directly from PostgreSQL when needed. Therefore, this approach is ideal for static or infrequently updated data, as it’s more resource-efficient than maintaining a constant CDC connection.

PUBLIC PREVIEWThis feature is currently in public preview, meaning it is nearing the final product but may not yet be fully stable. If you encounter any issues or have feedback, please reach out to us via our Slack channel. Your input is valuable in helping us improve this feature. For more details, see our Public Preview Feature List.

Added in version 2.1.

Syntax

Define postgres_query as follows:

postgres_query(
    hostname varchar,      -- Database hostname
    port varchar,          -- Database port
    username varchar,      -- Authentication username
    password varchar,      -- Authentication password
    database_name varchar, -- Target database name
    query varchar          -- SQL query to execute
)

Example

In your PostgreSQL database, create a table and populate it with sample data.

CREATE TABLE test (id bigint primary key, x int);
INSERT INTO test SELECT id, id::int FROM generate_series(1, 100) AS t(id);

In RisingWave, use postgres_query function to retrieve rows where id > 90.

SELECT * 
FROM postgres_query('localhost', '5432', 'postgres', 'postgres', 'mydb', 'SELECT * FROM test WHERE id > 90;');
----RESULT
91
92
93
94
95
96
97
98
99
100

Get started

Working with data

Deploy & operate

Performance

Troubleshooting

Reference

Cloud

Ingest data from PostgreSQL table

Syntax

Example

​Syntax

​Example

Syntax

Example