---
title: "PostgreSQL"
description: "Results into a PostgreSQL table or through a procedure — scripts and specifics"
url: "https://docs.justcat.it/reference/outputs/postgres/"
---
# PostgreSQL


## When to use it

When results should be kept across runs and reported on — see [Database outputs](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") for what every database output does: one row per test as it finishes, columns matched by name, objects created when CAT may. This page has what is specific to PostgreSQL: the scripts and the procedure form. The target is a `Postgres@1` data source.

## How to set it


**Properties**


**Data sources**


Name
: DWH

Provider
: Postgres@1

Connection string
: %DWH_CONNECTION_STRING%




**Output**


Database
: DWH

Table
: public.cat_test_result





**YAML**


```yaml
Data sources:
- Name: DWH
  Provider: Postgres@1
  Connection string: "%DWH_CONNECTION_STRING%"

Output:
- Database: DWH
  Table: public.cat_test_result
```




or, calling a procedure for every result:


**Properties**



Database
: DWH

Procedure
: public.save_cat_test_result





**YAML**


```yaml
Output:
- Database: DWH
  Procedure: public.save_cat_test_result
```




The account needs `INSERT` on the table, or `EXECUTE` on the procedure. If the object does not exist and the account may create it, CAT creates it — the procedure together with a `public.cat_test_result` table.

## What it looks like

One row per test, the columns of your table — see the [rows of the sample run](https://docs.justcat.it/reference/outputs/database-outputs/#what-it-looks-like "What it looks like") on Database outputs.

## Details

### Table

Any subset of these columns works; custom columns must be nullable or have a default. The table may live in any schema — name it in `Table`.

```sql
CREATE TABLE public.cat_test_result
(
    test_result_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    execution_guid UUID NOT NULL,
    project VARCHAR(200) NOT NULL,
    full_name VARCHAR(700) NOT NULL,
    test_result VARCHAR(30) NOT NULL,
    started_on TIMESTAMP NOT NULL,
    finished_on TIMESTAMP NOT NULL,
    raw_message TEXT NULL,
    exception TEXT NULL,
    description TEXT NULL,
    first_data_source VARCHAR(200) NOT NULL,
    first_query TEXT NOT NULL,
    second_data_source VARCHAR(200) NULL,
    second_query TEXT NULL,
    expectation VARCHAR(50) NOT NULL,
    key VARCHAR(200) NULL,
    metadata VARCHAR(300) NULL,
    tags VARCHAR(1000) NULL,
    suite VARCHAR(200) NULL,
    test_case VARCHAR(200) NULL,
    "order" INT NULL,
    "name" VARCHAR(300) NULL,
    timeout INT NULL,
    maximum_errors_logged INT NULL,
    thread_number INT NOT NULL,
    message TEXT NULL,
    number_of_errors INT NULL,
    log_number_of_errors BOOLEAN NULL
);
```

### Procedure

Called with `CALL`; parameters matched by name (optionally prefixed `C_`), any subset — CAT reads the procedure's parameters before the run and passes only those.

```sql
create procedure public.save_cat_test_result
(
    c_execution_guid UUID,
    c_project VARCHAR(200),
    c_full_name VARCHAR(700),
    c_test_result VARCHAR(30),
    c_started_on TIMESTAMP,
    c_finished_on TIMESTAMP,
    c_raw_message TEXT,
    c_exception TEXT,
    c_description TEXT,
    c_first_data_source VARCHAR(200),
    c_first_query TEXT,
    c_second_data_source VARCHAR(200),
    c_second_query TEXT,
    c_expectation VARCHAR(50),
    c_key VARCHAR(200),
    c_metadata VARCHAR(300),
    c_tags VARCHAR(1000),
    c_suite VARCHAR(200),
    c_test_case VARCHAR(200),
    c_order INT,
    c_name VARCHAR(300),
    c_timeout INT,
    c_maximum_errors_logged INT,
    c_thread_number INT,
    c_message TEXT,
    c_number_of_errors INT,
    c_log_number_of_errors BOOLEAN
)
language plpgsql
as
$$
begin
    insert into "public"."cat_test_result"
    (
        execution_guid, project, full_name, test_result, started_on, finished_on, raw_message, exception, description,
        first_data_source, first_query, second_data_source, second_query, expectation,
        "key", "metadata", tags, suite, test_case, "order", "name",
        "timeout", maximum_errors_logged, thread_number, "message", number_of_errors, log_number_of_errors
    )
    values (
        c_execution_guid, c_project, c_full_name, c_test_result, c_started_on, c_finished_on, c_raw_message, c_exception, c_description,
        c_first_data_source, c_first_query, c_second_data_source, c_second_query, c_expectation,
        c_key, c_metadata, c_tags, c_suite, c_test_case, c_order, c_name,
        c_timeout, c_maximum_errors_logged, c_thread_number, c_message, c_number_of_errors, c_log_number_of_errors
    );
end
$$;
```

### Specifics

* Each thread of the run holds its own connection; results are written as the tests finish.
* `started_on` and `finished_on` are `TIMESTAMP`, UTC; `execution_guid` is a `UUID`; the long texts are `TEXT`.

## Related

* [Database outputs](https://docs.justcat.it/reference/outputs/database-outputs/ "Database outputs") — the shared mechanics.
* [Properties](https://docs.justcat.it/reference/outputs/properties/ "Properties") — the column names CAT recognizes.
* [Postgres@1](https://docs.justcat.it/reference/data-sources/providers/postgres-1/ "Postgres@1") — the data source.