SQL script to create insert script

Question

A bit of a vague title, I will explain.

I am writing an SQL script to create an insert statement for each row of a table in my database, purely to be able to apply that data back to another database.

Here is what I have at the moment:

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

And it works great, outputting this:

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');

The problem is if one of the fields is empty that row will fail to produce an update script, in the output file the line is just blank. Obviously as there are 20+ fields, some optional this means that hardly any of my scripts are generated.

Is there a way to solve this issue?

Answer 1

Use the quote_nullable() function new in PostgreSQL 8.4. In addition to permitting NULL values, it retains your data types and protects you from Bobby Tables (SQL injections):

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

In older versions, you get the same behavior with coalesce() and 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;

Answer 2

In the case of NULL fields you can do something like

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

The coalesce function returns the first nonnull value in the list.

For truly blank fields (empty nvarchar for instance) I believe your script above will work.

Answer 3

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

and then:

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

and you're done. It's also safer and faster.