CODEWITHSUNDEEP
postgresql
Elitmiar
Elitmiar

Reputation: 36879

Getting the encoding of a Postgres database

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

Upvotes: 189

Views: 184495

Answers (7)

brunovianarezende
brunovianarezende

Reputation: 866

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

Upvotes: 4

Bohemian
Bohemian

Reputation: 425288

From the command line:

psql my_database -c 'SHOW SERVER_ENCODING'

From within psql, an SQL IDE or an API:

SHOW SERVER_ENCODING;

Upvotes: 241

AMADANON Inc.
AMADANON Inc.

Reputation: 5929

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

psql -l

Shows all the database names, encoding, and more.

Upvotes: 13

Basil Bourque
Basil Bourque

Reputation: 340025

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

Upvotes: 16

RunningAdithya
RunningAdithya

Reputation: 1754

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

Upvotes: 68

Greenisha
Greenisha

Reputation: 1437

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

Upvotes: 34

Peter Eisentraut
Peter Eisentraut

Reputation: 36759

A programmatic solution:

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

Upvotes: 53

Related Questions