← Every conversion

Convert a PostgreSQL schema to Google BigQuery

Paste the CREATE TABLE statements from PostgreSQL and ERD Designer draws them as a diagram. Change the diagram's target database to Google BigQuery and every column's datatype is converted. You review each change before it is applied, then generate the Google BigQuery script.

PostgreSQL DDL script

Source database

Diagram name (optional)

You sign up free and land on your diagram. Your script is held for 15 minutes and deleted from our server the moment you open it.

What changes in the sample

Converted to Google BigQuery, these columns of the sample above change type. Everything else keeps its type; names, keys, nullability and lists of allowed values are left as they are.

Column	PostgreSQL	Google BigQuery	Note
`customers.customer_id`	`BIGSERIAL`	`INT64`
`customers.email`	`VARCHAR(255)`	`STRING`
`customers.full_name`	`VARCHAR(120)`	`STRING`
`customers.notes`	`TEXT`	`STRING`
`customers.is_active`	`BOOLEAN`	`BOOL`
`customers.created_at`	`TIMESTAMP WITH TIME ZONE`	`TIMESTAMP`	default: now() → CURRENT_TIMESTAMP
`orders.order_id`	`SERIAL`	`INT64`
`orders.customer_id`	`BIGINT`	`INT64`
`orders.public_id`	`UUID`	`STRING`	default: gen_random_uuid() → GENERATE_UUID()
`orders.status`	`ENUM (order_status)`	`STRING`
`orders.placed_at`	`TIMESTAMP(3)`	`DATETIME`
`orders.receipt_pdf`	`BYTEA`	`BYTES`

What you are asked

Where Google BigQuery offers more than one fair answer, ERD Designer asks before it converts — once per kind of column, not once per column. For this page each question was answered with its first option:

orders.status — answered STRING

STRING — the list kept on the diagram as documentation — Google BigQuery cannot enforce it, so the DDL writes no constraint
Lookup table — a new table holding the values, keyed by the value itself, with a foreign key from this column

What it tells you

Where a conversion loses something, ERD Designer says so rather than picking for you. These are the messages for the sample:

orders.placed_at: Google BigQuery timestamps take no fractional-second precision — "(3)" was dropped.
BigQuery does not support UNIQUE constraints, so none was created for: customers (email).
BigQuery does not support indexes, so none was created for: orders (placed_at).

The Google BigQuery script

This is what ERD Designer generates for the sample, for Google BigQuery. Our test suite applies this conversion to a real Google BigQuery server and then reads the database back to confirm it matches the diagram exactly.

-- NOTE: BigQuery keys are NOT ENFORCED — the clause does not parse without
-- that word. They inform the query optimiser and document intent; nothing
-- prevents a duplicate key or an orphaned reference.

-- Create table customers
CREATE TABLE `customers` (
  `customer_id` INT64 NOT NULL,
  `email` STRING NOT NULL,
  `full_name` STRING NOT NULL,
  `notes` STRING,
  `is_active` BOOL DEFAULT TRUE NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  PRIMARY KEY (`customer_id`) NOT ENFORCED
);

-- Create table orders
CREATE TABLE `orders` (
  `order_id` INT64 NOT NULL,
  `customer_id` INT64 NOT NULL,
  `public_id` STRING DEFAULT generate_uuid() NOT NULL,
  `status` STRING DEFAULT 'new' NOT NULL,
  `total` NUMERIC(12,2) NOT NULL,
  `placed_at` DATETIME NOT NULL,
  `receipt_pdf` BYTES,
  PRIMARY KEY (`order_id`) NOT ENFORCED
);

-- Add foreign key orders (customer_id) → customers (customer_id)
ALTER TABLE `orders` ADD CONSTRAINT `fk_orders_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`) NOT ENFORCED;

How to convert your own schema

Paste your PostgreSQL script above and press Open in ERD Designer. The diagram opens laid out and ready to edit.
Click the diagram's name in the toolbar to open its properties, choose Google BigQuery under Target Database / Version, and press Apply Changes.
If Google BigQuery offers more than one way to store a kind of column, choose one and press Continue.
Read the list of retyped columns and any warnings, then press Apply Conversion. Save the diagram to keep it.
Press Generate DDL (the database icon) to copy or download the Google BigQuery script — or apply it to a database on your own network with the ERD Designer Agent.

What it reads, and what it does not

It reads CREATE TABLE, ALTER TABLE … ADD CONSTRAINT and CREATE INDEX: tables, columns, keys, foreign keys, unique constraints, check lists and indexes. Anything else in the script is skipped and listed.
It converts the schema, never the data: no rows are read, moved or changed.
It does not convert views, stored procedures, functions, triggers or packages.

Which plan you need

Pasting a script and working with it as a diagram is free on the Basic plan, with no card. Converting a diagram to another database is part of the Pro and Advanced plans, which also generate its script and can apply it through the Agent.

Other conversions

Questions? Write to support@erddesigner.com. · ERD Designer · Terms · Privacy