### Postgres Connection String Examples

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Examples of standard libpq connection strings using key=value pairs for connecting to a Postgres database.

```text
dbname=mydb
host=localhost port=5432 dbname=mydb user=postgres password=secret
hostaddr=192.168.1.100 port=5432 dbname=mydb connect_timeout=10
```

--------------------------------

### Setup PostgreSQL Database

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Script to create necessary test databases and tables in PostgreSQL. This is a one-time setup step.

```bash
# Create test database and tables
PGHOST=localhost PGPORT=5432 PGUSER=postgres PGPASSWORD=password \
  ./create-postgres-tables.sh
```

--------------------------------

### Install PostgreSQL Extension

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Commands to initialize and build the PostgreSQL extension for DuckDB. This is a one-time setup process.

```bash
cd duckdb-postgres
git submodule init
git pull --recurse-submodules
make
```

--------------------------------

### Example Connection Strings

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/configuration.md

These examples demonstrate various ways to format connection strings for the Postgres extension, including specifying host, port, database name, connection timeout, and SSL mode.

```sql
dbname=mydb
```

```sql
host=localhost port=5432 dbname=mydb connect_timeout=10 sslmode=require
```

```sql
hostaddr=192.168.1.100 port=5432 user=postgres password=secret dbname=mydb
```

--------------------------------

### Example Postgres Connection Strings

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

These are example connection strings for the ATTACH command, demonstrating various parameters. Refer to Postgres documentation for a full list.

```sql
dbname=postgresscanner
host=localhost port=5432 dbname=mydb connect_timeout=10
```

--------------------------------

### Example SQL Query with ORDER BY and LIMIT

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Demonstrates a query with an ORDER BY clause and a LIMIT, showcasing how these clauses can be pushed down to the Postgres server.

```sql
SELECT * FROM postgres_db.public.orders 
ORDER BY created_at DESC 
LIMIT 10;
```

--------------------------------

### Run DuckDB Shell with Unsigned Extensions

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Start the bundled DuckDB shell, allowing unsigned extensions to be loaded.

```bash
./build/release/duckdb -unsigned
```

--------------------------------

### Example SQL Query with Filters

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Illustrates a typical SQL query that includes multiple filter conditions (age, status, email) and a limit, demonstrating the pushdown architecture.

```sql
SELECT * FROM postgres_db.public.users 
WHERE age > 18 AND status = 'active' AND email LIKE '%@gmail.com'
LIMIT 1000;
```

--------------------------------

### Example SQL Query for CTID-based Parallelization

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

An example query to count all rows in a large table, illustrating how CTID-based parallelization would split the work across multiple tasks.

```sql
SELECT COUNT(*) FROM postgres_db.public.large_table;
```

--------------------------------

### PostgresTransaction::GetBeginTransactionQuery (Static)

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Generates the SQL query string for starting a transaction, based on specified isolation level and access mode.

```cpp
static string GetBeginTransactionQuery(
    PostgresIsolationLevel isolation_level,
    AccessMode access_mode
)
```

--------------------------------

### Get Postgres Server Version

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Detects and returns the version of the connected Postgres server.

```cpp
PostgresVersion GetPostgresVersion(ClientContext &context);
```

--------------------------------

### Get Database Path

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the database path or Data Source Name (DSN) for the current connection.

```cpp
string GetDBPath() override;
```

--------------------------------

### Get Associated Catalog

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves a pointer to the associated PostgresCatalog, if one has been set.

```cpp
optional_ptr<PostgresCatalog> GetCatalog() const
```

--------------------------------

### Pushdown Aggregate Function Example

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Demonstrates pushing down a COUNT(*) aggregate function to PostgreSQL for efficient execution. DuckDB receives a single result row.

```sql
SELECT COUNT(*) FROM postgres_db.public.users WHERE status = 'active';
```

--------------------------------

### PostgresTransaction::GetConnection

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves a PostgreSQL connection, starting a new transaction if one is not already active.

```cpp
PostgresConnection &GetConnection()
```

--------------------------------

### Querying PostgreSQL Tables with DuckDB

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/README.md

Examples of reading, writing, and performing complex queries on PostgreSQL tables using DuckDB SQL syntax. Ensure the PostgreSQL database is attached and accessible.

```sql
-- Read data
SELECT * FROM postgres_db.public.users LIMIT 10;

-- Write data
INSERT INTO postgres_db.public.archive 
SELECT * FROM duckdb_table WHERE archived = true;

-- Complex queries
SELECT u.*, COUNT(o.id) as order_count
FROM postgres_db.public.users u
LEFT JOIN postgres_db.public.orders o ON u.id = o.user_id
GROUP BY u.id
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC
LIMIT 100;
```

--------------------------------

### Fixed-Dimensional 2D Array Handling

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Shows an example of a supported multidimensional array operation in DuckDB, where dimensions are fixed and consistent.

```sql
SELECT array[ARRAY[1,2], ARRAY[3,4]] FROM postgres_table;
-- Works: 2D integer array with fixed dimensions

```

--------------------------------

### Get Index Information for a Postgres Table

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves index information for a specified table in the attached Postgres database.

```cpp
vector<IndexInfo> GetIndexInfo(const string &table_name);
```

--------------------------------

### Execute Simple SQL Query Directly

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Example of a simple SQL query that DuckDB can execute directly against a PostgreSQL database without complex optimization.

```sql
SELECT * FROM postgres_db.public.table WHERE id = 1;
```

--------------------------------

### Query Attached PostgreSQL Tables

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Examples of querying tables from an attached PostgreSQL database within DuckDB. Includes selecting, counting, and inserting data.

```sql
D SELECT * FROM postgres_db.public.users LIMIT 10;

D SELECT count(*) FROM postgres_db.public.orders;

D INSERT INTO postgres_db.public.archive 
  SELECT * FROM local_table;
```

--------------------------------

### Postgres Trigger Execution Example

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/operations.md

Illustrates how a trigger defined on a target table in PostgreSQL will automatically execute when operations are performed on that table via DuckDB.

```sql
-- Trigger on target_table fires during INSERT/UPDATE/DELETE
INSERT INTO postgres_db.public.target_table ...;
-- Any triggers defined on target_table execute on Postgres
```

