Getting the encoding of a Postgres database
PostgresqlPostgresql Problem Overview
I have a database, and I need to know the default encoding for the database. I want to get it from the command line.
Postgresql Solutions
Solution 1 - Postgresql
From the command line:
psql my_database -c 'SHOW SERVER_ENCODING'
From within psql, an SQL IDE or an API:
SHOW SERVER_ENCODING;
Solution 2 - Postgresql
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
Solution 3 - Postgresql
A programmatic solution:
SELECT pg_encoding_to_char(encoding) FROM pg_database WHERE datname = 'yourdb';
Solution 4 - Postgresql
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
Solution 5 - Postgresql
Because there's more than one way to skin a cat:
psql -l
Shows all the database names, encoding, and more.
Solution 6 - Postgresql
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.
The third column is character_set_name:
>Name of the character set, currently implemented as showing the name of the database encoding