PostgreSQL queries to show database and server collation, language, and encoding settings.
# Show PostgreSQL Language and Encoding
PostgreSQL queries to show database and server collation, language, and encoding settings:
## Database-Level Settings
```sql
-- Show current database encoding, collation, and locale
SELECT
datname as database_name,
encoding,
datcollate as collation,
datctype as character_classification,
pg_encoding_to_char(encoding) as encoding_name
FROM pg_database
WHERE datname = current_database();
```
## Server-Level Settings
```sql
-- Show server configuration settings
SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN (
'lc_collate',
'lc_ctype',
'lc_messages',
'lc_monetary',
'lc_numeric',
'lc_time',
'server_encoding',
'client_encoding',
'default_text_search_config'
);
```
## Current Session Settings
```sql
-- Show current session encoding and locale settings
SHOW server_encoding;
SHOW client_encoding;
SHOW lc_collate;
SHOW lc_ctype;
SHOW lc_messages;
```
## All Databases Overview
```sql
-- Show encoding and collation for all databases
SELECT
datname as database_name,
pg_encoding_to_char(encoding) as encoding,
datcollate as collation,
datctype as ctype
FROM pg_database
ORDER BY datname;
```
## Comprehensive View
```sql
-- Combined view of important encoding/collation info
SELECT
'Database' as scope,
current_database() as name,
pg_encoding_to_char(encoding) as encoding,
datcollate as collation,
datctype as ctype
FROM pg_database WHERE datname = current_database()
UNION ALL
SELECT
'Server' as scope,
'server_encoding' as name,
setting as encoding,
null as collation,
null as ctype
FROM pg_settings WHERE name = 'server_encoding';
```
These queries will help you understand the character encoding, collation, and locale settings at both the database and server levels.