--------------------------------

### PostgresTransaction Public Methods

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Provides methods for managing Postgres transactions, including starting, committing, rolling back, and executing queries.

```APIDOC
## PostgresTransaction Public Methods

### Description
Provides methods for managing Postgres transactions, including starting, committing, rolling back, and executing queries.

### Methods
#### Start
- **Signature**: `void Start()`
- **Description**: Begins a transaction on the Postgres connection.

#### Commit
- **Signature**: `void Commit()`
- **Description**: Commits the active transaction.

#### Rollback
- **Signature**: `void Rollback()`
- **Description**: Rolls back the active transaction.

#### GetConnectionWithoutTransaction
- **Signature**: `PostgresConnection &GetConnectionWithoutTransaction()`
- **Description**: Returns a connection without an active transaction.

#### GetConnection
- **Signature**: `PostgresConnection &GetConnection()`
- **Description**: Returns a connection with an active transaction (starts one if needed).

#### GetContext
- **Signature**: `optional_ptr<ClientContext> GetContext()`
- **Description**: Returns the client context.

#### GetDSN
- **Signature**: `string GetDSN()`
- **Description**: Returns the connection DSN.

#### Query
- **Signature**: `unique_ptr<PostgresResult> Query(const string &query)`
- **Description**: Executes a query within the transaction.

#### QueryWithoutTransaction
- **Signature**: `unique_ptr<PostgresResult> QueryWithoutTransaction(const string &query)`
- **Description**: Executes a query outside of transaction context.

#### ExecuteQueries
- **Signature**: `vector<unique_ptr<PostgresResult>> ExecuteQueries(ClientContext &context, const string &queries)`
- **Description**: Executes multiple queries.

#### Get (Static)
- **Signature**: `static PostgresTransaction &Get(ClientContext &context, Catalog &catalog)`
- **Description**: Retrieves the Postgres transaction associated with a context and catalog.

#### GetBeginTransactionQuery (Static)
- **Signature**: `static string GetBeginTransactionQuery(PostgresIsolationLevel isolation_level, AccessMode access_mode)`
- **Description**: Generates the BEGIN TRANSACTION query for the given isolation level and access mode.

#### ReferenceEntry
- **Signature**: `optional_ptr<CatalogEntry> ReferenceEntry(shared_ptr<CatalogEntry> &entry)`
- **Description**: Manages references to catalog entries within the transaction.

#### GetTemporarySchema
- **Signature**: `string GetTemporarySchema()`
- **Description**: Returns the temporary schema name for the transaction.
```

--------------------------------

### Get Raw libpq Connection Pointer

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns a raw pointer to the libpq connection structure (PGconn*). This method throws an exception if the connection is not currently open.

```cpp
PGconn *GetConn() const;
```

--------------------------------

### Remove Type Aliases to Get Base Type

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

This C++ function simplifies a DuckDB LogicalType by removing any type aliases, returning the base type. Examples include mapping BPCHAR to VARCHAR and INT2 to SMALLINT.

```cpp
static LogicalType RemoveAlias(const LogicalType &type)
```

--------------------------------

### PostgresCatalog Initialize Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes the catalog and optionally loads built-in schemas from the attached PostgreSQL database.

```cpp
void Initialize(bool load_builtin) override
```

--------------------------------

### Execute Complex SQL Query with Optimization

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Example of a complex SQL query involving JOIN, GROUP BY, HAVING, ORDER BY, and LIMIT, which DuckDB analyzes and optimizes for execution against PostgreSQL.

```sql
SELECT t1.*, COUNT(*) 
FROM postgres_db.public.t1
JOIN postgres_db.public.t2 ON t1.id = t2.id
WHERE t1.status = 'active'
GROUP BY t1.id
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC
LIMIT 100;
```

--------------------------------

### Create Postgres Test Tables

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Set up the necessary database schema in a running Postgres instance for testing. Ensure libpq defaults are configured or specify connection details.

```bash
PGHOST=localhost PGPORT=5432 PGUSER=<user> PGPASSWORD=<password> ./create-postgres-tables.sh
```

--------------------------------

### Initialize

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes the catalog and loads schemas from the attached PostgreSQL database. Optionally loads built-in schemas.

```APIDOC
## Initialize

### Description
Initializes the catalog and loads schemas from the attached PostgreSQL database. Optionally loads built-in schemas.

### Method
`void Initialize(bool load_builtin)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **load_builtin** (bool) - Required - Whether to load built-in schemas
```

--------------------------------

### Get Associated Table Entry

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves a pointer to the associated PostgresTableEntry, if one has been set.

```cpp
optional_ptr<PostgresTableEntry> GetTable() const
```

--------------------------------

### PostgresCatalog PlanCreateTableAs Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Plans the physical execution operators for a 'CREATE TABLE AS SELECT' statement targeting the PostgreSQL database.

```cpp
PhysicalOperator &PlanCreateTableAs(
    ClientContext &context,
    PhysicalPlanGenerator &planner,
    LogicalCreateTable &op,
    PhysicalOperator &plan
) override
```

--------------------------------

### Get Pooled Connection

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves a connection from the connection pool. This function will block if no connections are currently available.

```cpp
PostgresPoolConnection GetConnection()
```

--------------------------------

### Enable ORDER BY Pushdown Configuration

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Shows how to enable the pushdown of ORDER BY clauses to the Postgres server for more efficient sorting and reduced data transfer.

```sql
SET pg_order_pushdown = true;
```

--------------------------------

### Create PostgreSQL Test Tables

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Sets up the necessary schema, tables, and sample data in a PostgreSQL database for testing the DuckDB extension. Requires user credentials and a running PostgreSQL instance.

```bash
PGHOST=localhost PGPORT=5432 PGUSER=<user> PGPASSWORD=<password> \
  ./create-postgres-tables.sh
