# Load job postings data into PostgreSQL

> Upsert job posts into a PostgreSQL table with the raw JSON in a jsonb column - queryable with SQL and safe to re-run.

Page: https://jobdataapi.com/integrations/postgresql/
Category: Databases and warehouses
You need: Python 3.9+, pip install requests "psycopg[binary]" and a jobdata API key (https://jobdataapi.com/accounts/pricing/).

## Steps

1. Copy the fetch helper below and set your API key.
2. Point the connection string at your database; the script creates the table on first run.
3. Each job is upserted by its id, so re-runs update rows instead of duplicating them.
4. Schedule the script daily (cron, a scheduler or your orchestrator).

## Code

```python
import requests

API_KEY = "YOUR_API_KEY"


def fetch_jobs(**params):
    """Yield every job for the given filters, following pagination."""
    url = "https://jobdataapi.com/api/jobs/"
    params = {"page_size": 1000, **params}
    headers = {"Authorization": f"Api-Key {API_KEY}"}
    while url:
        resp = requests.get(url, headers=headers, params=params, timeout=60)
        resp.raise_for_status()
        data = resp.json()
        yield from data["results"]
        url, params = data["next"], None  # "next" already carries the filters


import psycopg
from psycopg.types.json import Jsonb

UPSERT = """
    INSERT INTO jobs (id, title, company, location, published, application_url, data)
    VALUES (%s, %s, %s, %s, %s, %s, %s)
    ON CONFLICT (id) DO UPDATE SET
        title = EXCLUDED.title,
        location = EXCLUDED.location,
        application_url = EXCLUDED.application_url,
        data = EXCLUDED.data
"""

with psycopg.connect("postgresql://user:password@localhost/jobs") as conn:
    conn.execute("""
        CREATE TABLE IF NOT EXISTS jobs (
            id bigint PRIMARY KEY,
            title text,
            company text,
            location text,
            published timestamptz,
            application_url text,
            data jsonb
        )
    """)
    with conn.cursor() as cur:
        for job in fetch_jobs(max_age=1):
            cur.execute(UPSERT, (
                job["id"], job["title"], job["company"]["name"], job["location"],
                job["published"], job["application_url"], Jsonb(job),
            ))
```

## Notes

- Run it daily with max_age=1 to pick up new job posts; on access lite, drop max_age - the feed covers the past 90 days and upserting by id keeps it free of duplicates.
- Query nested fields straight from the JSON, e.g. data->'company'->>'website_url', or add columns for the fields you filter on most.
- Remove closed listings with the expired jobs endpoint (access pro) or the expired field, so your copy never shows stale posts.

## Related docs

- [Jobs API Ingestion Guide: Reliable Historical Backfills and Incremental Syncs](https://jobdataapi.com/c/jobs-api-ingestion-guide-reliable-historical-backfills-and-incremental-syncs/)
- [Date, ID, and Age Slicing Parameters Documentation](https://jobdataapi.com/c/date-id-and-age-slicing-parameters-documentation/)
- [Jobs Expired API Endpoint Documentation](https://jobdataapi.com/c/jobs-expired-api-endpoint-documentation/)