Extensions
Writing Data to PostgreSQL
In addition to reading data from PostgreSQL, the extension allows you to create tables, ingest data into PostgreSQL and make other modifications to a PostgreSQL database using standard SQL queries.  
This allows you to use DuckDB to, for example, export data that is stored in a PostgreSQL database to Parquet, or read data from a Parquet file into PostgreSQL.  
Below is a brief example of how to create a new table in PostgreSQL and load data into it.  
```sql
ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES);
CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);
INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');
```  
Many operations on PostgreSQL tables are supported. All these operations directly modify the PostgreSQL database, and the result of subsequent operations can then be read using PostgreSQL.
Note that if modifications are not desired, `ATTACH` can be run with the `READ_ONLY` property which prevents making modifications to the underlying database. For example:  
```sql
ATTACH 'dbname=postgresscanner' AS postgres_db (TYPE POSTGRES, READ_ONLY);
```  
Below is a list of supported operations.  
#### `CREATE TABLE` {#docs:extensions:postgres::create-table}  
```sql
CREATE TABLE postgres_db.tbl (id INTEGER, name VARCHAR);
```  
#### `INSERT INTO` {#docs:extensions:postgres::insert-into}  
```sql
INSERT INTO postgres_db.tbl VALUES (42, 'DuckDB');
```  
#### `SELECT` {#docs:extensions:postgres::select}  
```sql
SELECT * FROM postgres_db.tbl;
```  
|   id | name   |
| ---: | ------ |
|   42 | DuckDB |  
#### `COPY` {#docs:extensions:postgres::copy}  
You can copy tables back and forth between PostgreSQL and DuckDB:  
```sql
COPY postgres_db.tbl TO 'data.parquet';
COPY postgres_db.tbl FROM 'data.parquet';
```  
These copies use [PostgreSQL binary wire encoding](https://www.postgresql.org/docs/current/sql-copy.html).
DuckDB can also write data using this encoding to a file which you can then load into PostgreSQL using a client of your choosing if you would like to do your own connection management:  
```sql
COPY 'data.parquet' TO 'pg.bin' WITH (FORMAT POSTGRES_BINARY);
```  
The file produced will be the equivalent of copying the file to PostgreSQL using DuckDB and then dumping it from PostgreSQL using `psql` or another client:  
DuckDB:  
```sql
COPY postgres_db.tbl FROM 'data.parquet';
```  
PostgreSQL:  
```sql
\copy tbl TO 'data.bin' WITH (FORMAT BINARY);
```  
You may also create a full copy of the database using the [`COPY FROM DATABASE` statement](#docs:sql:statements:copy::copy-from-database--to):  
```sql
COPY FROM DATABASE postgres_db TO my_duckdb_db;
```  
#### `UPDATE` {#docs:extensions:postgres::update}  
```sql
UPDATE postgres_db.tbl
SET name = 'Woohoo'
WHERE id = 42;
```  
#### `DELETE` {#docs:extensions:postgres::delete}  
```sql
DELETE FROM postgres_db.tbl
WHERE id = 42;
```  
#### `ALTER TABLE` {#docs:extensions:postgres::alter-table}  
```sql
ALTER TABLE postgres_db.tbl
ADD COLUMN k INTEGER;
```  
#### `DROP TABLE` {#docs:extensions:postgres::drop-table}  
```sql
DROP TABLE postgres_db.tbl;
```  
#### `CREATE VIEW` {#docs:extensions:postgres::create-view}  
```sql
CREATE VIEW postgres_db.v1 AS SELECT 42;
```  
#### `CREATE SCHEMA` / `DROP SCHEMA` {#docs:extensions:postgres::create-schema--drop-schema}  
```sql
CREATE SCHEMA postgres_db.s1;
CREATE TABLE postgres_db.s1.integers (i INTEGER);
INSERT INTO postgres_db.s1.integers VALUES (42);
SELECT * FROM postgres_db.s1.integers;
```  
|    i |
| ---: |
|   42 |  
```sql
DROP SCHEMA postgres_db.s1;
```