Skip to content

GCS to PostgreSQL

Import data from Google Cloud Storage files directly into PostgreSQL tables.

Operator Usage

from airsql.transfers import GCSToPostgresOperator

import_data = GCSToPostgresOperator(
    task_id='gcs_to_pg_import',
    bucket_name='data-bucket',
    object_name='exports/users.jsonl',
    target_table_name='staging.import_table',
    postgres_conn_id='postgres_default',
    gcp_conn_id='google_cloud_default',
)

Parameters

Parameter Type Required Description
target_table_name str Yes PostgreSQL table (schema.table)
bucket_name str Yes GCS bucket name
object_name str Yes GCS object path
postgres_conn_id str Yes PostgreSQL connection ID
gcp_conn_id str No GCP connection ID
conflict_columns List[str] No Columns for upsert conflict resolution
replace bool No Replace table content (default: False)
create_if_missing bool No Create table if missing (default: False)
create_if_empty bool No Create empty table if source empty
grant_table_privileges bool No Grant ALL privileges to PUBLIC (default: True)
source_schema dict No Source column types for type coercion
postgres_type_overrides dict[str, str] No Per-column PostgreSQL type hints used when creating destination tables
batch_size int No Rows read and loaded per batch for append/replace imports (default: 50000)
dry_run bool No Simulate without writing

Examples

Basic Import

import_task = GCSToPostgresOperator(
    task_id='import_data',
    bucket_name='data-bucket',
    object_name='exports/users.csv',
    target_table_name='staging.users',
    postgres_conn_id='postgres_default',
)

JSONL Import

import_task = GCSToPostgresOperator(
    task_id='import_jsonl',
    bucket_name='data-bucket',
    object_name='exports/events.jsonl',
    target_table_name='staging.events',
    postgres_conn_id='postgres_default',
)

Parquet Import

import_task = GCSToPostgresOperator(
    task_id='import_parquet',
    bucket_name='data-bucket',
    object_name='exports/data.parquet',
    target_table_name='warehouse.data',
    postgres_conn_id='postgres_default',
)

With Upsert

Upserts use conflict_columns to identify existing rows. AirSQL stages incoming rows in a temporary PostgreSQL table, then runs one set-based INSERT ... SELECT ... ON CONFLICT statement against the target table.

import_task = GCSToPostgresOperator(
    task_id='upsert_data',
    bucket_name='data-bucket',
    object_name='exports/user_updates.parquet',
    target_table_name='public.users',
    postgres_conn_id='postgres_default',
    conflict_columns=['id'],
)

If the incoming file contains duplicate conflict keys, AirSQL keeps the last row before loading the staging table. This avoids PostgreSQL's ON CONFLICT DO UPDATE command cannot affect row a second time error while matching the previous practical last-write-wins behavior.

Replace Table

import_task = GCSToPostgresOperator(
    task_id='replace_table',
    bucket_name='data-bucket',
    object_name='exports/full_export.jsonl',
    target_table_name='warehouse.full_data',
    postgres_conn_id='postgres_default',
    replace=True,
)

Create Table If Missing

import_task = GCSToPostgresOperator(
    task_id='import_or_create',
    bucket_name='data-bucket',
    object_name='exports/new_table.csv',
    target_table_name='staging.new_table',
    postgres_conn_id='postgres_default',
    create_if_missing=True,
)

Create If Empty Source

import_task = GCSToPostgresOperator(
    task_id='import_empty',
    bucket_name='data-bucket',
    object_name='exports/might_be_empty.csv',
    target_table_name='staging.data',
    postgres_conn_id='postgres_default',
    create_if_empty=True,
)

With Source Schema

import_task = GCSToPostgresOperator(
    task_id='import_with_schema',
    bucket_name='data-bucket',
    object_name='exports/data.parquet',
    target_table_name='public.data',
    postgres_conn_id='postgres_default',
    source_schema={'id': 'INTEGER', 'amount': 'FLOAT', 'created_at': 'TIMESTAMP'},
)

With PostgreSQL Type Overrides

Use postgres_type_overrides to force specific column types when create_if_missing or create_if_empty needs to build the target table.

import_task = GCSToPostgresOperator(
    task_id='import_with_type_hints',
    bucket_name='data-bucket',
    object_name='exports/data.parquet',
    target_table_name='public.data',
    postgres_conn_id='postgres_default',
    create_if_missing=True,
    source_schema={
        'followers_count': 'INT64',
        'account_views_count': 'INT64',
    },
    postgres_type_overrides={
        'followers_count': 'BIGINT',
        'account_views_count': 'BIGINT',
    },
)

Dry Run

import_task = GCSToPostgresOperator(
    task_id='test_import',
    bucket_name='data-bucket',
    object_name='exports/large_file.parquet',
    target_table_name='staging.test',
    postgres_conn_id='postgres_default',
    dry_run=True,
)

How It Works

  1. Downloads file from GCS
  2. Detects format from file extension (csv, jsonl, parquet, avro)
  3. Reads data into pandas DataFrame
  4. Coerces column types to match PostgreSQL schema
  5. Handles JSON columns automatically
  6. Inserts, replaces, partition-exchanges, or upserts data into PostgreSQL

AirSQL uses typed row insertion for PostgreSQL writes instead of relying on pandas.to_sql for the critical load paths. This preserves nullable integers, booleans, floats, and PyArrow-backed scalar values when loading Parquet or other typed files into PostgreSQL columns.

Supported Formats

Format Extension Use Case
CSV .csv Simple tabular data
JSONL .jsonl Nested data, JSON columns
Parquet .parquet Columnar storage, analytics
Avro .avro Schema evolution support

Format is auto-detected from filename.

Upsert Mode

Use conflict columns for merge/upsert:

import_task = GCSToPostgresOperator(
    task_id='merge_updates',
    bucket_name='data-bucket',
    object_name='updates/products.csv',
    target_table_name='public.products',
    postgres_conn_id='postgres_default',
    conflict_columns=['sku'],
    # Only update these columns on conflict
)

Upsert mode is optimized for larger batches:

  1. Incoming rows are deduplicated by conflict_columns, keeping the last row.
  2. Rows are loaded into a temporary staging table using typed row insertion.
  3. AirSQL inserts from staging into the target table with ON CONFLICT.
  4. Existing rows are only updated when selected values are actually different.

For unchanged rows, AirSQL adds an IS DISTINCT FROM guard to avoid unnecessary physical updates. This reduces WAL, dead tuples, index churn, and autovacuum work on large PostgreSQL tables.

Generated SQL is equivalent to:

CREATE TEMP TABLE _upsert_target_xxx
(LIKE target INCLUDING DEFAULTS)
ON COMMIT DROP;

INSERT INTO target AS target (id, name, updated_at)
SELECT staging.id, staging.name, staging.updated_at
FROM _upsert_target_xxx AS staging
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at
WHERE (target.name, target.updated_at)
IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.updated_at);

Type Coercion

Automatically coerces types between BigQuery and PostgreSQL:

BigQuery PostgreSQL
INTEGER integer
FLOAT double precision
BOOLEAN boolean
STRING text
TIMESTAMP timestamptz
JSON jsonb

PyArrow and Nullable Types

When loading Parquet or Arrow-backed data, pandas may expose values as nullable extension dtypes or PyArrow scalar objects. AirSQL converts these values to native Python scalars before writing to PostgreSQL, so BIGINT, BOOLEAN, DOUBLE PRECISION, and nullable columns receive typed values rather than text.