Determine Character Encoding on PostgreSQL Database System

PostgreSQL database system has a versatile character set support as described in the documentation. Since an application that uses a PostgreSQL database may run on different systems, it is a good practice to know what character set we are dealing with. We can query a database's encoding scheme in a number of methods.

1. Via the SHOW SERVER_ENCODING command in psql, e.g., assuming the database is foodb,

   
   foodb=> SHOW SERVER_ENCODING
       

2. Use a SQL query on pg_database, e.g., assuming the database is foodb,

   
   foodb=>  SELECT pg_encoding_to_char(encoding) 
               FROM pg_database WHERE datname = 'foodb';
       

3. Use a SQL query the information_schema, e.g., assuming the database is foodb,

   
   foodb=>  SELECT character_set_name 
               FROM information_schema.character_sets 
               WHERE default_collate_catalog='foodb'
       

In the above, the 3rd method is portable across most relational database systems since information_schema is a standard.