```

--------------------------------

### Build the DuckDB Extension

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Compile the extension using the make command.

```bash
make
```

--------------------------------

### PostgresTransaction::Rollback

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Aborts the current transaction, discarding any changes made since the last commit or start.

```cpp
void Rollback()
```

--------------------------------

### PostgresTransaction::Start

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initiates a new transaction on the associated PostgreSQL connection.

```cpp
void Start()
```

--------------------------------

### Run All Tests

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Execute all unit tests for the extension, ensuring the Postgres test database is available and specifying the local extension repository.

```bash
POSTGRES_TEST_DATABASE_AVAILABLE=1 LOCAL_EXTENSION_REPO=./build/release/repository \n  ./build/release/test/unittest --autoloading available
```

--------------------------------

### Initialize PostgresBindData

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Constructor for PostgresBindData, which holds scan parameters and metadata for PostgreSQL table scans. Requires a ClientContext.

```cpp
PostgresBindData(ClientContext &context)
```

--------------------------------

### Get Row Count from Postgres Result

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the total number of rows contained within the PostgreSQL query result.

```cpp
idx_t Count()
```

--------------------------------

### Recommended Settings for Development/Debugging

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/configuration.md

Enable query printing for debugging and disable experimental filter and order pushdown for development or when troubleshooting extension behavior.

```sql
SET pg_debug_query_print=true;
SET pg_experimental_filter_pushdown=false;
SET pg_order_pushdown=false;
```

--------------------------------

### Get Health Check Query

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves the SQL query string used by the connection pool to validate the health of connections.

```cpp
std::string GetHealthCheckQuery()
```

--------------------------------

### PlanCreateTableAs

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Generates the physical operator plan for a `CREATE TABLE AS SELECT` statement targeting the PostgreSQL database.

```APIDOC
## PlanCreateTableAs

### Description
Generates the physical operator plan for a `CREATE TABLE AS SELECT` statement targeting the PostgreSQL database.

### Method
`PhysicalOperator &PlanCreateTableAs(ClientContext &context, PhysicalPlanGenerator &planner, LogicalCreateTable &op, PhysicalOperator &plan)`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **context** (ClientContext&) - Required - The client context
- **planner** (PhysicalPlanGenerator&) - Required - The physical plan generator
- **op** (LogicalCreateTable&) - Required - The logical CREATE TABLE operation
- **plan** (PhysicalOperator&) - Required - The existing physical plan to append to
```

--------------------------------

### Get Underlying Postgres Connection Wrapper

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the shared pointer to the underlying wrapper object that manages the low-level libpq connection.

```cpp
shared_ptr<OwnedPostgresConnection> GetConnection() const;
```

--------------------------------

### PostgresCatalog Constructor

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes a new PostgresCatalog instance to manage a connection to a PostgreSQL database. It requires client context, attached database information, connection details, access mode, schema to load, isolation level, and authentication credentials.

```APIDOC
## PostgresCatalog Constructor

### Description
Initializes a new PostgresCatalog instance to manage a connection to a PostgreSQL database. It requires client context, attached database information, connection details, access mode, schema to load, isolation level, and authentication credentials.

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **ctx** (ClientContext&) - Required - Database client context
- **db_p** (AttachedDatabase&) - Required - Reference to attached database
- **attach_path** (string) - Required - DSN or connection path to Postgres
- **access_mode** (AccessMode) - Required - READ_ONLY or READ_WRITE mode
- **schema_to_load** (string) - Required - Specific schema to load (empty loads all)
- **isolation_level** (PostgresIsolationLevel) - Required - Transaction isolation level (READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE)
- **secret_name** (const string&) - Required - Secret name for authentication
- **secret_storage_table_p** (SecretStorageTable) - Required - Secret storage configuration
```

--------------------------------

### Postgres Server-Side Execution of ORDER BY and LIMIT

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Illustrates the equivalent SQL query executed on the Postgres server when ORDER BY and LIMIT pushdown is enabled.

```sql
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 10;
```

--------------------------------

### Open a Postgres Connection

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Opens a new connection to a Postgres server using a Data Source Name (DSN) and an attach path. The DSN should be in libpq format.

```cpp
auto conn = PostgresConnection::Open("dbname=mydb host=localhost port=5432", "mydb");
```

--------------------------------

### PostgresConnection::BeginCopyTo

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initiates a COPY FROM STDIN operation for bulk data transfer to a specified table.

```APIDOC
## PostgresConnection::BeginCopyTo

### Description
Initiates a COPY FROM STDIN operation for bulk data transfer.

### Method
void

### Parameters
#### Path Parameters
- **context** (ClientContext&) - Required - The client context.
- **state** (PostgresCopyState&) - Required - The state object for the COPY operation.
- **format** (PostgresCopyFormat) - Required - The format for the COPY operation.
- **schema_name** (const string&) - Required - The name of the schema.
- **table_name** (const string&) - Required - The name of the table.
- **column_names** (const vector<string>&) - Required - The names of the columns to copy data into.
```

--------------------------------

### NULL Byte Error Example

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/operations.md

Demonstrates an error caused by inserting a NULL byte into a VARCHAR column. The `pg_null_byte_replacement` setting can be used to handle this.

```sql
INSERT INTO postgres_db.public.table (text_col) 
VALUES ('string with \0 null byte');
-- Error: VARCHAR values cannot contain NULL bytes
-- Fix: SET pg_null_byte_replacement='';

```

--------------------------------

### Type Conversion Error Example

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/operations.md

Illustrates a type conversion error that occurs when attempting to insert non-numeric data into an integer column in PostgreSQL.

```sql
INSERT INTO postgres_db.public.table (int_col) 
VALUES ('not a number');
-- Error: type conversion failed
```

--------------------------------

### PostgresConnectionPool Constructor

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes the PostgresConnectionPool, associating it with a catalog and client context.

```cpp
PostgresConnectionPool(PostgresCatalog &postgres_catalog, ClientContext &context)
```

--------------------------------

### CTID Usage in DELETE Statements

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Provides an example of using the CTID annotation to specify rows for deletion in a PostgreSQL table via DuckDB.

```sql
DELETE FROM table WHERE ctid = '(0,1)';

