How to concatenate psql variables?

Question

How can I concatenate two psql (PostgreSQL client) variables? I want to generate an absolute path by concatenating a directory path variable and a filename variable.

I've tried this:

\set path '/tmp/'
\set file 'foo'
\echo :path:file

But psql puts a space between the path and the file, and outputs:

/tmp/ foo

Erwin Brandstetter · Accepted Answer · 2012-07-19T12:46:40.573

10

\set path '/tmp/'
\set file 'foo'
\set pf :path:file \echo :pf
/tmp/foo

Why does this work? I quote the manual here:

\set [ name [ value [ ... ] ] ]

Sets the internal variable name to value or, if more than one value is given, to the concatenation of all of them. [...]

Emphasis mine.

edited Jul 19 '12 at 12:46

answered Jul 19 '12 at 01:37

Erwin Brandstetter

175,982
27
439
600

1

Even better than mine – András Váczi Jul 19 '12 at 06:23

András Váczi · Answer 2 · 2012-07-18T12:49:47.423

4

Try this:

\set path /tmp/
\set file foo
\qecho :path:file \o | sed s/\ //
/tmp/foo

\qecho writes to the query output channel (unlike \echo, which writes to standard output). \o | then redirects the output to the subsequent command.

edited Jul 18 '12 at 12:49

answered Jul 18 '12 at 12:44

András Váczi

31,278
13
101
147

score 0 · Answer 3 · answered Jul 18 '12 at 11:23

0

It looks like you want either the || operator or the concat function:

http://www.postgresql.org/docs/9.1/static/functions-string.html

answered Jul 18 '12 at 11:23

Cargo23

249
1
4

1

How do you use these with \echo? – András Váczi Jul 18 '12 at 12:26
This answer refers to concatenation in SQL while the question is about psql (the terminal interface). – Erwin Brandstetter Feb 04 '13 at 20:20

How to concatenate psql variables?

3 Answers3

Linked