191

I have a database, and I need to know the default encoding for the database. I want to get it from the command line.

7 Answers 7

243

From the command line:

psql my_database -c 'SHOW SERVER_ENCODING'

From within psql, an SQL IDE or an API:

SHOW SERVER_ENCODING;
Sign up to request clarification or add additional context in comments.

2 Comments

Isn't that the server-level setting (the default used for newly created databases) rather than the database/catalog-level setting asked for in the Question?
Note that from within psql, there needs to be a semicolon at the end. i.e. SHOW SERVER_ENCODING;
69

Method 1:

If you're already logged in to the db server, just copy and paste this.

SHOW SERVER_ENCODING;

Result:

  server_encoding 
-----------------  
UTF8

For Client encoding :

 SHOW CLIENT_ENCODING;

Method 2:

Again if you are already logged in, use this to get the list based result

\l 

Comments

53

A programmatic solution:

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

Comments

34

If you want to get database encodings:

psql  -U postgres -h somehost --list

You'll see something like:

List of databases
           Name         |  Owner   | Encoding
------------------------+----------+----------
db1                     | postgres | UTF8

Comments

16

tl;dr

SELECT character_set_name 
FROM information_schema.character_sets 
;

Standard way: information_schema

From the SQL-standard schema information_schema present in every database/catalog, use the defined view named character_sets. This approach should be portable across all standard database systems.

SELECT * 
FROM information_schema.character_sets 
;

Despite the name being plural, it shows only a single row, reporting on the current database/catalog.

screenshot of pgAdmin 4 with results of query shown above

The third column is character_set_name:

Name of the character set, currently implemented as showing the name of the database encoding

Comments

13

Because there's more than one way to skin a cat:

psql -l

Shows all the database names, encoding, and more.

Comments

4

Another way of getting the server encoding (described at https://pgpedia.info/s/server_encoding.html):

 SELECT current_setting('server_encoding');

One can also use a similar select to get other settings, e.g. 'client_encoding'.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.