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¶
- Downloads file from GCS
- Detects format from file extension (csv, jsonl, parquet, avro)
- Reads data into pandas DataFrame
- Coerces column types to match PostgreSQL schema
- Handles JSON columns automatically
- 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:
- Incoming rows are deduplicated by
conflict_columns, keeping the last row. - Rows are loaded into a temporary staging table using typed row insertion.
- AirSQL inserts from staging into the target table with
ON CONFLICT. - 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.