SQL script для создания вставки script

Немного смутного названия, я объясню.

Я пишу SQL script для создания инструкции insert для каждой строки таблицы в моей базе данных, чтобы иметь возможность применить эти данные к другой базе данных.

Вот что я имею в данный момент:

SELECT 'INSERT INTO products (id,name,description) VALUES ('||ID||','''||name||''','''||description||''');' FROM products

И он отлично работает, выводя это:

INSERT INTO products (id,name,description) VALUES (1,'Lorem','Ipsum');
INSERT INTO products (id,name,description) VALUES (2,'Lorem','Ipsum');
INSERT INTO products (id,name,description) VALUES (3,'Lorem','Ipsum');
INSERT INTO products (id,name,description) VALUES (4,'Lorem','Ipsum');

Проблема в том, что если одно из полей пуст, строка не сможет создать обновление script, в выходном файле строка будет пустой. Очевидно, что, поскольку есть 20 полей, некоторые необязательные это означает, что вряд ли сгенерирован какой-либо из моих скриптов.

Есть ли способ решить эту проблему?

Ответ 1

В случае полей NULL вы можете сделать что-то вроде

Select COALESCE(Name, '') from...

Функция coalesce возвращает первое ненулевое значение в списке.

Для действительно пустых полей (например, пустой nvarchar), я считаю, что ваш script выше будет работать.

Ответ 2

pg_dump -a -U user1 -t products -f products.copy database1

а затем:

psql -U user2 -d database2 -f products.copy

и все готово. Это также безопаснее и быстрее.

Ответ 3

Используйте функцию quote_nullable() new в PostgreSQL 8.4. В дополнение к разрешению значений NULL он сохраняет ваши типы данных и защищает вас от таблиц Bobby (SQL-инъекций):

SELECT 'INSERT INTO products (id,name,description) VALUES (' ||
quote_nullable(ID) || ',' || quote_nullable(name) || ',' ||
quote_nullable(description) || ');' FROM products;

В более старых версиях вы получаете одинаковое поведение с coalesce() и quote_literal():

SELECT 'INSERT INTO products (id,name,description) VALUES (' ||
coalesce(quote_literal(ID), 'null') || ',' ||
coalesce(quote_literal(name), 'null') || ',' ||
coalesce(quote_literal(description), 'null') || ',' ||
');' FROM products;

Ответ 4

Я написал python script на основе ответа @intgr, чтобы построить инструкцию select. Он принимает разделенный запятыми список столбцов из stdin (use -).

Я хотел использовать sqlparse, но я не мог понять, как использовать эту библиотеку.

import fileinput

names = ' '.join(fileinput.input())

ns = [x.strip() for x in names.split(',')]
quoted = ['quote_nullable(' + x + ')' for x in ns]
insert = "SELECT  'INSERT INTO <TABLE> ( " + (', ').join(ns) + " ) VALUES(' || " + (" || ',' || ").join(quoted)  + " || ');' FROM <TABLE>"
print insert

Суть script находится здесь: https://gist.github.com/2568047