1.1 Access PostgreSQL

First, you need to access your PostgreSQL database using a command-line tool like `psql` or a graphical user interface such as pgAdmin.

1.2 Choose Data to Export

Decide which tables or data you want to export from PostgreSQL. You can export entire tables or a subset of data using SQL queries.

1.3 Export Data to CSV

Use the PostgreSQL `COPY` command to export the data to a CSV file. Run the following command in the `psql` tool or your preferred PostgreSQL interface:

```sql

COPY (SELECT * FROM your_table_name) TO '/path/to/your/output.csv' WITH CSV HEADER;

```

Replace `your_table_name` with the name of the table you want to export and `/path/to/your/output.csv` with the path to the CSV file you want to create.

2.1 Install DuckDB

If you haven't already installed DuckDB, you can download it from the official website or install it using Python:

```bash

pip install duckdb

```

2.2 Start DuckDB

Start DuckDB using the command line or by running it in a Python script:

```python

import duckdb

conn = duckdb.connect(database=':memory:', read_only=False)

```

This will start an in-memory DuckDB instance. If you want to persist the data, provide a file path instead of `':memory:'`.

3.1 Create a Table in DuckDB

Before you can import the data, you need to create a table in DuckDB with the same schema as the PostgreSQL table you exported. You can do this using the DuckDB SQL interface:

```sql

CREATE TABLE your_table_name (

    column1 datatype1,

    column2 datatype2,

    ...

);

```

Replace `your_table_name`, `column1`, `column2`, `datatype1`, `datatype2`, etc., with the appropriate table name and column definitions.

3.2 Import Data from CSV

Use the DuckDB `COPY` command to import the CSV file into the newly created table:

```sql

COPY your_table_name FROM '/path/to/your/output.csv' WITH (FORMAT 'csv', HEADER);

```

Replace `your_table_name` with the name of the DuckDB table you created and `/path/to/your/output.csv` with the path to the CSV file you exported from PostgreSQL.

4.1 Check Row Counts

To ensure that all data has been transferred correctly, compare the row counts in both the PostgreSQL and DuckDB tables:

```sql

-- In PostgreSQL

SELECT COUNT(*) FROM your_table_name;

‍

-- In DuckDB

SELECT COUNT(*) FROM your_table_name;

```

‍

4.2 Sample Data Check

Perform a few sample data checks to verify that the data looks correct in DuckDB:

‍

```sql

SELECT * FROM your_table_name LIMIT 10;

```

5.1 Remove Temporary Files

If you no longer need the CSV file, you can delete it to free up space:

```bash

rm /path/to/your/output.csv

```

5.2 Close Connections

Close any database connections you have opened during the process:

```python

# For DuckDB

conn.close()

```