4. psql

4.1. How do I get query results formatted in the same style as in MySQL or Oracle (SQL*PLUS)?

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)

4.2. How do I force null values to be displayed as NULL?

Use \pset null NULL.

Put this command in the psql configuration file ~/.psqlrc to make this change permanent.

4.3. How can I insert character codes using psql?

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$$'

4.4. A \connect to a non-existent user causes psql to disconnect completely

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.