# Notes on using PostgreSQL

### **Run SQL query**

When using a PostgreSQL connector to run SQL operations (such as `SELECT`, `INSERT`, `UPDATE`, or `DELETE`), the connector facilitates interaction between your application or tool and the PostgreSQL database.
A sample query to create a table in the Redshift database would be something similar to this: `CREATE TABLE customer ( id SERIAL, name VARCHAR(50), email VARCHAR(100), age INT ); `
![postresql-sql-query](https://tray.ai/documentation/images/connectors/service/postgresql/6nWdPYbOmpi4E1UjRLpzF8_postresql-sql-query.png)
For the ease of working with the PostgreSQL queries, refer to the PostreSQL [Queries](https://www.postgresql.org/docs/current/queries.html) document.

### Insert single record

To Insert a single record operation allows you to insert a single customer record into a PostgreSQL table. Once inserted, you can verify the record by using the **Find Rows** operation to read the inserted data.
The below example demonstrates how you can insert single record into a PostgreSQL table. While working with multiple records use [batch insert method](https://tray.ai/documentation/connectors/service/postgresql/notes-on-using-postgresql/#batch-insertion-of-records).
Consider the following JSON payload received by a Webhook, which contains a single customer record:

```json
{
    "table": "customer",
    "data": [
        \{
            "id": 1,
            "name": "Alice Johnson",
            "email": "alice.johnson@example.com",
            "age": 35
        \}
    ]
}
```

### **Batch insertion of records**

The workflow uses **Insert new rows** operation to insert multiple records in a table.
When inserting records, it's important to ensure that they are formatted correctly according to the table's schema. You can verify the expected format using [sample payloads](https://tray.ai/documentation/connectors/service/postgresql/postgresql-operations-sample-payloads/#insert-new-rows).
The workflow is designed to receive and process various types of data through a Webhook. For example, let's consider a scenario where the following JSON payload is received:

```json
{
    "table": "customer",
    "data": [
        {
            "id": 1001,
            "name": \{
                "first": "John",
                "last": "Doe"
            \},
            "contact": \{
                "email": "john.doe@example.com",
                "phone": "+1-555-123-4567"
            \}
        },
        {
            "id": 1002,
            "name": \{
                "first": "Jane",
                "last": "Smith"
            \},
            "contact": \{
                "email": "jane.smith@example.com",
                "phone": "+1-555-987-6543"
            \}
        }
    ]
}
```

To properly format this data for insertion into the PostgreSQL table, we used the JSON Transformer connector. Alternatively, you can achieve the same transformation using a script or Python connector. The following JSONata script is applied to structure the data as required for PostgreSQL:

```json
{
  "table": "customers",
  "data": $map(data, function($customer, $index) {
    \{
      "id": $index + 1,
      "name": $customer.name.first & " " & $customer.name.last,
      "email": $customer.contact.email,
      "age": $customer.id = 1001 ? 30 : 28
    \}
  })
}

```

This transformation prepares the customer data by extracting key fields like `id`, `name`, `email`, and `age` from the incoming JSON payload.
Once the data has been transformed, it is passed to the **PostgreSQL connector** to be inserted into the `customers` table.
![postgresql-insert-new-rows](https://tray.ai/documentation/images/connectors/service/postgresql/c9qmVBH8yWDc2OQsuv9L9_postgresql-insert-new-rows.png)