Save sql file without NULL values

Question

When I run

pg_dump -O --column-inserts -U root map --file=

anything that has the value NULL is also printed. Can I create an SQL file such that the columns which have NULL are not included?

--column-inserts does create a query which published column name and data.

Example:

Say I have a table

Name | class | element | Id

Nee     V       NULL      102
Mat     VI      NULL      103
...

When I export the sql file the data insertion lines look like

INSERT INTO table (Name, class, element, Id) VALUES ('Nee', 'V', NULL, 102);

what I would like is

INSERT INTO table (Name, class, Id) VALUES ('Nee', 'V', 102);

Such that the sql file doesn't contain any NULL element to be put into the database. This is required because in the system database, we use a lot of columns and my code only changes a few of them. Hence the sql generator has a LOT of NULLs.

Answer 1

First, there is no option. Secondly the inserts don't look like that which makes it more or less impossible to automatically process the dump to do what you want.

pg_dump creates inserts by default with an implicit column list, i.e.:

INSERT INTO test VALUES ('test2 test', 'foobar');

This is ok, because it uses the columns in the order specified in the table declaration. However, it also means you can't omit columns. So this isn't something you can quickly do with sed in an automatic fashion.

Determining a proper solution here depends on what problem exactly you are trying to solve. In the absence of a problem to solve, the best I can say is "don't even try to do this." There are likely better solutions for your problem.