Экспорт результатов PL/pgSQL запроса из PostgreSQL в CSV

Быстрый ответ

Если вы хотите сохранить результат выполнения функции PL/pgSQL в CSV-файл с использованием COPY TO, воспользуйтесь этой командой:

COPY (SELECT * FROM your_plpgsql_function()) TO '/path/to/output.csv' CSV HEADER;

В случае использования терминала с psql, примените следующую команду:

\copy (SELECT * FROM your_plpgsql_function()) TO '/path/to/output.csv' CSV HEADER

Замените your_plpgsql_function() на имя своей функции. Также убедитесь, что у пользователя сервиса PostgreSQL есть права на запись в /path/to/. CSV-файл будет автоматически снабжен заголовком.

Генерация CSV "на лету" через psql

В терминале psql доступны удобные однострочные команды, позволяющие быстро получить данные на выходе в формате CSV:

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

Если вам нужен более чистый результат без заголовка, используйте эту команду

psql -P pager=off -d mydb -t -A -F',' -c 'select * from table;' > table.csv

Такие команды позволяют мгновенно экспортировать результаты ваших запросов для быстрого анализа данных.

Безопасность: суперпользовательское обязательство и права на доступ

Обратите внимание, что непосредственное копирование с помощью COPY TO требует наличия прав суперпользователя. Рекомендуется обеспечить безопасность таких операций, обернув команду в SECURITY DEFINER, как показано ниже:

CREATE FUNCTION export_users_csv() RETURNS void LANGUAGE plpgsql SECURITY DEFINER AS $$
BEGIN
  COPY (SELECT * FROM users) TO '/safe/path/users.csv' WITH CSV HEADER;
  -- "Права root? Никогда о них не слышал." – Будет сказать эта функция.
END;
$$;

Этот подход обеспечивает правильное применение разрешений. Не забудьте заранее установить как следует права доступа к файлам и данным.

Подробнее об этом расскажет наш спикер на видео

Использование графического интерфейса: преимущества pgAdmin

Если вы предпочитаете работать с использованием графического интерфейса, pgAdmin станет для вас отличным инструментом. Для экспорта данных выполните следующие действия:

• Найдите нужную таблицу
• Кликните правой кнопкой мыши, выберите пункт Export
• В новом окне выберите формат CSV и определите требуемые настройки

pgAdmin – это удобный графический интерфейс, который упрощает процесс трансформации данных.

Визуализация

Представьте таблицу в PostgreSQL как золотое рудник 🏦, где хранятся интересующие вас данные 💎:

        🏦 PostgreSQL Table
             /        \
            /          \
          🛠️            💼 CSV file

Инструмент 🛠️, который вы используете, помогает извлечь данные и преобразовать их в формат CSV:

COPY (SELECT * FROM goldmine) TO '/path/to/csv/goldmine.csv' CSV HEADER;

Данные успешно извлечены – пользуйтесь ими по своему усмотрению! 🏦💎

Гибкость: вы управляете всем, неважно, являетесь ли вы суперпользователем

Если вы работаете в окружении, где контролируются суперпользовательские права, вам подойдёт команда \copy. Она обходит требования, связанные с ролью суперпользователя, и обеспечивает удобное сочетание простоты и функциональности:

psql -c "\copy (SELECT * FROM table) TO '/path/to/file.csv' WITH CSV"

В PHP для экспорта данных можно воспользоваться функциями pg_copy_from и pg_copy_to:

pg_copy_to($connection, 'tablename', '/path/to/file.csv');
// Готово, теперь вы знаете, как сделать "копипаст" на PHP 🍝.

При выполнении масштабных экспортных операций или дистанционных действий уместно использовать COPY совместно с Docker или Kubernetes. Это обеспечит масштабируемость и надёжное поддержание соединения.

Удалённое выполнение: знакомство с командами PostgreSQL через SSH

Вы можете не только самостоятельно подключаться к своему серверу, но и выполнять удалённые команды через SSH:

ssh postgres@host 'psql -c "QUERY" --csv' > output.csv

Можно также использовать последовательность интерактивных команд psql для генерации CSV-вывода:

psql -d dbname -c "\f ',' \a \o '/tmp/output.csv' SELECT * FROM table;"

Специальный экспорт: ваш способ в любое время, в любом месте

Возможно, вам нужно задать свой разделитель или обеспечить неотложную выдачу вывода. Не проблема – вы можете уточнить свои предпочтения с помощью флага --csv в psql, как показано ниже:

psql -c "COPY (SELECT * FROM table) TO STDOUT WITH CSV DELIMITER '|'" > output.csv

Для немедленного вывода достаточно использовать флаг --csv:

psql -c "QUERY" --csv > output.csv

Полезные материалы

1. PostgreSQL: Documentation: 16: COPY — официальная документация PostgreSQL на команду COPY.
2. Copy | Postgres Guide — практическое руководство по использованию COPY для сохранения вывода в CSV-файл.
3. How to export table as CSV with headings on PostgreSQL? – Stack Overflow — обсуждение в форуме экспорта результатов запросов в CSV, включая заголовки.
4. Import/Export Data Dialog — pgAdmin 4 8.2 documentation — руководство по pgAdmin о том, как экспортировать данные в формате CSV.
5. Useful PostgreSQL Queries and Commands · GitHub — пример функции PL/pgSQL для экспорта данных в CSV на GitHub.
6. Timescale Blog — урок по использованию psql для экспорта данных из PostgreSQL в CSV-файл.