Как сохранить sql запрос в файл postgresql
Обновлено: 02.07.2024
Каков самый простой способ сохранить вывод PL/pgSQL из базы данных PostgreSQL в файл CSV?
Я использую PostgreSQL 8.4 с плагинами pgAdmin III и PSQL, из которых я запускаю запросы.
ОТВЕТЫ
Ответ 1
Вы хотите получить полученный файл на сервере или на клиенте?
Если вам нужно что-то простое для повторного использования или автоматизации, вы можете использовать Postgresql, встроенный в команду COPY. например.
Этот подход полностью работает на удаленном сервере - он не может писать на ваш локальный ПК. Он также должен запускаться как "суперпользователь Postgres" (обычно называемый "root" ), потому что Postgres не может остановить его, делая неприятные вещи с локальной файловой системой этой машины.
Это на самом деле не означает, что вы должны быть подключены как суперпользователь (автоматизация, которая будет представлять угрозу безопасности другого типа), потому что вы можете использовать параметр SECURITY DEFINER CREATE FUNCTION , чтобы создать функцию, которая работает так, как если бы вы были суперпользователем.
Важнейшая часть заключается в том, что ваша функция должна выполнять дополнительные проверки, а не просто обходить безопасность, чтобы вы могли написать функцию, которая экспортирует точные данные, которые вам нужны, или вы могли бы написать что-то, что может принимать различные варианты, такие как пока они встречают строгий белый список. Вам нужно проверить две вещи:
- Какие файлы должны быть разрешены пользователю для чтения/записи на диск? Например, это может быть конкретный каталог, и имя файла может иметь подходящий префикс или расширение.
- Какие таблицы должны ли пользователи читать или записывать в базе данных? Обычно это определяется GRANT в базе данных, но теперь функция работает как суперпользователь, поэтому таблицы, которые обычно будут "за пределами границ", будут полностью доступны. Вероятно, вы не хотите, чтобы кто-то вызывал вашу функцию и добавлял строки в конец вашей таблицы "users".
Другой подход - выполнить обработку файлов на стороне клиента, то есть в приложении или script. Сервер Postgres не должен знать, к какому файлу вы копируете, он просто выплевывает данные, а клиент ставит их где-то.
Подходящим синтаксисом для этого является команда COPY TO STDOUT , а графические инструменты, такие как pgAdmin, завершают ее для вас в приятном диалоговом окне.
Клиент командной строки psql имеет специальную "мета-команду" под названием \copy , которая принимает все те же опции, что и "реальный" COPY , но выполняется внутри клиента:
Обратите внимание, что завершение ; завершено, потому что мета-команды завершаются символом новой строки, в отличие от команд SQL.
Не путайте COPY с инструкцией psql\copy.\copy вызывает COPY FROM STDIN или COPY TO STDOUT, а затем извлекает/сохраняет данные в файле, доступном для клиента psql. Таким образом, доступ к файлам и права доступа зависят от клиента, а не от сервера, когда используется \copy.
В вашем языке программирования приложений также может поддерживаться толкание или выборка данных, но вы не можете обычно использовать COPY FROM STDIN / TO STDOUT в стандартном SQL-заявлении, потому что нет способа подключения потока ввода-вывода. Обработчик PHP PostgreSQL (не PDO) включает в себя очень простой pg_copy_from и pg_copy_to , которые копируются в/из массива PHP, что может быть неэффективным для больших наборов данных.
Ответ 2
Существует несколько решений:
psql -d dbname -t -A -F"," -c "select * from users" > output.csv
COPY (SELECT * from users) To '/tmp/output.csv' With CSV;
Ответ 3
В терминале (при подключении к db) установите вывод в файл cvs
1) Установите разделитель поля на ',' :
2) Установите выходной формат без знака:
3) Показывать только кортежи:
4) Установите выход:
5) Выполните свой запрос:
Затем вы сможете найти свой CSV файл в этом месте:
Скопируйте его с помощью команды scp или отредактируйте с помощью nano:
Ответ 4
Если вас интересуют все столбцы конкретной таблицы вместе с заголовками, вы можете использовать
Это немного проще, чем
которые, насколько мне известно, эквивалентны.
Ответ 5
Итак, я использовал:
и он функционирует
Ответ 6
Эта информация не очень хорошо представлена. Поскольку это второй раз, когда мне нужно было получить это, я помещу это здесь, чтобы напомнить себе, если ничего больше.
Действительно лучший способ сделать это (получить CSV из postgres) - это использовать команду COPY. TO STDOUT . Хотя вы не хотите делать это так, как показано в ответах здесь. Правильный способ использования команды:
Запомните только одну команду!
Отлично подходит для использования через ssh:
Это отлично подходит для использования внутри докера через SSH:
Это даже здорово на локальной машине:
Или внутри докера на локальной машине?
Так универсально, много запятых!
Ты когда-нибудь?
Да, вот мои заметки:
Использование /copy эффективно выполняет файловые операции в любой системе, в которой выполняется команда psql , как пользователь, выполняющий ее 1. Если вы подключаетесь к удаленному серверу, просто скопировать файлы данных в системе, выполнив psql на/с удаленного сервера.
COPY выполняет файловые операции на сервере, поскольку учетная запись пользователя внутреннего процесса ( postgres умолчанию), пути к файлам и разрешения проверяются и применяются соответствующим образом. При использовании TO STDOUT проверки прав доступа к файлам обходятся.
Обе эти опции требуют последующего перемещения файла, если psql не выполняется в системе, где вы хотите, чтобы результирующий CSV в конечном итоге находился. По моему опыту, это наиболее вероятный случай, когда вы в основном работаете с удаленными серверами.
Более сложно настроить что-то вроде туннеля TCP/IP через ssh на удаленную систему для простого вывода CSV, но для других форматов вывода (двоичного) может быть лучше /copy через туннельное соединение, выполняя локальный psql . Аналогичным образом, при большом импорте перемещение исходного файла на сервер и использование COPY , вероятно, является наиболее эффективным вариантом.
Параметры PSQL
С параметрами psql вы можете отформатировать вывод как CSV, но есть и недостатки, такие как необходимость помнить, чтобы отключить пейджер и не получать заголовки:
Другие инструменты
Нет, я просто хочу вывести CSV из моего сервера без компиляции и/или установки инструмента.
Ответ 7
psql может сделать это для вас:
См. man psql для справки о параметрах, используемых здесь.
Ответ 8
Ответ 9
Я работаю над AWS Redshift, который не поддерживает функцию COPY TO .
Мой инструмент BI поддерживает CSV с разделителями табуляции, поэтому я использовал следующее:
Ответ 10
Новая версия - PSQL 12 - будет поддерживать --csv .
PSQL - devel
--csv
Переключение в режим вывода CSV (значения, разделенные запятыми). Это эквивалентно формату\pset csv.
csv_fieldsep
Определяет разделитель полей, который будет использоваться в выходном формате CSV. Если символ-разделитель появляется в значении поля, это поле выводится в двойных кавычках, следуя стандартным правилам CSV. По умолчанию используется запятая.
Ответ 11
Я написал небольшой инструмент под названием psql2csv который инкапсулирует шаблон COPY query TO STDOUT , что приводит к правильному CSV. Это интерфейс похож на psql .
Предполагается, что запрос представляет собой содержимое STDIN, если оно есть, или последний аргумент. Все остальные аргументы перенаправляются в psql, кроме следующих:
Ответ 12
Если у вас есть более длинный запрос, и вы хотите использовать psql, поместите свой запрос в файл и используйте следующую команду:
Ответ 13
Я попробовал несколько вещей, но немногие из них смогли дать мне желаемый CSV с подробностями заголовка.
Вот что сработало для меня.
Ответ 14
Чтобы загрузить CSV файл с именами столбцов в качестве HEADER, используйте следующую команду:
Ответ 15
JackDB, клиент базы данных в вашем веб-браузере, делает это очень простым. Особенно, если вы на Хереку.
Он позволяет вам подключаться к удаленным базам данных и запускать на них SQL-запросы.
Как только ваша БД подключена, вы можете запустить запрос и экспортировать его в CSV или TXT (см. внизу справа).
Примечание: Я никоим образом не связан с JackDB. В настоящее время я использую их бесплатные услуги и считаю это отличным продуктом.
Ответ 16
Я очень рекомендую DataGrip, IDE для базы данных JetBrains. Вы можете экспортировать SQL-запрос в CSV файл и с легкостью настроить ssh-туннелирование. Когда документация ссылается на "набор результатов", они означают результат, возвращаемый SQL-запросом в консоли.
Я использую psql \dt для отображения всех таблиц в базе данных, и мне нужно сохранить результаты.
Каков синтаксис для экспорта результатов команды psql в файл?
Из справки psql ( \? ):
\ o [ФАЙЛ] отправлять все результаты запроса в файл или | pipeу
Последовательность команд будет выглядеть следующим образом:
Команда psql \o уже была описана jhwist.
Альтернативный подход заключается в использовании команды COPY TO для прямой записи в файл на сервере. Преимущество этого заключается в том, что он сбрасывался в удобном для восприятия формате по вашему выбору, а не в формате psql. Также очень легко импортировать в другую таблицу/базу данных с помощью COPY FROM .
NB! Это требует привилегий суперпользователя и будет записываться в файл на сервере.
Пример: COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';')
\copy , которая является командой postgres, может работать для любого пользователя. Не знаю, работает ли он для \dt или нет, но общий синтаксис воспроизводится по следующей ссылке Синтаксис копирования SQL Postgres
Приведенный выше текст сохранит выходные данные запроса выбора в файле с именем, предоставленным в виде csv файла.
Для моего сервера psql работает следующая команда, это более старая версия v8.5
COPY tablename TO '/tmp/output.csv' DELIMITER ',' CSV HEADER;
эта команда используется для хранения всей таблицы как csv
Если вы получили следующую ошибку
ufgtoolspg=> COPY (SELECT foo, bar FROM baz) TO '/tmp/query.csv' (format csv, delimiter ';')
;
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql \copy command also works for anyone.
Вы можете запустить его таким образом:
psql somepsqllink_or_credentials -c "COPY (SELECT foo, bar FROM baz) TO STDOUT (format csv, delimiter ';')" > baz.csv
Я предполагаю, что для этого существует некоторая внутренняя команда psql, но вы также можете запустить команду script из util-linux-ng package:
ОПИСАНИЕ Script делает typescript всего, что было напечатано на вашем терминале.
Приветствую всех.
При автоматизации небольших магазинов для хранения данных часто используют PostgreSQL. И часто возникает потребность экспортировать эти данные в Excel. В этой статье я расскажу вам как я решал эту задачу. Естественно, матерые специалисты вряд ли откроют для себя что-то новое. Однако, материал будет интересен тем кто «плавает» в этой теме.
Итак, естественно, самый просто и банальный способ экспортировать данные результатов запросов в csv-файлы, а затем открыть их в Excel. Это выглядит вот так:
- во-первых, вставка данных из PostgreSQL происходит именно на сервере;
- во-вторых, можно конечно заморочиться написать batch-скрипт, который будет удаленно вызывать этот запрос на сервере, затем этот файл скопировать на компьютер пользователя и инициировать открытие в Excel.
1. Идем по ссылке и в зависимости от разрядности компьютера скачиваем установщик ODBC драйвера. Установка его проста и не требует особых знаний.
2. Чтобы пользователи могли со своих компьютеров цепляться к БД не забудьте в файле pg_hba.conf установить параметры для IP-адресов, с которых можно производить подключения:
В данном примере, что все рабочие станции смогут подключаться к серверу с БД:
3. Далее через Excel просто генерируем файл динамического запроса к данным *.dqy. Далее этот файл просто можно менять по своему усмотрению. Можно прям ниже следующий текст взять, скопировать в блокнот и там отредактировать, сохранив файл *.dqy. Вводим имя файла и расширение dqy. Выбираем типа файла ВСЕ(All files):
DATABASE – указывается наименование БД к которой будет производиться подключение;
SERVER – адрес сервера;
PASSWORD – пароль на подключение к БД.
Обратите внимание, что в большом тексте указываются параметры подключения к БД и ваша БД. Также можно еще сконфигурировать множество параметров подключения
В последней строке пишется сам запрос. Далее сохраняем файл. Если на компьютере установлен Microsoft Excel, тогда файл сразу же приобретет пиктограмму:
При запуске файла будет выдано диалоговое окно. Смело нажимаем «Включить»:
И получаем результат запроса из БД:
Теперь можно создать несколько таких файлов и спокойно скопировать их на рабочий стол пользователя:
Кстати, я пошел немного дальше. Откопал старый добрый VB6. Можно так сделать с любым языком программирования. Сделал форму, которая по выбранной дате запрашивает данные из БД, путем генерации этого *.dqy файла:
Затем немного покодил (вот часть кода):
Результат получился тот же — данные из Excel, и пользователю удобно. Да, кстати, в строке:
если речь идет о 64-битном процессоре и драйвере ODBC, установленном для 64 бит, то надо писать:
Ну, и самое главное, несмотря на всю простоту способа, у него есть конечно недостатки: запрос можно писать только в одну строку, т.е. записать строку вот в таком виде не получиться. Нужно только в одну:
— Не сможет обрабатывать на изменение данных типа:
или
Ну и может выводить только результат запроса в виде списка, т.е. красивый документ сделать не получиться. На этом все. Надеюсь данный способ кому-нибудь пригодиться. Буду рад получить ваши рекомендации по усовершенствованию моего метода или альтернативного решения данной проблемы.
В этом руководстве ты научишься использовать хранимые процедуры, чтобы дать имя твоим SELECT запросам и сделать их переиспользуемыми.
Когда ты работаешь с любой SQL базой данных, ты можешь заметить, что одни и те же SELECT запросы используются очень часто. Они бывают короткими, тогда их несложно писать с нуля.
Но бывает так, что в одном запросе есть несколько джойнов, агрегаций, сортировки, группировки и ты не хочешь потерять этот запрос, после отключения от консоли.
SELECT запросы очень удобно сохранять как хранимые процедуры в PostgreSQL. Ты можешь дать им любое имя и вызывать когда тебе нужно.
Сохраняем простой SELECT запрос как хранимую процедуру
Для примера, представим, что у нас есть таблица app_user , которая хранит всех пользователей нашего приложения.
Если тебе нужно получить количество пользователей твоего приложения, ты напишешь что-то вроде:
Чтобы дать имя этому SQL запросу, ты можешь сохранить его как stored procedure.
Давай разберем пример по шагам:
- В первой строке мы создаем функцию и называем ее count_app_users .
- Команда create or replace используется для того, чтобы создать функцию, если она не существует, или обновить ее, если она уже есть. Если ты уверен в том, что функции еще нет, можешь использовать обычный create
- Дальше мы явно указываем тип результата. Она равен table для всех SELECT запросов.
- В скобках мы указываем столбцы итоговой таблицы и их тип.
- Команду language plpgsql и несколько следующих строк ты можешь рассматривать как шаблон и не слишком вникать в них
- Сам запрос мы помещаем внутрь выражения return query(. ) которое обернуто в $$ begin и end $$
Вызываем хранимую процедуру
После того, как мы сохранили SELECT запрос, мы можем вызвать его.
Такой синтаксис получается потому, что процедура возвращает таблицу. Чтобы получить все ее строки мы выполняем обычный SELECT *
Передаем аргументы в хранимую процедуру PostgreSQL
Дальше, представим, что мы хотим посчитать только пользователей созданных после определенной даты.
Чтобы сделать это, нам нужно передать параметра внутрь хранимой процедуры и использовать его внутри SELECT запроса. В сигнатуру процедуры, мы добавим имя параметров и их типы. Потом, внутри SELECT, мы обратимся к ним.
Теперь мы можем передать дату в процедуру count_app_users и получить количество всех пользователей созданных после этой даты.
Удаление хранимой процедуры PostgreSQL
Если ты попробуешь вызывать функцию count_app_users без аргументов, она будет до сих пор работать. Это получается потому, что функция определяется не только именем, но и типом и количеством параметров.
И у нас получилось две функции. Первая — count_app_users , вторая — count_app_users(after) . Обе они отлично живут в SQL базе данных.
Если одна из них тебе не нужна, то ее можно удалить так:
Синтаксис похож на удаление таблицы и в нем нет каких-то подводных камней.
Посмотреть текст хранимой процедуры PostgreSQL
Наш пример с count намеренно максимально упрощен, но если у тебя более длинная функция, то бывает полезно получить ее текст. Например, чтобы понять, как она работает или чтобы дальше его изменить.
Чтобы получить текст хранимой процедуры в PostgreSQL, тебе нужно знать ее имя:
Такой запрос вернет нам все, что мы писали между begin и end при создании.
Разбираемся с частыми ошибками
Первая ошибка с которой ты можешь столкнуться при сохранении SELECT запроса выглядит примерно так:
Она возникает если ты не оберешь SELECT запрос в выражение return query(. ) .
Дальше, может быть ошибка связанная с неправильным вызовом хранимой процедуры в PostgreSQL:
Скорее всего ты попробовал вызывать функцию так:
Так как наша функция возвращала таблицу, то и результат из этой таблицы нужно получать как из любых других. С помощью SELECT .
Заключение
Сохранять SELECT запросы в хранимые процедуры PostgreSQL очень удобно. Потом их можно вызывать по имени и не нужно будет каждый раз писать запрос с нуля.
Мы разобрали очень простой пример, но ты можешь сохранять SELECT запросы любой сложности.
Используй джойны, агрегацию, фильтр, сортировку и больше не пиши одни и те же запросы раз за разом.
Читайте также: