docs/integrations/data-integrations/postgresql.mdx
This documentation describes the integration of MindsDB with PostgreSQL, a powerful, open-source, object-relational database system. The integration allows MindsDB to access data stored in the PostgreSQL database and enhance PostgreSQL with AI capabilities.
<Tip> This data source integration is thread-safe, utilizing a connection pool where each thread is assigned its own connection. When handling requests in parallel, threads retrieve connections from the pool as needed. </Tip>Before proceeding, ensure the following prerequisites are met:
Establish a connection to your PostgreSQL database from MindsDB by executing the following SQL command:
CREATE DATABASE postgresql_conn
WITH ENGINE = 'postgres',
PARAMETERS = {
"host": "127.0.0.1",
"port": 5432,
"database": "postgres",
"user": "postgres",
"schema": "data",
"password": "password"
};
Required connection parameters include the following:
user: The username for the PostgreSQL database.password: The password for the PostgreSQL database.host: The hostname, IP address, or URL of the PostgreSQL server.port: The port number for connecting to the PostgreSQL server.database: The name of the PostgreSQL database to connect to.Optional connection parameters include the following:
schema: The database schema to use. Default is public.sslmode: The SSL mode for the connection.connection_parameters: allows passing any PostgreSQL libpq parameters, such as:
* SSL settings: sslrootcert, sslcert, sslkey, sslcrl, sslpassword
* Network and reliability options: connect_timeout, keepalives, keepalives_idle, keepalives_interval, keepalives_count
* Session options: application_name, options, client_encoding
* Any other libpq-supported parameterThe following usage examples utilize the connection to PostgreSQL made via the CREATE DATABASE statement and named postgresql_conn.
Retrieve data from a specified table by providing the integration name, schema, and table name:
SELECT *
FROM postgresql_conn.table_name
LIMIT 10;
Run PostgreSQL-native queries directly on the connected PostgreSQL database:
SELECT * FROM postgresql_conn (
--Native Query Goes Here
SELECT
model,
COUNT(*) OVER (PARTITION BY model, year) AS units_to_sell,
ROUND((CAST(tax AS decimal) / price), 3) AS tax_div_price
FROM used_car_price
);
Follow this tutorial to see more use case examples. </Tip>