```

--------------------------------

### Show Tables from Attached Postgres Database

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

After attaching a Postgres database, you can list its tables using SHOW ALL TABLES.

```sql
SHOW ALL TABLES;
```

--------------------------------

### PostgresCatalog Constructor Signature

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Defines the parameters required to initialize a PostgresCatalog instance, including client context, database reference, connection details, access mode, schema loading preferences, isolation level, and authentication secrets.

```cpp
PostgresCatalog::PostgresCatalog(
    ClientContext &ctx,
    AttachedDatabase &db_p,
    string attach_path,
    AccessMode access_mode,
    string schema_to_load,
    PostgresIsolationLevel isolation_level,
    const string &secret_name,
    SecretStorageTable secret_storage_table_p
)
```

--------------------------------

### Get Affected Rows from Postgres Result

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the number of rows affected by a data modification query (e.g., INSERT, UPDATE, DELETE).

```cpp
idx_t AffectedRows()
```

--------------------------------

### Create Symlink for Development Auto-Loading

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Create a symbolic link to enable auto-loading of the Postgres extension for development purposes.

```bash
ln -sf "$(pwd)/build/reldebug/extension/postgres_scanner/postgres_scanner.duckdb_extension" ~/
.duckdb/extensions/${DUCKDB_VERSION}/${DUCKDB_PLATFORM}/postgres_scanner.duckdb_extension
```

--------------------------------

### Get String Reference from Postgres Result

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves a string reference from a specific row and column without copying the data. Useful for performance.

```cpp
string_t GetStringRef(idx_t row, idx_t col)
```

--------------------------------

### Get Default Health Check Query (Static)

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the default health check query string, which is 'SELECT 1'. This is a static method.

```cpp
static std::string DefaultHealthCheckQuery()
```

--------------------------------

### Configure Binary Copy Protocol Usage

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Demonstrates how to configure the use of the binary copy protocol for data transfer between DuckDB and Postgres. The default attempts binary first, then falls back to text.

```sql
SET pg_use_binary_copy = true;
```

```sql
SET pg_use_binary_copy = false;
```

--------------------------------

### Get Postgres Connection DSN

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the Data Source Name (DSN) string used to establish the current connection to the Postgres server.

```cpp
string GetDSN() const;
```

--------------------------------

### ConnectionOptionNames Static Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-functions.md

Returns a list of supported secret parameter names for PostgreSQL connections.

```cpp
static const std::vector<std::string> &ConnectionOptionNames()
```

--------------------------------

### Troubleshoot SSL Errors

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Addresses issues related to SSL context creation failures, often caused by uninitialized OpenSSL or outdated client libraries. Suggests ensuring OpenSSL initialization, upgrading libpq, or disabling SSL.

```bash
sudo apt-get install libpq-dev
```

--------------------------------

### PostgresConnection::BeginCopyFrom

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initiates a COPY TO STDOUT operation, expecting a specific result status.

```APIDOC
## PostgresConnection::BeginCopyFrom

### Description
Initiates a COPY TO STDOUT operation.

### Method
void

### Parameters
#### Path Parameters
- **context** (ClientContext&) - Required - The client context.
- **query** (const string&) - Required - The query to execute for COPY TO STDOUT.
- **expected_result** (ExecStatusType) - Required - The expected execution status type.
```

--------------------------------

### Load the Postgres Extension

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/README.md

Load the DuckDB Postgres extension using its file path. Ensure the path points to the correct extension file.

```sql
LOAD 'path/to/postgres_scanner.duckdb_extension';
```

--------------------------------

### Initialize OpenSSL for Secure Connections

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

These C++ calls initialize OpenSSL, which is required for secure connections when the Postgres extension is loaded.

```cpp
OPENSSL_init_crypto(0, nullptr);
OPENSSL_init_ssl(0, nullptr);
```

--------------------------------

### Get Table Statistics Function Signature

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

C++ function signature for retrieving statistics of a specific column from a PostgreSQL table within a DuckDB client context.

```cpp
unique_ptr<BaseStatistics> GetStatistics(ClientContext &context, column_t column_id) override;
```

--------------------------------

### Attach Postgres Database with Standard Secret

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Create a secret for standard Postgres authentication and then attach the database using that secret.

```sql
CREATE SECRET pg_secret (
    TYPE POSTGRES,
    HOST 'localhost',
    PORT '5432',
    USER 'postgres',
    PASSWORD 'mypassword',
    DATABASE 'mydb'
);

ATTACH '' AS postgres_db (TYPE POSTGRES, SECRET pg_secret);
```

--------------------------------

### Symlink Latest Build for Testing

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Create a symbolic link to ensure the latest build of the extension is used during testing. This avoids overwriting on subsequent loads.

```bash
DUCKDB_VERSION=$(ls build/reldebug/repository/)
DUCKDB_PLATFORM=$(ls build/reldebug/repository/${DUCKDB_VERSION}/)
ln -sf "$(pwd)/build/reldebug/extension/postgres_scanner/postgres_scanner.duckdb_extension"   ~/.duckdb/extensions/${DUCKDB_VERSION}/${DUCKDB_PLATFORM}/postgres_scanner.duckdb_extension
```

--------------------------------

### Attach Postgres Database with Connection String

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Attach a Postgres database using a connection string and assign it a catalog name.

```sql
ATTACH 'dbname=mydb host=localhost port=5432 user=postgres password=secret' 
  AS postgres_db (TYPE postgres);
