# PostgreSQL

> Query and change data in PostgreSQL, including Amazon RDS, Cloud SQL, Supabase and Neon: run SQL with safe parameters; find, count, select, insert, upsert, update and delete rows without writing SQL; create and drop tables; call procedures; look up schemas, tables, columns and indexes; and start a workflow when a row is added or changed.

App · 21 actions. Web page: https://docs.lodol.com/docs/integrations/postgres

## Actions

### Call procedure

Call a stored procedure or function with arguments and get back the rows it returns. A function's result comes back as rows; a procedure's OUT parameters as one row.

**Inputs**

- Procedure (`procedure`, text, required): The procedure's or function's name, e.g. add_customer. Use 'List procedures' to find one.
- Schema (`schema`, text): The procedure's schema. Leave empty for the default (public, or the user's search path).
- Arguments (`arguments`, list): The values to pass, in order, e.g. [42, "active"]; or by name, e.g. {"customer_id": 42}.
- Maximum rows (`max_rows`, number): Return at most this many rows from each result set. From 1 to 10000. Default: `1000`.

**Outputs**

- `procedure` (text): The procedure or function called
- `result_set_count` (number): How many sets of rows it returned
- `result_sets` (list): Every set of rows it returned, in order
- `rows` (list): The first result set's rows

**Example output**

```json
{
  "procedure": "add_one",
  "result_set_count": 1,
  "result_sets": [
    {
      "columns": [
        "result"
      ],
      "row_count": 1,
      "rows": [
        {
          "result": 42
        }
      ],
      "truncated": false
    }
  ],
  "rows": [
    {
      "result": 42
    }
  ]
}
```

### Count rows

How many rows in a table match the given values, or all of its rows when none are given.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Match rows where (`filters`, object): Column and value pairs every row must match, e.g. {"status": "active"}. A list matches any of its values; null matches a missing value (NULL), not empty text. To compare, give an operator: {"amount": {">=": 10}}, {"name": {"contains": "ada"}} or {"created_at": {"between": ["2026-01-01", "2026-02-01"]}}. Operators: =, !=, >, >=, <, <=, contains, not_contains, starts_with, ends_with, like, not_like, in, not_in, between, is_null. contains, starts_with and ends_with take the text as written and ignore case; like and not_like take a pattern, with % for any text and _ for one character.

**Outputs**

- `count` (number): Rows matching the filters
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "count": 42,
  "table": "customers"
}
```

### Create table

Create a table from a list of columns. By default an existing table of that name is left alone.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Columns (`columns`, list, required): One entry per column, written as name then type, e.g. 'id integer primary key auto_increment', 'email varchar(255) not null unique', 'amount numeric(10,2) default 0', 'created_at timestamptz default current_timestamp'. After the type: primary key, not null, unique, auto_increment, and default followed by a number, 'text', true, false, null or current_timestamp.
- Skip if it exists (`if_not_exists`, true/false): Leave an existing table alone instead of failing. Default: `true`.

**Outputs**

- `created` (true/false): False when the table already existed and was left alone
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "created": true,
  "table": "customers"
}
```

### Delete rows

Remove the rows matching the given values. At least one 'Match rows where' entry is required.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Match rows where (`filters`, object, required): Column and value pairs every row must match, e.g. {"id": 42}. Required, so the whole table can never be changed by accident. To compare, give an operator: {"amount": {">=": 10}}, {"name": {"contains": "ada"}} or {"created_at": {"between": ["2026-01-01", "2026-02-01"]}}. Operators: =, !=, >, >=, <, <=, contains, not_contains, starts_with, ends_with, like, not_like, in, not_in, between, is_null. contains, starts_with and ends_with take the text as written and ignore case; like and not_like take a pattern, with % for any text and _ for one character.

**Outputs**

- `rows_affected` (number): Rows the statement changed; null when the database doesn't say
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "rows_affected": 1,
  "table": "customers"
}
```

### Describe table

A table's or view's columns in order, with type, nullability and default, and its primary key.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).

**Outputs**

- `columns` (list): The table's columns, in order
- `count` (number): How many columns the table has
- `primary_key` (list): The primary key's columns, in order
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "columns": [
    {
      "character_maximum_length": null,
      "column_default": "nextval('customers_id_seq'::regclass)",
      "column_name": "id",
      "column_type": "int4",
      "data_type": "integer",
      "is_nullable": false,
      "is_primary_key": true,
      "numeric_precision": 32,
      "numeric_scale": 0,
      "ordinal_position": 1
    }
  ],
  "count": 1,
  "primary_key": [
    "id"
  ],
  "table": "customers"
}
```

