copy-query-builder.md

Copy Query Builder

[DOC_LINK:/documentation/components/libs/postgresql.md]

[TOC]

The Copy Query Builder provides a fluent, type-safe interface for constructing PostgreSQL COPY statements. It supports both COPY TO (data export) and COPY FROM (data import) operations with files, programs, STDIN, and STDOUT.

COPY FROM (Data Import)

Basic COPY FROM

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->from('users')
    ->file('/tmp/users.csv');

echo $query->toSql();
// COPY users FROM '/tmp/users.csv'

COPY FROM with Columns

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->from('users')
    ->columns('id', 'name', 'email')
    ->file('/tmp/users.csv');

echo $query->toSql();
// COPY users(id, name, email) FROM '/tmp/users.csv'

COPY FROM STDIN

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$query = copy()
    ->from('users')
    ->stdin()
    ->format(CopyFormat::CSV);

echo $query->toSql();
// COPY users FROM STDIN WITH (format csv)

COPY FROM PROGRAM

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->from('logs')
    ->program('gunzip -c /var/log/app.log.gz');

echo $query->toSql();
// COPY logs FROM PROGRAM 'gunzip -c /var/log/app.log.gz'

CSV Format with Options

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$query = copy()
    ->from('data')
    ->file('/tmp/data.csv')
    ->format(CopyFormat::CSV)
    ->withHeader()
    ->delimiter(';')
    ->nullAs('NULL')
    ->quote("'")
    ->escape('\\')
    ->encoding('UTF8');

echo $query->toSql();
// COPY data FROM '/tmp/data.csv' WITH (format csv, delimiter ';', null 'NULL', header true, quote '''', escape '\\', encoding 'UTF8')

Force Not Null

Treat specified columns as non-nullable during import:

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$query = copy()
    ->from('users')
    ->file('/tmp/users.csv')
    ->format(CopyFormat::CSV)
    ->forceNotNull('name', 'email');

echo $query->toSql();
// COPY users FROM '/tmp/users.csv' WITH (format csv, force_not_null (name, email))

Force Null

Treat specified values as NULL for these columns:

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$query = copy()
    ->from('users')
    ->file('/tmp/users.csv')
    ->format(CopyFormat::CSV)
    ->forceNull('description', 'notes');

echo $query->toSql();
// COPY users FROM '/tmp/users.csv' WITH (format csv, force_null (description, notes))

Error Handling

Control behavior when encountering invalid data:

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyOnError;

$query = copy()
    ->from('events')
    ->file('/tmp/events.csv')
    ->onError(CopyOnError::IGNORE);

echo $query->toSql();
// COPY events FROM '/tmp/events.csv' WITH (on_error 'ignore')

COPY TO (Data Export)

Basic COPY TO

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->to('users')
    ->file('/tmp/users.csv');

echo $query->toSql();
// COPY users TO '/tmp/users.csv'

COPY TO with Columns

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->to('users')
    ->columns('id', 'name', 'email')
    ->file('/tmp/users.csv');

echo $query->toSql();
// COPY users(id, name, email) TO '/tmp/users.csv'

COPY TO STDOUT

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$query = copy()
    ->to('users')
    ->stdout()
    ->format(CopyFormat::CSV);

echo $query->toSql();
// COPY users TO STDOUT WITH (format csv)

COPY TO PROGRAM

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->to('logs')
    ->program('gzip > /tmp/logs.csv.gz');

echo $query->toSql();
// COPY logs TO PROGRAM 'gzip > /tmp/logs.csv.gz'

COPY with SELECT Query

Export results of a query instead of a table:

<?php

use function Flow\PostgreSql\DSL\{copy, select, col, table};
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$selectQuery = select()
    ->select(col('id'), col('name'))
    ->from(table('users'));

$query = copy()
    ->toQuery($selectQuery)
    ->file('/tmp/active_users.csv')
    ->format(CopyFormat::CSV);

echo $query->toSql();
// COPY (SELECT id, name FROM users) TO '/tmp/active_users.csv' WITH (format csv)

Binary Format

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

$query = copy()
    ->to('data')
    ->file('/tmp/data.bin')
    ->format(CopyFormat::BINARY);

echo $query->toSql();
// COPY data TO '/tmp/data.bin' WITH (format binary)

Force Quote

Quote specific columns or all columns in CSV output:

<?php

use function Flow\PostgreSql\DSL\copy;
use Flow\PostgreSql\QueryBuilder\Copy\CopyFormat;

// Quote specific columns
$query = copy()
    ->to('products')
    ->file('/tmp/products.csv')
    ->format(CopyFormat::CSV)
    ->forceQuote('name', 'description');

echo $query->toSql();
// COPY products TO '/tmp/products.csv' WITH (format csv, force_quote (name, description))

// Quote all columns
$query = copy()
    ->to('products')
    ->file('/tmp/products.csv')
    ->format(CopyFormat::CSV)
    ->forceQuoteAll();

echo $query->toSql();
// COPY products TO '/tmp/products.csv' WITH (format csv, force_quote *)

Schema-Qualified Tables

<?php

use function Flow\PostgreSql\DSL\copy;

$query = copy()
    ->from('analytics.events')
    ->file('/tmp/events.csv');

echo $query->toSql();
// COPY analytics.events FROM '/tmp/events.csv'

$query = copy()
    ->to('analytics.events')
    ->file('/tmp/events.csv');

echo $query->toSql();
// COPY analytics.events TO '/tmp/events.csv'

Copy Formats

The CopyFormat enum provides three format options:

Format	Description
CopyFormat::TEXT	Default PostgreSQL text format
CopyFormat::CSV	Comma-separated values format
CopyFormat::BINARY	PostgreSQL binary format

Error Handling Options

The CopyOnError enum (COPY FROM only) provides error handling options:

Option	Description
CopyOnError::STOP	Stop on first error (default)
CopyOnError::IGNORE	Skip rows with errors and continue

For a complete list of DSL functions, see the DSL reference.