```

--------------------------------

### PostgresCatalog GetConnectDisplay Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns a user-friendly string representation of the connection details for the attached PostgreSQL database, such as host and port.

```cpp
string GetConnectDisplay() override
```

--------------------------------

### PostgresTransaction Constructor

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes a PostgresTransaction object, linking it to the catalog, transaction manager, and client context.

```cpp
PostgresTransaction(
    PostgresCatalog &postgres_catalog,
    TransactionManager &manager,
    ClientContext &context
)
```

--------------------------------

### Initialize and Update DuckDB Submodules

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Initialize and pull the latest changes for the DuckDB submodule before building.

```bash
git submodule init
git pull --recurse-submodules
```

--------------------------------

### Enable ORDER BY Pushdown

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Enables the pushdown of ORDER BY clauses to the PostgreSQL data source, optimizing sorting operations.

```sql
SET pg_order_pushdown = true
```

--------------------------------

### Initiate Postgres COPY FROM STDIN

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initiates a COPY FROM STDIN operation for bulk data transfer into a Postgres table. Requires specifying format, schema, table, and column names.

```cpp
void BeginCopyTo(
    ClientContext &context,
    PostgresCopyState &state,
    PostgresCopyFormat format,
    const string &schema_name,
    const string &table_name,
    const vector<string> &column_names
);
```

--------------------------------

### Recommended Settings for Small Ad-Hoc Queries

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/configuration.md

Tune performance for small, ad-hoc queries by reducing the maximum number of pooled connections and the number of pages processed per task.

```sql
SET pg_pool_max_connections=2;
SET pg_pages_per_task=100;
```

--------------------------------

### PostgresCatalog Supports Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Checks if the attached PostgreSQL catalog supports specific remote capabilities. It returns true for IS_REMOTE and CONNECT capabilities.

```cpp
bool Supports(RemoteCapability capability) const override
```

--------------------------------

### Initiate Postgres COPY TO STDOUT

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initiates a COPY TO STDOUT operation to retrieve data from a Postgres table based on a query. Requires specifying the expected result type.

```cpp
void BeginCopyFrom(
    ClientContext &context,
    const string &query,
    ExecStatusType expected_result
);
```

--------------------------------

### PostgresCatalog GetConnectionString Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the full DSN connection string used to connect to the attached PostgreSQL database.

```cpp
string GetConnectionString()
```

--------------------------------

### Run a Single Test

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Execute a specific unit test by providing its path. This is useful for targeted debugging.

```bash
POSTGRES_TEST_DATABASE_AVAILABLE=1 LOCAL_EXTENSION_REPO=./build/release/repository \n  ./build/release/test/unittest --autoloading available test/sql/storage/attach_simple.test
```

--------------------------------

### PostgresTableEntry Constructors

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Constructs a PostgresTableEntry. Use the appropriate constructor based on the table information provided.

```cpp
PostgresTableEntry(Catalog &catalog, SchemaCatalogEntry &schema, CreateTableInfo &info)
PostgresTableEntry(Catalog &catalog, SchemaCatalogEntry &schema, PostgresTableInfo &info)
```

--------------------------------

### PostgresConnection::GetConnection

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the underlying wrapper for the libpq connection.

```APIDOC
## PostgresConnection::GetConnection

### Description
Returns the underlying connection wrapper.

### Method
shared_ptr<OwnedPostgresConnection>
```

--------------------------------

### Set Global PostgreSQL Extension Settings

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Sets global configuration options for the PostgreSQL extension that apply to all newly attached databases. These include binary copy protocol, pool size, and query optimization.

```sql
-- Binary copy protocol
SET pg_use_binary_copy = true;

-- Connection pool size
SET pg_pool_max_connections = 8;

-- Parallel scan settings
SET pg_use_ctid_scan = true;
SET pg_pages_per_task = 1000;

-- Query optimization
SET pg_experimental_filter_pushdown = true;
SET pg_order_pushdown = true;

-- Type handling
SET pg_array_as_varchar = false;
SET pg_null_byte_replacement = '';

-- Debugging
SET pg_debug_query_print = false;
```

--------------------------------

### PostgresCatalog GetDatabaseSize Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves size information, such as disk usage, for the attached PostgreSQL database.

```cpp
DatabaseSize GetDatabaseSize(ClientContext &context) override
```

--------------------------------

### Setting NULL Byte Replacement Character

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Demonstrates how to configure the replacement character for NULL bytes within VARCHAR strings when interacting with PostgreSQL via DuckDB.

```sql
SET pg_null_byte_replacement = '';  -- Remove NULL bytes (default behavior)
SET pg_null_byte_replacement = '_'; -- Replace with underscore

```

--------------------------------

### PostgresConnectionPool Constructor

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes a PostgresConnectionPool for managing reusable Postgres connections.

```APIDOC
## PostgresConnectionPool Constructor

### Description
Initializes a PostgresConnectionPool for managing reusable Postgres connections.

### Constructor
- `PostgresConnectionPool(PostgresCatalog &postgres_catalog, ClientContext &context)`

### Parameters
- **postgres_catalog** (PostgresCatalog&) - Required - Associated catalog
- **context** (ClientContext&) - Required - Client context
```

--------------------------------

### PostgresConnection::GetConn

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Returns the raw libpq connection pointer. Throws an exception if the connection is not established.

```APIDOC
## PostgresConnection::GetConn

### Description
Returns the raw libpq connection pointer. Throws if not connected.

### Method
PGconn *
```

--------------------------------

### PostgresTransaction::Get (Static)

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Statically retrieves the PostgresTransaction instance associated with a given client context and catalog.

```cpp
static PostgresTransaction &Get(ClientContext &context, Catalog &catalog)
```

--------------------------------

### Set Extension Configuration Options

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/configuration.md

Configure global settings for the Postgres extension using the SET command. These options control aspects like data transfer protocol and parallelization.

```sql
SET pg_use_binary_copy = true;
```

```sql
SET pg_pages_per_task = 1000;
```

```sql
SET pg_connection_limit = 10;
```

--------------------------------

### Run All Tests

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Executes all unit tests for the PostgreSQL extension, ensuring comprehensive validation. Requires a PostgreSQL test database to be available.

```bash
POSTGRES_TEST_DATABASE_AVAILABLE=1 \
  LOCAL_EXTENSION_REPO=./build/release/repository \
  ./build/release/test/unittest --autoloading available
