# Postgres Table

This class stores result log data in a Postgres table.

It is much better choice for production usage compared to Snowflake table, since OLTP databases are better suited for small transactions and indexed lookups.

Internally this storage relies on brand new [psycopg3](https://www.psycopg.org/psycopg3/docs/) package.

### Setup

1. Install SnowKill with additional dependency for Postgres:

```
pip install snowkill[postgres]
```

2. Setup and run a Postgres instance.
3. Create a Postgres table with the following structure:

```sql
CREATE TABLE snowkill_log
(
    query_id TEXT,
    check_result_level INTEGER,
    check_result_name TEXT,
    check_result_description TEXT,
    check_result_time TIMESTAMP WITHOUT TIME ZONE,

    PRIMARY KEY (query_id, check_result_level)
)
```

Please note the full name of created table. It should be passed to `PostgresTableStorage` constructor.

### Example

<pre class="language-python"><code class="lang-python">from psycopg import connect as postgres_connect
<strong>from snowkill import *
</strong>from snowkill.storage.postgres_table import PostgresTableStorage


postgres_connection = postgres_connect(getenv("POSTGRES_DSN"))

...

snowkill_storage = PostgresTableStorage(postgres_connection, getenv("POSTGRES_TARGET_TABLE"))
check_results = snowkill_storage.store_and_remove_duplicate(check_results)
</code></pre>

### Usage notes

* It is important to import `PostgresTableStorage` separately, using `from snowkill.storage.postgres_table import PostgresTableStorage`. This storage class is not automatically exported with `snowkill` module, since it would trigger import error if `psycopg` is not installed.