Pg copy to: примеры (PHP)

Экспорт данных PostgreSQL с pg_copy_to
Раздел: Базы данных (PostgreSQL)
pg_copy_to(PgSql\Connection $connection, string $table_name, string $separator = "\t", string $null_as = "\\\\N"): array|false
типовые примерыpg copy to на js, python, mysqlpg copy to изменения phpпримеры расширенные
Описание функции pg_copy_to

Функция pg_copy_to копирует данные из таблицы PostgreSQL в массив или, при указании файла, напрямую в него. Она является высокопроизводительным способом экспорта большого объема данных, так как использует команду COPY TO STDOUT сервера PostgreSQL.

Когда используется: когда требуется быстро получить все строки таблицы или результат запроса в виде массива или сохранить их в файл. Часто применяется для создания дампов, переноса данных или интеграции с другими системами.

Аргументы функции:

  • $connection (PgSql\Connection) – обязательный. Ресурс соединения с базой данных PostgreSQL.
  • $table_name (string) – обязательный. Имя таблицы или выражение (например, (SELECT * FROM users)), из которого копируются данные.
  • $separator (string) – необязательный, по умолчанию "\t" (табуляция). Символ-разделитель полей в строке.
  • $null_as (string) – необязательный, по умолчанию "\\\\N". Как представляются значения NULL в выходных данных.

Функция возвращает массив строк, где каждая строка соответствует строке таблицы, или false в случае ошибки.

Простые примеры использования

Базовый пример копирования таблицы в массив с разделителем-табуляцией.

<?php
$conn = pg_connect("host=localhost dbname=test user=postgres");
$result = pg_copy_to($conn, 'products');
print_r($result);
?>
Array
(
    [0] => 1\tХлеб\t50.50
    [1] => 2\tМолоко\t80.00
)

Копирование с указанием пользовательского разделителя и представления NULL.

<?php
$result = pg_copy_to($conn, 'orders', ',', 'NULL');
print_r($result);
?>
Array
(
    [0] => 1,2023-10-01,1500.00
    [1] => 2,2023-10-02,NULL,3
)
Похожие функции в PHP
  • pg_query + pg_fetch_all: Стандартный способ выборки данных. Предпочтительнее, когда нужен более гибкий контроль над форматом данных (ассоциативные массивы, объекты) или необходимо выполнить сложный запрос с соединениями.
  • pg_fetch_all_columns: Извлекает все значения одного столбца результата в массив. Удобно для получения плоского списка значений.
  • pg_select: Выполняет SELECT запрос по условию и возвращает данные в виде ассоциативного массива. Используется для выборки с условиями WHERE.
  • Выбор: pg_copy_to – для максимальной скорости экспорта всей таблицы или результата простого запроса. pg_query – для большинства других случаев, требующих гибкости.
Аналоги в других языках и СУБД
Python (psycopg2)
import psycopg2
conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
with open('output.csv', 'w') as f:
    cur.copy_expert("COPY (SELECT * FROM products) TO STDOUT WITH CSV", f)
cur.close()

Метод copy_expert или copy_to предоставляет схожую функциональность, но часто требует явной работы с курсором и файловым объектом.

JavaScript (Node.js, node-postgres)
const { Client } = require('pg');
const client = new Client();
await client.connect();
const stream = client.query(new Query("COPY products TO STDOUT"));
stream.pipe(fs.createWriteStream('output.csv')); // Потоковая запись в файл

Библиотека node-postgres использует потоковый режим (pg.CopyStream) для работы с COPY, что эффективно для больших данных.

Pg copy to в MySQL

В MySQL прямой аналог отсутствует. Для экспорта используется команда SELECT ... INTO OUTFILE на стороне сервера или клиентские утилиты типа mysqldump.

SELECT * FROM products INTO OUTFILE '/tmp/products.csv' FIELDS TERMINATED BY ',';
Распространенные ошибки

Ошибка при отсутствии соединения или его неверном типе.

<?php
$result = pg_copy_to(null, 'products');
var_dump($result); // bool(false)
echo pg_last_error(); // Warning: pg_copy_to(): supplied resource is not a valid PostgreSQL link resource
?>

Попытка скопировать данные из несуществующей таблицы.

<?php
$result = pg_copy_to($conn, 'non_existent_table');
if ($result === false) {
    echo pg_last_error($conn); // ERROR: relation "non_existent_table" does not exist
}
?>

Использование разделителя, который может встретиться в данных, без экранирования.

<?php
// Если в поле "description" есть запятая, это исказит структуру CSV.
$result = pg_copy_to($conn, 'products', ',');
?>
Изменения в версиях PHP
  • В PHP 8.0 тип возвращаемого значения ресурса (resource) был изменен на объект PgSql\Connection. Сама функция pg_copy_to продолжает работать с обоими типами для обратной совместимости.
  • В PHP 8.1 были ужесточены типы аргументов, что может приводить к предупреждениям типа TypeError при передаче неверных значений.
  • Значимых изменений в поведении или аргументах функции в последних версиях PHP (8.2, 8.3) не было.
Расширенные примеры

Копирование результата сложного запроса. Имя таблицы может быть заменено подзапросом в скобках.

Пример php
<?php
$query = "(SELECT id, name FROM products WHERE price > 70 ORDER BY name)";
$result = pg_copy_to($conn, $query, '|');
print_r($result);
?>

Экспорт данных напрямую в файл с помощью output buffer.

Пример php
<?php
ob_start();
$stream = fopen('php://output', 'w');
$resultArray = pg_copy_to($conn, 'products', ',');
if ($resultArray) {
    fwrite($stream, implode(PHP_EOL, $resultArray));
}
fclose($stream);
$csvContent = ob_get_clean();
file_put_contents('export.csv', $csvContent);
?>

Обработка больших объемов данных по частям. Функция возвращает весь массив сразу, что может потребовать много памяти.

Пример php
<?php
// Альтернатива для больших таблиц - использовать pg_copy_to с LIMIT в подзапросе
// или использовать pg_query с циклом выборки.
$offset = 0;
$limit = 10000;
$allData = [];
do {
    $query = "(SELECT * FROM large_table LIMIT $limit OFFSET $offset)";
    $chunk = pg_copy_to($conn, $query);
    $allData = array_merge($allData, $chunk);
    $offset += $limit;
} while (!empty($chunk));
// $allData теперь содержит все данные, но загружались частями
?>

Генерация строки в формате CSV с заголовками.

Пример php
<?php
$data = pg_copy_to($conn, 'products', ',', '""');
// Получаем имена столбцов
$res = pg_query($conn, "SELECT * FROM products LIMIT 0");
$numFields = pg_num_fields($res);
$headers = [];
for ($i = 0; $i < $numFields; $i++) {
    $headers[] = pg_field_name($res, $i);
}
$csv = implode(',', $headers) . PHP_EOL . implode(PHP_EOL, $data);
echo $csv;
?>

PHP pg_copy_to function comments

En
Pg copy to Copy a table to an array