```

--------------------------------

### Recommended Settings for Large Table Scans

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/configuration.md

Optimize performance for large table scans by enabling binary copy, CTID scans, and setting appropriate values for `pages_per_task` and `pool_max_connections`.

```sql
SET pg_use_binary_copy=true;
SET pg_use_ctid_scan=true;
SET pg_pages_per_task=1000;
SET pg_pool_max_connections=8;
```

--------------------------------

### PostgresCatalog GetPostgresVersion Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves the detected version of the connected PostgreSQL server.

```cpp
PostgresVersion GetPostgresVersion() const
```

--------------------------------

### Projection Pushdown (Column Selection)

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Demonstrates projection pushdown, where DuckDB requests only the necessary columns from PostgreSQL, reducing network traffic and improving performance.

```sql
SELECT col1, col3 FROM postgres_db.public.table;
```

--------------------------------

### PostgresCatalog PlanUpdate Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Plans the physical execution operators for an UPDATE statement targeting tables in the PostgreSQL database.

```cpp
PhysicalOperator &PlanUpdate(
    ClientContext &context,
    PhysicalPlanGenerator &planner,
    LogicalUpdate &op,
    PhysicalOperator &plan
) override
```

--------------------------------

### Include Directories

Source: https://github.com/duckdb/duckdb-postgres/blob/main/src/CMakeLists.txt

Specifies include directories for the project. Use this to make header files accessible.

```cmake
include_directories(include)
```

--------------------------------

### Create PostgresVersion Object

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Constructs a PostgresVersion object. Can be default-constructed or initialized with major, minor, and optional patch versions.

```cpp
PostgresVersion()
PostgresVersion(idx_t major_v, idx_t minor_v, idx_t patch_v = 0)
```

--------------------------------

### PostgresConnection::Open

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Opens a new connection to a Postgres server using a Data Source Name (DSN) and an attach path.

```APIDOC
## PostgresConnection::Open

### Description
Opens a new connection to a Postgres server.

### Method
static PostgresConnection

### Parameters
#### Path Parameters
- **dsn** (const string&) - Required - Postgres connection string (libpq format)
- **attach_path** (const string&) - Required - Path for attaching (may differ from DSN)

### Response
#### Success Response
- **PostgresConnection** - New connection object

### Request Example
```cpp
auto conn = PostgresConnection::Open("dbname=mydb host=localhost port=5432", "mydb");
```
```

--------------------------------

### PostgresCatalog PlanInsert Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Plans the physical execution operators for an INSERT statement targeting tables in the PostgreSQL database.

```cpp
PhysicalOperator &PlanInsert(
    ClientContext &context,
    PhysicalPlanGenerator &planner,
    LogicalInsert &op,
    optional_ptr<PhysicalOperator> plan
) override
```

--------------------------------

### Development/Testing Configuration

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/README.md

Sets binary copy to true, limits pool connections, and disables query printing for development or testing environments.

```sql
SET pg_use_binary_copy = true;
SET pg_pool_max_connections = 2;
SET pg_debug_query_print = false;
```

--------------------------------

### SetSecretParameters Static Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-functions.md

Sets up the secret parameters required for the CREATE SECRET function related to PostgreSQL.

```cpp
static void SetSecretParameters(CreateSecretFunction &function)
```

--------------------------------

### Attach Postgres Database with Read-Write Access Mode

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Attach a Postgres database with READ_WRITE access mode, allowing operations like INSERT, UPDATE, DELETE, and CREATE TABLE.

```sql
ATTACH 'dbname=mydb' AS postgres_db (
    TYPE postgres,
    ACCESS_MODE 'READ_WRITE'
);
```

--------------------------------

### Troubleshoot Connection Failed Error

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Provides solutions for DNS resolution, network connectivity, or invalid hostname issues when connecting to a PostgreSQL database. Includes checking DNS, pinging the host, and using IP addresses.

```bash
ping db.example.com
```

--------------------------------

### NULL Byte Handling in VARCHAR (Default)

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Illustrates the default behavior of removing NULL bytes from VARCHAR strings when interacting with PostgreSQL, as defined by `pg_null_byte_replacement`.

```cpp
// VARCHAR with NULL byte: "hello\0world"
if (memchr(str_data, '\0', str_size) != nullptr) {
    // Replace all \0 with replacement string
    // "hello\0world" -> "helloworld" (if replacement = '')
    // "hello\0world" -> "hello_world" (if replacement = '_')
}

```

--------------------------------

### PostgresTableEntry Constructors

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Constructs a PostgresTableEntry. It can be initialized with either a generic CreateTableInfo or a specific PostgresTableInfo.

```APIDOC
## PostgresTableEntry Constructors

### Description
Constructs a PostgresTableEntry. It can be initialized with either a generic CreateTableInfo or a specific PostgresTableInfo.

### Constructors
- `PostgresTableEntry(Catalog &catalog, SchemaCatalogEntry &schema, CreateTableInfo &info)`
- `PostgresTableEntry(Catalog &catalog, SchemaCatalogEntry &schema, PostgresTableInfo &info)`

### Parameters
#### Constructor 1 & 2
- **catalog** (Catalog&) - Required - Parent catalog
- **schema** (SchemaCatalogEntry&) - Required - Parent schema
- **info** (CreateTableInfo& or PostgresTableInfo&) - Required - Table definition
```

--------------------------------

### Casting Custom Types to VARCHAR

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Demonstrates how unsupported custom PostgreSQL types are cast to VARCHAR for compatibility within DuckDB.

```sql
SELECT custom_type_column FROM postgres_table;
-- Result: VARCHAR representation