### Drop table

Delete a table and every row in it. This cannot be undone.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Ignore if missing (`if_exists`, true/false): Succeed without doing anything if it is not there. Default: `true`.

**Outputs**

- `dropped` (true/false): False when there was no such table
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "dropped": true,
  "table": "customers"
}
```

### Find row

The first row matching the given values, or found: false when there is none, so a later step can branch on it. Without a sort column, which matching row comes back is up to the database.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Match rows where (`filters`, object): Column and value pairs every row must match, e.g. {"status": "active"}. A list matches any of its values; null matches a missing value (NULL), not empty text. To compare, give an operator: {"amount": {">=": 10}}, {"name": {"contains": "ada"}} or {"created_at": {"between": ["2026-01-01", "2026-02-01"]}}. Operators: =, !=, >, >=, <, <=, contains, not_contains, starts_with, ends_with, like, not_like, in, not_in, between, is_null. contains, starts_with and ends_with take the text as written and ignore case; like and not_like take a pattern, with % for any text and _ for one character.
- Sort by column (`order_by`, text): Column to sort on. Leave empty for no order.
- Sort direction (`sort_direction`, text): Which way to sort, when a sort column is given. One of: `ASC` (Ascending), `DESC` (Descending). Default: `ASC`.

**Outputs**

- `found` (true/false): True when a row matched
- `row` (object): The first matching row, or null
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "found": true,
  "row": {
    "email": "ada@example.com",
    "id": 1,
    "name": "Ada Lovelace"
  },
  "table": "customers"
}
```

### Get server info

The server's product and version, the database and the user signed in. The quickest way to check the connection works.

**Inputs**: None.

**Outputs**

- `current_schema` (text): Where unqualified table names are looked up
- `database` (text): The database connected to
- `product` (text): PostgreSQL, MySQL or MariaDB
- `server_time` (text): The server's clock, ISO 8601
- `user_name` (text): The user signed in
- `version` (text): The version number, such as 16.4
- `version_string` (text): The server's full version text

**Example output**

```json
{
  "current_schema": "public",
  "database": "postgres",
  "product": "PostgreSQL",
  "server_time": "2026-10-16T09:30:00+00:00",
  "user_name": "lodol",
  "version": "16.4",
  "version_string": "PostgreSQL 16.4 on x86_64-pc-linux-gnu"
}
```

### Insert row

Add one row to a table and get it back as stored, with its generated id and defaults.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Values (`values`, object, required): Column and value pairs, e.g. {"name": "Ada", "email": "ada@example.com"}. Leave out generated and default columns. An object or list is stored as JSON.

**Outputs**

- `row` (object): The inserted row as stored, with generated ids and defaults
- `rows_affected` (number): Rows the statement changed; null when the database doesn't say
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "row": {
    "email": "ada@example.com",
    "id": 43,
    "name": "Ada Lovelace"
  },
  "rows_affected": 1,
  "table": "customers"
}
```

### Insert rows

Add up to 1,000 rows in one go, all or nothing. A column missing from a row is stored as null.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Rows (`rows`, list, required): A list of rows, each a column and value object, e.g. [{"name": "Ada"}, {"name": "Grace"}].

**Outputs**

- `rows_affected` (number): Rows the statement changed; null when the database doesn't say
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "rows_affected": 2,
  "table": "customers"
}
```

### List databases

The databases on the server that accept connections (templates left out).

**Inputs**: None.

**Outputs**

- `count` (number): How many are listed
- `databases` (list): The databases the user can see, without the system's own

**Example output**

```json
{
  "count": 1,
  "databases": [
    {
      "name": "postgres"
    }
  ]
}
```

### List indexes

A table's indexes with their columns in key order, and which is the primary key.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).

**Outputs**

