Removing spaces from postgres SQL

Question

I have a sequence of insert statements which I want to run on a Postgres database. It looks something like this -

INSERT INTO test_admin.test_admins (id,
                                    "name",
                                    description,
                                    start_date,
                                    end_date)
        VALUES (
                  3,
                  'admin',
                  NULL,
                  TO_TIMESTAMP ('10/01/2015 00:00:00.00 -0500',
                                'MM/DD/YYYY fmHH24fm:MI:SS.FF Z'),

Due to the extra spaces preceding each line, neither pgadmin 3, nor the postgres console would accept this query and give syntax errors. What is the best way to remove these?

Answer 1

As spaces can be freely used in SQL, maybe you have some non-printable characters in your code. This problem usually relates to using Windows and/or word processors for programming. How (using what program) did you write your code?

Always use an editor designed for programming and always save in 'text only' mode or something similar. If you are in Windows, you can use Notepad (until you get a better editor which is out of scope here), copy-paste your code there and save it.

Answer 2

pg-minify can do what you want. In addition, it can compress (option compress) your SQL to its bare minimum, by removing all comments and all optional spaces.

Even better, it can automatically patch multi-line text with the correct E prefix that indicates multi-line syntax. Plus, it does some basic SQL parsing.

Such library as pg-promise, for example, uses it for compressing all SQL scripts on-the-fly for maximum performance.

Example:

var minify = require('pg-minify');

var sql = "SELECT 'abc'; -- comments";

minify(sql); //=> SELECT 'abc';
/* or:
minify(sql, {compress: true}); //=> SELECT'abc';
*/

Answer 3

The REPLACE command. I've used it several times for a specific search function. This should do it: REPLACE('your string', ' ', '') It will remove all spaces.