```

--------------------------------

### Production High Performance Configuration

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/README.md

Optimizes for performance in production by enabling binary copy, ctid scan, and experimental features, while setting a higher connection pool limit and page size.

```sql
SET pg_use_binary_copy = true;
SET pg_use_ctid_scan = true;
SET pg_pages_per_task = 1000;
SET pg_pool_max_connections = 8;
SET pg_experimental_filter_pushdown = true;
SET pg_order_pushdown = true;
```

--------------------------------

### Enable Filter Pushdown

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Enables experimental filter pushdown, allowing filters to be applied earlier in the query execution process on the PostgreSQL side.

```sql
SET pg_experimental_filter_pushdown = true
```

--------------------------------

### PostgresCatalog CreateSchema Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Creates a new schema within the attached PostgreSQL database based on the provided CreateSchemaInfo.

```cpp
optional_ptr<CatalogEntry> CreateSchema(
    CatalogTransaction transaction,
    CreateSchemaInfo &info
) override
```

--------------------------------

### Attach Postgres Database and Create Views

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-functions.md

Attaches a PostgreSQL database, creating DuckDB views for its tables. Allows specifying schemas, overwrite behavior, and filter pushdown.

```sql
SELECT * FROM postgres_attach(
    'dbname=mydb host=localhost',
    source_schema='public',
    sink_schema='postgres',
    overwrite=true,
    filter_pushdown=true
);
```

--------------------------------

### Compare Postgres Versions

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Enables comparison of PostgresVersion objects using standard operators (<, <=, >, >=). This allows for version-based logic.

```cpp
PostgresVersion v1(12, 0), v2(13, 0);
if (v1 < v2) {
    // v1 is older
}
```

--------------------------------

### PostgresCatalog RemoteExecute Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Executes a given SQL query directly on the remote PostgreSQL server and returns the results as a DuckDB TableRef.

```cpp
unique_ptr<TableRef> RemoteExecute(ClientContext &context, const string &sql) override
```

--------------------------------

### Create PostgreSQL Extension Library

Source: https://github.com/duckdb/duckdb-postgres/blob/main/src/CMakeLists.txt

Defines a library named 'postgres_ext_library' as an OBJECT library. This compiles the specified source files into object files without linking them into a final executable or shared library yet.

```cmake
add_library(
  postgres_ext_library OBJECT
  postgres_attach.cpp
  postgres_aws.cpp
  postgres_binary_copy.cpp
  postgres_binary_file_reader.cpp
  postgres_binary_parser.cpp
  postgres_binary_reader.cpp
  postgres_connection.cpp
  postgres_copy_from.cpp
  postgres_copy_to.cpp
  postgres_execute.cpp
  postgres_extension.cpp
  postgres_filter_pushdown.cpp
  postgres_hstore.cpp
  postgres_parameters.cpp
  postgres_query.cpp
  postgres_scanner.cpp
  postgres_secrets.cpp
  postgres_storage.cpp
  postgres_text_reader.cpp
  postgres_utils.cpp
  postgres_logging.cpp
  postgres_oauth.cpp)
```

--------------------------------

### PGConnect Utility Function

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-functions.md

A static utility function to establish a libpq connection to a PostgreSQL database using a Data Source Name (DSN) and an attach path.

```cpp
static PGconn *PGConnect(const string &dsn, const string &attach_path)
```

--------------------------------

### JSONB Annotation for Binary JSON

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Highlights the JSONB annotation, which optimizes handling of PostgreSQL's binary JSON format for improved storage and performance in DuckDB.

```cpp
PostgresTypeAnnotation::JSONB

```

--------------------------------

### Enable Binary Copy Protocol

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/operations.md

Configures DuckDB to use the BINARY COPY protocol for INSERT operations into PostgreSQL. This is enabled by default.

```sql
SET pg_use_binary_copy = true;  -- Enable (default)
```

--------------------------------

### Optimized Table Scanning with Pushdown

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Use `postgres_scan_pushdown` for optimized scanning that supports WHERE, ORDER BY, and LIMIT clause pushdown to the PostgreSQL server.

```sql
SELECT * FROM postgres_scan_pushdown(
    'dbname=mydb',
    'public',
    'users'
) WHERE status = 'active' ORDER BY created_at LIMIT 100;
```

--------------------------------

### PostgresCatalog PlanMergeInto Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Plans the physical execution operators for a MERGE INTO statement targeting tables in the PostgreSQL database.

```cpp
PhysicalOperator &PlanMergeInto(
    ClientContext &context,
    PhysicalPlanGenerator &planner,
    LogicalMergeInto &op,
    PhysicalOperator &plan
) override
```

--------------------------------

### Real-time Reporting View and Query

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/README.md

Creates a view that combines order, customer, and order item data to calculate total items and value for recent orders, and then queries this view to find high-value orders.

```sql
-- Combine Postgres tables
CREATE VIEW report AS
SELECT 
  o.id,
  c.name,
  SUM(oi.quantity) as total_items,
  SUM(oi.price * oi.quantity) as total_value
FROM postgres_db.public.orders o
JOIN postgres_db.public.customers c ON o.customer_id = c.id
JOIN postgres_db.public.order_items oi ON o.id = oi.order_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.id, c.name;

-- Query many times
SELECT * FROM report WHERE total_value > 1000 ORDER BY total_value DESC;
```

--------------------------------

### PostgresTransaction Constructor

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Initializes a PostgresTransaction, managing connections and transaction states for Postgres operations.

```APIDOC
## PostgresTransaction Constructor

### Description
Initializes a PostgresTransaction, managing connections and transaction states for Postgres operations.

### Constructor
- `PostgresTransaction(PostgresCatalog &postgres_catalog, TransactionManager &manager, ClientContext &context)`

### Parameters
- **postgres_catalog** (PostgresCatalog&) - Required - Associated Postgres catalog
- **manager** (TransactionManager&) - Required - Transaction manager
- **context** (ClientContext&) - Required - Client context
```

--------------------------------

### Configure PostgreSQL Connection Pool

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Demonstrates how to adjust the connection pool size for the PostgreSQL catalog in DuckDB. This is useful for managing concurrent query loads and optimizing performance.

```sql
FROM postgres_configure_pool(catalog_name='db', max_connections=20);
```

```sql
FROM postgres_configure_pool(catalog_name='db', max_connections=0);
```

--------------------------------

### Run Specific Test

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Executes a specific unit test file for the PostgreSQL extension. Useful for targeted debugging. Requires a PostgreSQL test database.

```bash
POSTGRES_TEST_DATABASE_AVAILABLE=1 \
  LOCAL_EXTENSION_REPO=./build/release/repository \
  ./build/release/test/unittest --autoloading available test/sql/storage/attach_simple.test
```

--------------------------------

### PostgresConnectionPool::TryGetConnection

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Attempts to acquire a pooled PostgreSQL connection without blocking. Returns true if a connection was successfully obtained.

```cpp
bool TryGetConnection(PostgresPoolConnection &connection)
```

--------------------------------

### CreateFunction Static Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-functions.md

Creates a PostgreSQL secret object from input parameters provided within a client context.

```cpp
static unique_ptr<BaseSecret> CreateFunction(ClientContext &context, CreateSecretInput &input)
```

--------------------------------

### Query Postgres and Retrieve Results

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Executes a query and returns the results. This method throws an exception if the query execution fails.

```cpp
unique_ptr<PostgresResult> Query(
    optional_ptr<ClientContext> context,
    const string &query,
    const PostgresParameters &params = PostgresParameters()
);
```

--------------------------------

### PostgresCatalog GetConnectionPool Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Provides direct access to the PostgresConnectionPool object responsible for managing connections to the PostgreSQL database.

```cpp
PostgresConnectionPool &GetConnectionPool()
```

--------------------------------

### PostgresCatalog PlanDelete Method

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Plans the physical execution operators for a DELETE statement targeting tables in the PostgreSQL database.

```cpp
PhysicalOperator &PlanDelete(
    ClientContext &context,
    PhysicalPlanGenerator &planner,
    LogicalDelete &op,
    PhysicalOperator &plan
) override
```

--------------------------------

### HSTORE Type Handling

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/data-types.md

Details the support for PostgreSQL's HSTORE (key-value) type in DuckDB, which results in a STRUCT representation if the extension is registered.

```sql
CREATE EXTENSION hstore;

