---
warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there
title: psql
excerpt: |
  DuckDB Community Extensions
  Support for PSQL, a piped SQL dialect for DuckDB

extension:
  name: psql
  description: Support for PSQL, a piped SQL dialect for DuckDB
  version: 2.0.0
  language: C++
  build: cmake
  license: MIT
  opt_in_platforms: "osx_amd64;windows_arm64;linux_amd64_musl;linux_arm64_musl"
  maintainers:
    - ywelsch

repo:
  github: ywelsch/duckdb-psql
  andium: f0ee86e08020f4b7d7300da5be9c7a0f01c63dce
  variegata: 4e310a6056dff4808de4be4bd0c0e571ed6d7a5a
  ref: 13bea88da7050c72e4d33f7649fa72db58a22627

docs:
  hello_world: |
    from 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/invoices.csv' |>
    where invoice_date >= date '1970-01-16' |>
    extend
      0.8 as transaction_fees,
      total - transaction_fees as income |>
    where income > 1 |>
    select
      customer_id,
      sum(income) as sum_income
      group by all |>
    order by sum_income desc |>
    limit 10 |>
    as invoices
      join 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/customers.csv'
        as customers
      on invoices.customer_id = customers.customer_id |>
    select
      customer_id,
      last_name || ', ' || first_name as name,
      sum_income |>
    order by sum_income desc, name;
  extended_description: |
    PSQL extends DuckDB's SQL with a pipe syntax to provide simple composable queries. It's a lightweight variant of piped languages such as [PRQL](https://prql-lang.org) and [Kusto](https://docs.microsoft.com/azure/data-explorer/kusto/query/samples?pivots=azuredataexplorer), yet leveraging the full power of DuckDB's SQL.

    Every stage continues a `FROM`-first query on the result of the previous stage: `A |> B` is `FROM (A) B`. A stage can therefore contain anything that can follow the first table of a `FROM`-first query, such as joins, `SELECT`, `WHERE`, `GROUP BY` or `ORDER BY`, as well as an alias for that table, which needs `AS`, as in `|> as invoices` above.

    Pipelines can be used anywhere a query can appear: in subqueries, CTEs, views, `CREATE TABLE ... AS`, `INSERT INTO ...`, and so on. A final `|> to 'file'` stage (or `|> copy to 'file'`) writes the result of a pipeline to a file, a short-hand for `COPY (pipeline) TO 'file'`.

    PSQL also supports the operators of [BigQuery's pipe syntax](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax) that map directly to DuckDB's `SELECT`:

    - `|> extend expr [as alias], ...` is `|> select *, expr [as alias], ...`
    - `|> set column = expr, ...` is `|> select * replace (expr as column, ...)`
    - `|> drop column, ...` is `|> select * exclude (column, ...)`
    - `|> rename column [as] new_name, ...` is `|> select * rename (column as new_name, ...)`
    - `|> distinct` is `|> select distinct *`

    ```sql
    from 'https://raw.githubusercontent.com/ywelsch/duckdb-psql/main/example/invoices.csv' |>
    drop billing_address, billing_state, billing_postal_code |>
    rename billing_city as city, billing_country as country |>
    set invoice_date = invoice_date::date |>
    extend round(total * 0.8, 2) as income |>
    limit 3;
    ```

    PSQL requires DuckDB 2.0 or later, whose parser is extensible. The pipe syntax is a grammar extension, which loading PSQL activates on every connection. A connection can opt out with `reset active_grammar_extensions;`, and opt in again with `call psql_enable();`.

    See the [README](https://github.com/ywelsch/duckdb-psql) for more examples, and for the BigQuery operators that PSQL leaves out because DuckDB's SQL doesn't need them.

extension_star_count: 110
extension_star_count_pretty: 110
extension_download_count: 947
extension_download_count_pretty: 947
image: '/images/community_extensions/social_preview/preview_community_extension_psql.png'
layout: community_extension_doc
---
### Compatibility Matrix

This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

<div class="extension_compatibility_table"></div>

| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
| :--- | :---: | :---: | :---: | :---: | :---: |
| Linux AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Linux ARM64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| macOS AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| macOS ARM64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Windows AMD64 | ✅ | ❌ | ✅ | ✅ | ✅ |
| Windows ARM64 | ❌ | ❌ | ❌ | ❌ | ✅ |
| WebAssembly | ✅ | ❌ | ✅ | ✅ | ✅ |

### Installing and Loading
```sql
INSTALL {{ page.extension.name }} FROM community;
LOAD {{ page.extension.name }};
```

{% if page.docs.hello_world %}
### Example
```sql
{{ page.docs.hello_world }}```
{% endif %}

{% if page.docs.extended_description %}
### About {{ page.extension.name }}
{{ page.docs.extended_description }}
{% endif %}
### Added Functions

<div class="extension_functions_table"></div>

| function_name | function_type | description | comment | examples |
|---------------|---------------|-------------|---------|----------|
| psql_enable   | table         | NULL        | NULL    |          |

### Overloaded Functions

<div class="extension_functions_table"></div>

This extension does not add any function overloads.

### Added Types

<div class="extension_types_table"></div>

This extension does not add any types.

### Added Settings

<div class="extension_settings_table"></div>

|           name            |                       description                       | input_type | scope  | aliases |  typed_value  |
|---------------------------|---------------------------------------------------------|------------|--------|---------|---------------|
| active_grammar_extensions | The grammar extensions used by the parser.              | VARCHAR[]  | LOCAL  | []      | [shell, psql] |
| temp_directory            | Set the directory to which temporary files are written. | VARCHAR    | GLOBAL | []      | post.db.tmp   |