Prior to pg 9.1, you could connect to any database with psql regardless of encoding and you’d get the server encoding as your client encoding unless you set it:
C:\>chcp 1252 Active code page: 1252 C:\>"C:\Program Files\PostgreSQL\9.0\bin\psql" -U glyn -d TEST -h pgtest psql (9.0.22, server 9.4.4) WARNING: psql version 9.0, server version 9.4. Some psql features might not work. SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256) Type "help" for help. TEST=> show client_encoding; client_encoding ----------------- LATIN1 (1 row)
That wasn’t quite right; the client_encoding is a lie. On a modern psql version that’s quite rightly prevented:
C:\>"C:\Program Files (x86)\pgAdmin III\1.20\psql" -U glyn -d TEST -h pgtest psql: FATAL: conversion between WIN1252 and LATIN1 is not supported
This is not an issue if you want to connect to a utf8 database, but the issue I had this morning was connecting to a latin1 database with psql from a Windows client (something I do rarely). If I set the codepage to utf8 to match client encoding, I got a “Not enough memory.” error:
C:\>chcp 65001 Active code page: 65001 C:\>set PGCLIENTENCODING=UTF8 C:\>"C:\Program Files (x86)\pgAdmin III\1.20\psql" -U glyn -d TEST -h pgtest psql (9.4.0, server 9.4.4) WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 25 6, compression: off) Type "help" for help. TEST=> show client_encoding; Not enough memory.
I could set the codepage to 1252, but that would mean my setting for client_encoding would be a lie, and if I were to then revert to set client_encoding=’WIN1252′ I’d have come full circle and be back at the “FATAL: conversion between WIN1252 and LATIN1 is not supported” error message.
A quick google revealed these bug reports with no solutions. Another dig at the docs revealed the following passage:
pager
Controls use of a pager program for query and psql help output. If the environment variable PAGER is set, the output is piped to the specified program. Otherwise a platform-dependent default (such as more) is used.
So how does more behave?
C:\>chcp 65001 Active code page: 65001 C:\>more Not enough memory.
Bingo! So if I turn the pager off the error should go:
C:\>chcp 65001 Active code page: 65001 C:\>set PGCLIENTENCODING=UTF8 C:\>"C:\Program Files (x86)\pgAdmin III\1.20\psql" -U glyn -d TEST -h pgtest WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 25 6, compression: off) Type "help" for help. TEST=> \pset pager off Pager usage is off. TEST=> show client_encoding; client_encoding ----------------- UTF8 (1 row)
Cluprit found; quite embarrassing that the source of such a verbose error as “Not enough memory.” being Microsoft didn’t occour sooner. So lets try a different pager (sourced from)
C:\>chcp 65001 Active code page: 65001 C:\>set PGCLIENTENCODING=UTF8 C:\>set PAGER="C:\Program Files (x86)\gnuwin32\bin\less" C:\>set LESS=--quit-at-eof C:\>"C:\Program Files (x86)\pgAdmin III\1.20\psql" -U glyn -d TEST -h pgtest psql (9.4.0, server 9.4.4) WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page "Notes for Windows users" for details. SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 25 6, compression: off) Type "help" for help. TEST=> show client_encoding; client_encoding ----------------- UTF8 (1 row) (END)
Thank you for this post. It is the first time I found a working solution for this special problem. As an alternative you can use cat.exe. The text won’t disappear anymore.