SELECT hstore_column FROM postgres_table;
-- Result: STRUCT with key-value pairs (if registered)

```

--------------------------------

### Execute Multiple Queries on Postgres

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Submits multiple SQL queries for execution on the remote server. Returns a vector containing the results for each query.

```cpp
vector<unique_ptr<PostgresResult>> ExecuteQueries(
    ClientContext &context,
    const string &queries
);
```

--------------------------------

### Attach to a Postgres Database

Source: https://github.com/duckdb/duckdb-postgres/blob/main/README.md

Use the ATTACH command to make a Postgres database accessible to DuckDB. This command takes a libpq connection string as input.

```sql
ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE postgres);
```

--------------------------------

### Supports

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Checks if the attached PostgreSQL catalog supports a given remote capability, such as direct connection or being a remote data source.

```APIDOC
## Supports

### Description
Checks if the attached PostgreSQL catalog supports a given remote capability, such as direct connection or being a remote data source.

### Method
`bool Supports(RemoteCapability capability) const`

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Parameters
- **capability** (RemoteCapability) - Required - The remote capability to check for
```

--------------------------------

### Prepare and Execute SQL Statement

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

Use PREPARE and EXECUTE for parameterized queries in SQL. This helps prevent SQL injection and allows for query plan caching.

```sql
PREPARE stmt (INT) AS
  SELECT * FROM postgres_db.public.users WHERE id = $1;

EXECUTE stmt(42);
```

--------------------------------

### Build PostgreSQL Connector Library

Source: https://github.com/duckdb/duckdb-postgres/blob/main/src/dbconnector/CMakeLists.txt

Adds a CMake library target named 'postgres_ext_dbconnector' of type OBJECT. It lists the C++ source files that constitute the database connector extension.

```cmake
add_library(
  postgres_ext_dbconnector OBJECT
  ${POSTGRES_DBCONNECTOR_PATH}/src/query/query_writer.cpp
  ${POSTGRES_DBCONNECTOR_PATH}/src/table_scan/filter_pushdown.cpp
  ${POSTGRES_DBCONNECTOR_PATH}/src/table_scan/filter_util.cpp
  ${POSTGRES_DBCONNECTOR_PATH}/src/optimizer/aggregate_optimizer.cpp
  ${POSTGRES_DBCONNECTOR_PATH}/src/optimizer/optimizer_util.cpp
  ${POSTGRES_DBCONNECTOR_PATH}/src/optimizer/order_by_and_limit_optimizer.cpp)
```

--------------------------------

### CreateEmptyPostgresType Utility Function

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-functions.md

Creates an empty PostgresType structure based on a DuckDB LogicalType.

```cpp
static PostgresType CreateEmptyPostgresType(const LogicalType &type)
```

--------------------------------

### Equivalent Aggregate Pushdown in Postgres

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/query-execution.md

The equivalent SQL query executed on the PostgreSQL server when DuckDB pushes down the COUNT(*) aggregate function.

```sql
SELECT COUNT(*) FROM users WHERE status = 'active';
```

--------------------------------

### postgres_query

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Executes arbitrary SQL queries on a Postgres server.

```APIDOC
## postgres_query

### Description
Execute arbitrary SQL on Postgres server.

### Method
SELECT

### Endpoint
postgres_query

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
- **dsn** (VARCHAR) - Required - Postgres connection string
- **query** (VARCHAR) - Required - The SQL query to execute

### Request Example
```sql
SELECT * FROM postgres_query('dbname=mydb', 'SELECT * FROM users WHERE id > 100');
```

### Response
#### Success Response (200)
Query results with automatic type conversion

#### Response Example
```sql
-- Example response showing query results
-- (Actual data depends on the query executed)
```
```

--------------------------------

### Insert Data using Binary COPY Protocol

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/operations.md

Inserts a large dataset into a PostgreSQL table from a DuckDB table. This operation automatically utilizes the BINARY COPY protocol for significant performance gains.

```sql
INSERT INTO postgres_db.public.large_table
SELECT * FROM duckdb_large_table;
```

--------------------------------

### PostgresTableEntry::GetStorageInfo

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/api-reference-core-classes.md

Retrieves storage information for the table.

```cpp
TableStorageInfo GetStorageInfo(ClientContext &context) override
```

--------------------------------

### PostgresCopyState Structure

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/types.md

Manages the state for COPY operations in PostgreSQL, including format and NULL byte replacement settings. Use this to configure how data is copied to or from PostgreSQL.

```cpp
struct PostgresCopyState {
    PostgresCopyFormat format = PostgresCopyFormat::AUTO;
    bool has_null_byte_replacement = false;
    string null_byte_replacement;

    void Initialize(ClientContext &context);
};
```

--------------------------------

### Load PostgreSQL Extension into DuckDB Session

Source: https://github.com/duckdb/duckdb-postgres/blob/main/_autodocs/attachment-and-initialization.md

Commands to load the PostgreSQL extension into a DuckDB session. This can be done manually or by setting up auto-loading.

```bash
./build/release/duckdb -unsigned

D LOAD 'build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension';
```

```bash
ln -sf "$(pwd)/build/release/extension/postgres_scanner/postgres_scanner.duckdb_extension" \
  ~/.duckdb/extensions/$(duckdb -batch "SELECT value FROM duckdb_functions() WHERE function_name = 'duckdb_version'" | tr -d '"')/linux_amd64/postgres_scanner.duckdb_extension
```