- `count` (number): How many are listed
- `indexes` (list): The table's indexes
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "count": 1,
  "indexes": [
    {
      "columns": [
        "id"
      ],
      "is_primary": true,
      "is_unique": true,
      "method": "btree",
      "name": "customers_pkey"
    }
  ],
  "table": "customers"
}
```

### List procedures

The stored procedures and functions the user can see, optionally with each one's parameters.

**Inputs**

- Schema (`schema`, text): Only this schema. Leave empty for every schema.
- Include parameters (`include_parameters`, true/false): Also list each one's parameters and types. Default: `false`.

**Outputs**

- `count` (number): How many are listed
- `procedures` (list): The procedures and functions, by schema and name

**Example output**

```json
{
  "count": 1,
  "procedures": [
    {
      "parameters": [
        {
          "data_type": "text",
          "mode": "IN",
          "name": "customer_name",
          "position": 1
        }
      ],
      "return_type": null,
      "routine_name": "add_customer",
      "routine_schema": "public",
      "routine_type": "PROCEDURE"
    }
  ]
}
```

### List schemas

The schemas in the connected database.

**Inputs**

- Include system schemas (`include_system`, true/false): Also list pg_catalog, information_schema and the other pg_ schemas. Default: `false`.

**Outputs**

- `count` (number): How many are listed
- `schemas` (list): The schemas; the system's own only when asked for

**Example output**

```json
{
  "count": 1,
  "schemas": [
    {
      "schema_name": "public"
    }
  ]
}
```

### List tables

The tables the user can see, in every schema or one, optionally with views.

**Inputs**

- Schema (`schema`, text): Only this schema. Leave empty for every schema.
- Include views (`include_views`, true/false): Also list views. Default: `false`.

**Outputs**

- `count` (number): How many are listed
- `tables` (list): The tables, by schema and name
- `truncated` (true/false): True when there were more than the limit and the rest were left out

**Example output**

```json
{
  "count": 1,
  "tables": [
    {
      "table_name": "customers",
      "table_schema": "public",
      "table_type": "BASE TABLE"
    }
  ],
  "truncated": false
}
```

### List views

The views the user can see, in every schema or one.

**Inputs**

- Schema (`schema`, text): Only this schema. Leave empty for every schema.

**Outputs**

- `count` (number): How many are listed
- `truncated` (true/false): True when there were more than the limit and the rest were left out
- `views` (list): The views, by schema and name

**Example output**

```json
{
  "count": 1,
  "truncated": false,
  "views": [
    {
      "table_name": "active_customers",
      "table_schema": "public",
      "table_type": "VIEW"
    }
  ]
}
```

### Run query

Run a SQL query and return its rows. Use %(name)s placeholders with 'Parameters' for any value that comes from data. The user's privileges decide what it can touch.

**Inputs**

- SQL query (`query`, text, required): The SQL to run, e.g. SELECT * FROM customers WHERE status = %(status)s. Write a literal % as %% when using parameters.
- Parameters (`parameters`, object): Values for each %(name)s in the SQL, e.g. {"status": "active"} for WHERE status = %(status)s. A list is sent as an array, for = ANY(%(ids)s). Passing values here, not pasting them into the SQL, keeps a quote in the data from changing the statement.
- Maximum rows (`max_rows`, number): Return at most this many rows. From 1 to 10000. Default: `1000`.

**Outputs**

- `columns` (list): Column names, in order
- `row_count` (number): Rows returned
- `rows` (list): The rows, each keyed by column
- `rows_affected` (number): Rows the statement changed; null when the database doesn't say
- `truncated` (true/false): True when more rows existed than the limit allowed

**Example output**

```json
{
  "columns": [
    "id",
    "name",
    "email"
  ],
  "row_count": 1,
  "rows": [
    {
      "email": "ada@example.com",
      "id": 1,
      "name": "Ada Lovelace"
    }
  ],
  "rows_affected": null,
  "truncated": false
}
```

### Run statement

Run an INSERT, UPDATE, DELETE or DDL statement (such as CREATE TABLE) and get the number of rows it changed. Use %(name)s placeholders with 'Parameters' for values.

**Inputs**

- SQL statement (`statement`, text, required): The SQL to run.
- Parameters (`parameters`, object): Values for each %(name)s in the SQL, e.g. {"status": "active"} for WHERE status = %(status)s. A list is sent as an array, for = ANY(%(ids)s). Passing values here, not pasting them into the SQL, keeps a quote in the data from changing the statement.

**Outputs**

- `rows_affected` (number): Rows the statement changed; null when the database doesn't say

**Example output**

```json
{
  "rows_affected": 1
}
```

### Select rows

Read rows from a table without writing SQL: choose columns, match on values, sort and page. A limit always applies.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Columns (`columns`, list): Columns to return. Leave empty for all.
- Match rows where (`filters`, object): Column and value pairs every row must match, e.g. {"status": "active"}. A list matches any of its values; null matches a missing value (NULL), not empty text. To compare, give an operator: {"amount": {">=": 10}}, {"name": {"contains": "ada"}} or {"created_at": {"between": ["2026-01-01", "2026-02-01"]}}. Operators: =, !=, >, >=, <, <=, contains, not_contains, starts_with, ends_with, like, not_like, in, not_in, between, is_null. contains, starts_with and ends_with take the text as written and ignore case; like and not_like take a pattern, with % for any text and _ for one character.
- Sort by column (`order_by`, text): Column to sort on. Leave empty for no order.
- Sort direction (`sort_direction`, text): Which way to sort, when a sort column is given. One of: `ASC` (Ascending), `DESC` (Descending). Default: `ASC`.
- Maximum rows (`limit`, number): Return at most this many rows. From 1 to 10000. Default: `100`.
- Skip rows (`offset`, number): Skip this many rows first, for paging. Set a sort column too, or the pages may overlap. From 0 to 100000000. Default: `0`.

**Outputs**

- `columns` (list): Column names, in order
- `row_count` (number): Rows returned
- `rows` (list): The rows, each keyed by column
- `table` (text): The table, with its schema when one was given
- `truncated` (true/false): True when more rows existed than the limit allowed

**Example output**

```json
{
  "columns": [
    "id",
    "name",
    "email"
  ],
  "row_count": 1,
  "rows": [
    {
      "email": "ada@example.com",
      "id": 1,
      "name": "Ada Lovelace"
    }
  ],
  "table": "customers",
  "truncated": false
}
```

### Update rows

Change columns on the rows matching the given values. At least one 'Match rows where' entry is required.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- New values (`values`, object, required): Column and value pairs to set, e.g. {"status": "inactive"}.
- Match rows where (`filters`, object, required): Column and value pairs every row must match, e.g. {"id": 42}. Required, so the whole table can never be changed by accident. To compare, give an operator: {"amount": {">=": 10}}, {"name": {"contains": "ada"}} or {"created_at": {"between": ["2026-01-01", "2026-02-01"]}}. Operators: =, !=, >, >=, <, <=, contains, not_contains, starts_with, ends_with, like, not_like, in, not_in, between, is_null. contains, starts_with and ends_with take the text as written and ignore case; like and not_like take a pattern, with % for any text and _ for one character.

**Outputs**

- `rows_affected` (number): Rows the statement changed; null when the database doesn't say
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "rows_affected": 1,
  "table": "customers"
}
```

### Upsert row

Update the row with the same key, or insert it if there is none, and get the row back as stored. The key defaults to the table's primary key; any other key needs a unique constraint on those columns.

**Inputs**

- Table (`table`, text, required): The table's name, e.g. customers (or sales.customers to name the schema too). Names are case-sensitive; 'List tables' shows them.
- Schema (`schema`, text): The table's schema. Leave empty for the default (public, or the user's search path).
- Values (`values`, object, required): Column and value pairs for the row, including its key, e.g. {"email": "ada@example.com", "name": "Ada"}.
- Match on columns (`key_columns`, list): The columns that identify the row. Leave empty to use the primary key.

**Outputs**

- `action` (text): INSERT or UPDATE; on MySQL, NONE when the row was already the same
- `key_columns` (list): The columns the row was matched on
- `row` (object): The row as stored after the upsert
- `rows_affected` (number): Rows the statement changed; null when the database doesn't say
- `table` (text): The table, with its schema when one was given

**Example output**

```json
{
  "action": "UPDATE",
  "key_columns": [
    "email"
  ],
  "row": {
    "email": "ada@example.com",
    "id": 43,
    "name": "Ada"
  },
  "rows_affected": 1,
  "table": "customers"
}
```
