Go to appAcademyWhat's new?
Back
Artisan IMG > PostgreSQL (postgresql) (e8cbdd05-d792-44b8-85f7-994dbb7eb4e1)
PostgreSQL
OverviewOperations (sample payloads)Operations

Operations (sample payloads)

Main operations
Copy

Count rows
Copy

Counts the number of rows that meet a given set of conditions.

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "sales_db",
5
    "schema": "public",
6
    "user": "db_user",
7
    "password": "securePassword123",
8
    "ssl": true,
9
    "table": "customers",
10
    "conditions": [
11
        {
12
            "field": "country",
13
            "operator": "equal to",
14
            "value": "USA"
15
        },
16
        {
17
            "field": "age",
18
            "operator": "greater than",
19
            "value": 18
20
        }
21
    ]
22
}

Sample Output

1
{
2
    "count": 1250
3
}

Delete rows
Copy

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "mydb",
5
    "schema": "public",
6
    "user": "dbuser",
7
    "password": "secretpassword",
8
    "ssl": true,
9
    "table": "customers",
10
    "conditions": [
11
        {
12
            "field": "status",
13
            "operator": "equal to",
14
            "value": "inactive"
15
        },
16
        {
17
            "field": "last_login",
18
            "operator": "less than",
19
            "value": "2023-01-01"
20
        }
21
    ]
22
}

Sample Output

1
{
2
    "deleted": true
3
}

Find rows
Copy

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "customers_db",
5
    "schema": "public",
6
    "user": "db_user",
7
    "password": "password123",
8
    "ssl": true,
9
    "table": "customers",
10
    "fields": [
11
        "id",
12
        "name",
13
        "email",
14
        "created_at"
15
    ],
16
    "conditions": [
17
        {
18
            "field": "created_at",
19
            "operator": "greater than",
20
            "value": "2023-01-01"
21
        },
22
        {
23
            "field": "name",
24
            "operator": "contains",
25
            "value": "Smith"
26
        }
27
    ],
28
    "limit": 10,
29
    "offset": 0
30
}

Sample Output

1
{
2
    "count": 3,
3
    "rows": [
4
        {
5
            "id": 1001,
6
            "name": "John Smith",
7
            "email": "john.smith@example.com",
8
            "created_at": "2023-03-15T10:30:00Z"
9
        },
10
        {
11
            "id": 1052,
12
            "name": "Sarah Smithson",
13
            "email": "sarah.smithson@example.com",
14
            "created_at": "2023-02-28T14:45:00Z"
15
        },
16
        {
17
            "id": 1078,
18
            "name": "Michael Smith-Johnson",
19
            "email": "michael.sj@example.com",
20
            "created_at": "2023-04-02T09:15:00Z"
21
        }
22
    ]
23
}

Insert new rows
Copy

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "mydb",
5
    "schema": "public",
6
    "user": "dbuser",
7
    "password": "securepassword",
8
    "ssl": true,
9
    "ssl_config": {},
10
    "table": "customers",
11
    "data": [
12
        {
13
            "id": 1,
14
            "name": "John Doe",
15
            "email": "john.doe@example.com",
16
            "age": 30
17
        },
18
        {
19
            "id": 2,
20
            "name": "Jane Smith",
21
            "email": "jane.smith@example.com",
22
            "age": 28
23
        }
24
    ]
25
}

Sample Output

1
{
2
    "count": 2,
3
    "inserted": true
4
}

Run SQL query
Copy

Execute the specified SQL code on the chosen database

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "mydb",
5
    "schema": "public",
6
    "user": "dbuser",
7
    "password": "securepassword",
8
    "ssl": true,
9
    "ssl_config": {},
10
    "sql": "SELECT * FROM public.users WHERE age > $1 AND city = $2",
11
    "sql_parameters": [
12
        30,
13
        "New York"
14
    ]
15
}

Sample Output

1
{
2
    "count": 3,
3
    "result": [
4
        {
5
            "id": 1,
6
            "name": "John Doe",
7
            "age": 35,
8
            "city": "New York"
9
        },
10
        {
11
            "id": 2,
12
            "name": "Jane Smith",
13
            "age": 42,
14
            "city": "New York"
15
        },
16
        {
17
            "id": 3,
18
            "name": "Mike Johnson",
19
            "age": 38,
20
            "city": "New York"
21
        }
22
    ]
23
}

Update rows
Copy

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "mydb",
5
    "schema": "public",
6
    "user": "dbuser",
7
    "password": "secretpassword",
8
    "ssl": true,
9
    "ssl_config": {},
10
    "table": "employees",
11
    "data": {
12
        "salary": 55000,
13
        "department": "Marketing"
14
    },
15
    "conditions": [
16
        {
17
            "field": "employee_id",
18
            "operator": "equal to",
19
            "value": 1001
20
        },
21
        {
22
            "field": "hire_date",
23
            "operator": "less than",
24
            "value": "2023-01-01"
25
        }
26
    ]
27
}

Sample Output

1
{
2
    "count": 1,
3
    "updated": true
4
}

DDL operations
Copy

List table fields (DDL)
Copy

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "mydb",
5
    "schema": "public",
6
    "user": "dbuser",
7
    "password": "securepassword123",
8
    "ssl": true,
9
    "ssl_config": {}
10
}

Sample Output

1
{}

List tables (DDL)
Copy

Note that DDL operations can only be called directly by Connectors API, or when using CustomJS in the Embedded solution editor for e.g. DDL-dependent data mapping

Sample Input

1
{
2
    "host": "db.example.com",
3
    "port": 5432,
4
    "database": "mydb",
5
    "schema": "public",
6
    "user": "dbuser",
7
    "password": "secretpassword",
8
    "ssl": true,
9
    "ssl_config": {}
10
}

Sample Output

1
{
2
    "tables": [
3
        {
4
            "table_name": "users",
5
            "ddl": "CREATE TABLE users (\n  id SERIAL PRIMARY KEY,\n  username VARCHAR(50) NOT NULL,\n  email VARCHAR(100) NOT NULL,\n  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);"
6
        },
7
        {
8
            "table_name": "products",
9
            "ddl": "CREATE TABLE products (\n  id SERIAL PRIMARY KEY,\n  name VARCHAR(100) NOT NULL,\n  description TEXT,\n  price DECIMAL(10, 2) NOT NULL,\n  stock INTEGER NOT NULL DEFAULT 0\n);"
10
        },
11
        {
12
            "table_name": "orders",
13
            "ddl": "CREATE TABLE orders (\n  id SERIAL PRIMARY KEY,\n  user_id INTEGER REFERENCES users(id),\n  total_amount DECIMAL(10, 2) NOT NULL,\n  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);"
14
        }
15
    ]
16
}
Previous

Overview

Next

Operations