Specifying Windows Paths in PostgreSQL Expressions

Not too many expressions exist in Postgres ' SQL dialect, requiring the file system path as an argument. On the move I can name a couple:

• CREATE TABLESPACE ...
• CREATE FUNCTION ... (in the case of dynamic loading of functions in the C language)

So, the crux of the matter is that these arguments are represented by string constants. And this in turn means the absence of lexical verification, which leads to different effects in different operating systems. In order not to spread the thought on the tree:

Windows users! Replace your usual slashes (backslash, in fact) with these slashes: '/', in the parameters indicating the system path!

NOT:

CREATE TABLESPACE dbspace
LOCATION 'C:\Program Files\PostgreSQL\8.3\data\dbs' ;

CREATE TABLESPACE dbspace
LOCATION E'C:\Program Files\PostgreSQL\8.3\data\dbs' ;

* This source code was highlighted with Source Code Highlighter .

')
YES:

CREATE TABLESPACE dbspace
LOCATION 'C:/Program Files/PostgreSQL/8.3/data/dbs' ;

CREATE TABLESPACE dbspace
LOCATION 'C:\\Program Files\\PostgreSQL\\8.3\\data\\dbs' ;
-- !

CREATE TABLESPACE dbspace
LOCATION E'C:\\Program Files\\PostgreSQL\\8.3\\data\\dbs' ;

* This source code was highlighted with Source Code Highlighter .

PS: I’m not a fan of absolute paths, so I’ll ask: “Why do we have dynamic_library_path magic for CREATE FUNCTION , but for CREATE TABLESPACE, there’s nothing like that (even a run-down run-time parameter)?