Skip to main content

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.