How to fix Character with byte sequence 0xXX in encoding "Y" has no equivalent in encoding "Z" in PostgreSQL

-- Check current database encoding
SHOW client_encoding;
SHOW server_encoding;

-- Set client encoding to match server
SET client_encoding = 'UTF8';

import psycopg2
conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="user",
    password="password",
    client_encoding='utf8'
)

const { Client } = require('pg');
const client = new Client({
  connectionString: 'postgresql://user:password@localhost/mydb',
  statement_timeout: 30000,
  application_name: 'myapp'
});

-- Find rows with non-ASCII characters (UTF-8 multi-byte sequences)
SELECT id, column_name 
FROM your_table 
WHERE column_name::bytea != convert(column_name::text, 'UTF8'::name, 'LATIN1'::name)::bytea;

-- Replace smart quotes with regular quotes
UPDATE your_table 
SET column_name = REPLACE(column_name, '\u201c', '\"')
WHERE column_name LIKE '%\u201c%';

-- Or replace with spaces
UPDATE your_table
SET column_name = REPLACE(column_name, '\u2013', '-')
WHERE column_name LIKE '%\u2013%';

# Dump FROM original encoding (do not force UTF-8)
pg_dump -E LATIN1 -U username original_database > backup.sql

# Restore TO target database
psql -U username -d target_database < backup.sql

# Convert dump file encoding
iconv -f UTF-8 -t LATIN1 backup.sql > backup_latin1.sql
psql -U username -d target_database < backup_latin1.sql

pg_dumpall -g | psql  # Dump globals
pg_dump original_db | psql target_db  # Clone database

'pgsql' => [
    'driver' => 'pgsql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', 5432),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'schema' => 'public',
    'sslmode' => 'prefer',
],

Properties props = new Properties();
props.setProperty("user", "username");
props.setProperty("password", "password");
props.setProperty("stringtype", "unspecified");  // Handles encoding better
Connection conn = DriverManager.getConnection("jdbc:postgresql://host/db", props);