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?

• user1822: What is the exact error message you get. The spaces are not a problem. But the you have not closed the ( for the values clause. And you are missing a value for the end_date column
• codeninja84: I posted only part of the query for IPR reasons but it is well formed in terms of brackets. By removing spaces, I was able to run one of the inserts. The error says "syntax error near values".
• user1822: Works for me: imgur.com/a/8ryMj You need to post the complete statement, otherwise this is impossible to answer. By removing parts of the statement you probably changed it so that the culprit can no longer be seen.
• Craig Ringer: I've seen similar errors when editors use nonprinting characters in white space. There's no really easy way to clean up.

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.

• Andriy M: The issue is not about spaces in returned values, it's about spaces in the text of the SQL query/script. You cannot resolve them by including the REPLACE function into your SQL statement, because the problem is running the SQL statement in the first place.
• theblackwidower: I see. I must've misread the question. In that case, I don't see why that would be a problem, since the whitespace should be consolidated when it's interpreted. Seems like they missed a bigger issue.