Edit

#Copy Query Builder

• ⬅️ Back

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:

#Error Handling Options

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

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