Экспорт определенных строк из таблицы PostgreSQL в виде сценария INSERT SQL
У меня есть схема базы данных с именем: nyummy и стол с именем cimory:
create table nyummy.cimory (
id numeric(10,0) not null,
name character varying(60) not null,
city character varying(50) not null,
CONSTRAINT cimory_pkey PRIMARY KEY (id)
);
Я хочу экспортировать cimory данные таблицы в виде файла сценария вставки SQL. Тем не менее, я хочу экспортировать записи / данные только в том случае, если город равен "Токио" (предположим, что все данные города строчные).
Как это сделать?
Не имеет значения, находится ли решение в бесплатных инструментах с графическим интерфейсом или в командной строке (хотя решение для инструментов с графическим интерфейсом лучше). Я пробовал pgAdmin III, но я не могу найти вариант сделать это.
13 ответов
Создайте таблицу с набором, который вы хотите экспортировать, а затем используйте утилиту командной строки pg_dump для экспорта в файл:
create table export_table as
select id, name, city
from nyummy.cimory
where city = 'tokio'
$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql
--column-inserts сбросит как команды вставки с именами столбцов.
--data-only не сбрасывать схему
Как указано ниже, создание представления вместо таблицы будет исключать создание таблицы всякий раз, когда необходим новый экспорт.
Для экспорта только для данных COPY,
Вы получаете файл с одной строкой таблицы на строку в виде простого текста (не INSERT команд), он меньше и быстрее:
COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv';
Импортируйте то же самое в другую таблицу с той же структурой где угодно с:
COPY other_tbl FROM '/path/to/file.csv';
COPY пишет и читает файлы локально на сервере, в отличие от клиентских программ, таких как pg_dump или же psql которые читают и пишут файлы локально для клиента. Если оба работают на одном компьютере, это не имеет большого значения, но это важно для удаленных подключений.
Также есть \copy команда psql, которая:
Выполняет внешнюю (клиентскую) копию. Это операция, которая запускает SQL
COPYкоманда, но вместо того, чтобы сервер считывал или записывал указанный файл, psql считывает или записывает файл и направляет данные между сервером и локальной файловой системой. Это означает, что доступность файлов и привилегии принадлежат локальному пользователю, а не серверу, и никаких привилегий суперпользователя SQL не требуется.
Это простой и быстрый способ экспортировать таблицу в скрипт с помощью pgAdmin вручную без дополнительных установок:
- Щелкните правой кнопкой мыши на целевой таблице и выберите "Резервное копирование".
- Выберите путь к файлу для хранения резервной копии. В качестве формата выберите "Обычный".
- Откройте вкладку "Параметры дампа #2" внизу и установите флажок "Использовать вставки столбцов".
- Нажмите кнопку резервного копирования.
- Если вы откроете полученный файл с помощью программы чтения текста (например, notepad++), вы получите скрипт для создания всей таблицы. Оттуда вы можете просто скопировать сгенерированные INSERT-заявления.
Этот метод также работает с техникой создания export_table, как показано в ответе @Clodoaldo Neto.
Для моего варианта использования я смог просто передать данные в grep.
pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql
SQL Workbench имеет такую особенность.
После выполнения запроса щелкните правой кнопкой мыши результаты запроса и выберите "Копировать данные как SQL > Вставка SQL"
Я пытался написать процедуру, основанную на кодах @PhilHibbs, по-другому. Пожалуйста, посмотрите и проверьте.
CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)
RETURNS setof text AS
$BODY$
DECLARE
dumpquery_0 text;
dumpquery_1 text;
selquery text;
selvalue text;
valrec record;
colrec record;
BEGIN
-- ------ --
-- GLOBAL --
-- build base INSERT
-- build SELECT array[ ... ]
dumpquery_0 := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table) || '(';
selquery := 'SELECT array[';
<<label0>>
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table and table_schema = p_schema
ORDER BY ordinal_position
LOOP
dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
selquery := selquery || 'CAST(' || quote_ident(colrec.column_name) || ' AS TEXT),';
END LOOP label0;
dumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')';
dumpquery_0 := dumpquery_0 || ' VALUES (';
selquery := substring(selquery ,1,length(selquery)-1) || '] AS MYARRAY';
selquery := selquery || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table);
selquery := selquery || ' WHERE '||p_where;
-- GLOBAL --
-- ------ --
-- ----------- --
-- SELECT LOOP --
-- execute SELECT built and loop on each row
<<label1>>
FOR valrec IN EXECUTE selquery
LOOP
dumpquery_1 := '';
IF not found THEN
EXIT ;
END IF;
-- ----------- --
-- LOOP ARRAY (EACH FIELDS) --
<<label2>>
FOREACH selvalue in ARRAY valrec.MYARRAY
LOOP
IF selvalue IS NULL
THEN selvalue := 'NULL';
ELSE selvalue := quote_literal(selvalue);
END IF;
dumpquery_1 := dumpquery_1 || selvalue || ',';
END LOOP label2;
dumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ');';
-- LOOP ARRAY (EACH FIELD) --
-- ----------- --
-- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
-- debug: RETURN NEXT selquery;
RETURN NEXT dumpquery_0 || dumpquery_1;
END LOOP label1 ;
-- SELECT LOOP --
-- ----------- --
RETURN ;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
А потом:
-- for a range
SELECT dump('public', 'my_table','my_id between 123456 and 123459');
-- for the entire table
SELECT dump('public', 'my_table','true');
протестировано на моем postgres 9.1, с таблицей со смешанным типом данных поля (text, double, int, отметка времени без часового пояса и т. д.).
Вот почему необходим CAST in TEXT. Мой тестовый запуск корректно для примерно 9 миллионов строк, похоже, что он провалился незадолго до 18 минут работы.
PS: я нашел эквивалент для MySQL на веб-сайте.
Вы можете просмотреть таблицу с указанными записями, а затем сбросить файл sql.
CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'
Мне нужен был способ создания операторов вставки без создания временной таблицы (в производстве). Я видел некоторые полезные аргументы в пользу pg_dump выше, но все же в конечном итоге придумал способ
генерировать операторы вставки и выгружать в файл
Приведенное ниже утверждение сделало трюк, который, как мне кажется, будет полезен людям, которые в конечном итоге получат аналогичный ответ.
$ /usr/local/bin/pg_dump -h the_db_host_address -d the_db_name --table=schema_name.table_name --data-only --column-inserts -U postgres -p the_port_number -v -f /Users/the_user/folder_name/insrt_stmts_file_name.sql
Следующее - это приглашение с запросом пароля, в приведенном выше случае я предоставил
postgres пароль, но любой другой пользователь, у которого есть возможность чтения, должен работать нормально.
Я запустил только операторы вставки из файла с именем:
insrt_stmts_file_name.sqlв клиенте DBeaver.
Но то же самое можно запустить и из командной строки, используя следующий скрипт:
/usr/local/bin/psql -h the_db_host_address -d the_db_name -v -U postgres -f /Users/the_user/folder_name/insrt_stmts_file_name.sql
Справочник по флагам pg_dump / psql: -h = host -d = dbname -v = verbose (выводится по мере продвижения) -U = db username -f = file / path
Я просто выполнил быструю процедуру, чтобы сделать это. Это работает только для одной строки, поэтому я создаю временное представление, которое просто выбирает нужную строку, а затем заменяю pg_temp.temp_view на фактическую таблицу, в которую я хочу вставить.
CREATE OR REPLACE FUNCTION dv_util.gen_insert_statement(IN p_schema text, IN p_table text)
RETURNS text AS
$BODY$
DECLARE
selquery text;
valquery text;
selvalue text;
colvalue text;
colrec record;
BEGIN
selquery := 'INSERT INTO ' || quote_ident(p_schema) || '.' || quote_ident(p_table);
selquery := selquery || '(';
valquery := ' VALUES (';
FOR colrec IN SELECT table_schema, table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = p_table and table_schema = p_schema
ORDER BY ordinal_position
LOOP
selquery := selquery || quote_ident(colrec.column_name) || ',';
selvalue :=
'SELECT CASE WHEN ' || quote_ident(colrec.column_name) || ' IS NULL' ||
' THEN ''NULL''' ||
' ELSE '''' || quote_literal('|| quote_ident(colrec.column_name) || ')::text || ''''' ||
' END' ||
' FROM '||quote_ident(p_schema)||'.'||quote_ident(p_table);
EXECUTE selvalue INTO colvalue;
valquery := valquery || colvalue || ',';
END LOOP;
-- Replace the last , with a )
selquery := substring(selquery,1,length(selquery)-1) || ')';
valquery := substring(valquery,1,length(valquery)-1) || ')';
selquery := selquery || valquery;
RETURN selquery;
END
$BODY$
LANGUAGE plpgsql VOLATILE;
Вызывается так:
SELECT distinct dv_util.gen_insert_statement('pg_temp_' || sess_id::text,'my_data')
from pg_stat_activity
where procpid = pg_backend_pid()
Я не проверял это с помощью инъекционных атак, пожалуйста, дайте мне знать, если для этого недостаточно вызова quote_literal.
Также он работает только для столбцов, которые можно просто привести к::text и обратно.
Также это для Greenplum, но я не могу придумать причину, почему это не будет работать на Postgres, CMIIW.
Вот функции BASH, которые я использую, чтобы легко сделать это из CLI:
function psql_oneoff() {
local query=$1
PGPASSWORD=$POSTGRES_PASSWORD "psql" \
-d "$PG_DB_NAME" \
-h "${PG_HOST:-localhost}" \
-U "${POSTGRES_USER:-$USER}" \
-p "$PG_PORT" \
-c "$query"
}
function dump_sql_query_as_insert() {
local query=$1
local temp_table_name="_tmp_dump_as_inserts"
psql_oneoff "DROP TABLE IF EXISTS $temp_table_name"
full_query="CREATE TABLE $temp_table_name AS $query"
psql_oneoff "$full_query"
PGPASSWORD=$POSTGRES_PASSWORD pg_dump \
--host "${PG_HOST:-localhost}" \
--user "${POSTGRES_USER:-$USER}" \
--port "$PG_PORT" \
--column-inserts \
--data-only \
--table="$temp_table_name" \
"$PG_DB_NAME"
psql_oneoff "DROP TABLE $temp_table_name"
}
# Can be used like this:
dump_sql_query_as_insert "SELECT * FROM important_table WHERE clever_condition = true"
Просто для контекста: я в основном использую это для создания необходимых исходных данных в средах тестирования и локальной разработки.
Щелкните правой кнопкой мыши базу данных и выберите резервное копирование
Затем используйте команды вставки только из данных и запросов из раздела параметров дампа.
Тогда Формат "Обычный" выбрал именно его.
Просто добавить простой способ, но ручной метод.
1) Используя PGADMIN 4, после запроса данных загрузите данные в формате csv.
откройте csv в любом блокноте и скопируйте и вставьте данные в онлайн-конвертер csv в sql. Например: https://www.convertcsv.com/csv-to-sql.htm . Вы можете установить имя целевой таблицы. Выходом являются сценарии вставки sql.
Скопируйте сценарии INSERT обратно в PGADMIN4 в целевом окне запроса базы данных.
Вы пробовали в pgadmin выполнить запрос с " EXECUTE QUERY WRITE RESULT TO FILE " вариант
его только экспорт данных, иначе попробуйте как
pg_dump -t view_name DB_name > db.sql
Опция -t используется для ==> дампить только таблицы (или представления или последовательности), соответствующие таблице, см.