To get query output in a similar style to the MySQL monitor program, use \pset border 2.
test=# SELECT 'Hello', 'World'; +----------+----------+ | ?column? | ?column? | +----------+----------+ | Hello | World | +----------+----------+ (1 row)
To get SQL*PLUS-style output, use \pset border 0.
test=# SELECT 'Hello', 'World'; ?column? ?column? -------- -------- Hello World (1 row)
To return output to the default psql style, use \pset border 1.
test=# SELECT 'Hello', 'World'; ?column? | ?column? ----------+---------- Hello | World (1 row)
Use \pset null NULL.
Put this command in the psql configuration file ~/.psqlrc to make this change permanent.
You wish to insert a character using its byte value. This is not a problem when carrying out queries from another programming language, because you can use that language's native functions to perform the conversion beforehand.
The short solution is to use escaped octal character codes, e.g.
INSERT INTO my_tbl (unitxt) VALUES('\303\244')
which will insert the character UTF-8 'ä'. If converting to octal is too much trouble, there is a more verbose solution using the function encode(), which converts a hexadecimal value into a binary (bytea) value, followed by decode() to convert it into a TEXT value, e.g.
INSERT INTO my_tbl (unitxt) VALUES(encode(decode('c3a4','hex'), 'escape'))
And if you are working with decimal use to_hex():
INSERT INTO my_tbl (unitxt) VALUES(encode(decode(to_hex(50084),'hex'), 'escape'))
Note these examples are not specific to psql and can be used in any context.
It is also possible to use psql's "native" interpolation facilities, albeit not directly in an SQL string constant:
test=> \set myvalue '\'This is a lower case a with an umlaut: \0xc3\0xa4\'' test=> INSERT INTO my_tbl VALUES(:myvalue);
Beginning with PostgreSQL 8.0 it is also possible to write:
test=> \set myvalue '$$This is a lower case a with an umlaut: \0xc3\0xa4$$'
When issuing a \connect command from a script where the user name does not exist in the database, psql stops with a FATAL error but does not retain the previous connection, whereas issuing the same command directly in psql does. E.g.:
foo=# \! cat /tmp/connect.sql \connect - foo foo=# \connect - foo FATAL: user "foo" does not exist Previous connection kept foo=# \i /tmp/connect.sql psql:/tmp/connect.sql:1: \connect: FATAL: user "foo" does not exist !> \l You are currently not connected to a database. !>
This is defined behaviour and exists as a safety mechanism to prevent non-interactive scripts acting on the wrong database. See man 1 psql (online version: http://www.postgresql.org/docs/current/static/app-psql.html) for further details.