Determine Character Encoding on PostgreSQL Database System

PostgreSQL database system has a versatile character set support as described in the documentation. Since an application that uses a PostgreSQL database may run on different systems, it is a good practice to know what character set we are dealing with. We can query a database's encoding scheme in a number of methods.

1. Via the SHOW SERVER_ENCODING command in psql, e.g., assuming the database is foodb,

   
   foodb=> SHOW SERVER_ENCODING
       

2. Use a SQL query on pg_database, e.g., assuming the database is foodb,

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

3. Use a SQL query the information_schema, e.g., assuming the database is foodb,

   
   foodb=>  SELECT character_set_name 
               FROM information_schema.character_sets 
               WHERE default_collate_catalog='foodb'
       

In the above, the 3rd method is portable across most relational database systems since information_schema is a standard.

Command psql of PostgreSQL Complains about Encoding

I have a PostgreSQL database instance configured to use UTF-8. When I connects to it using psql on Windows, it complains with the following,

foopgdb => select * from bar;
ERROR:  character with byte sequence 0xe2 0x86 0x92 in encoding "UTF8" has no 
equivalent in encoding "WIN1252"
foopgdb =>

Method 1

It turns out that we can inform psql the encoding and select proper encoding table when we run psql as follows in a Windows Command Prompt window,

SET PGCLIENTENCODING=utf-8
chcp 65001

The explanation about PGCLIENTENCODING is in the PostgreSQL documentation while that of chcp at Microsoft Technet. The reference to the code page 65001 is at this MSDN page.

However, the above method may result in the Not enough memory error on some version of Windows, such as, Windows 7. See this discussion for more.

Method 2

We can also set the encoding in psql as follows,

foopgdb => \encoding UTF8
foopgdb => select * from bar;
...... 
foopgdb =>

Higher Education: Articles on Ideological Diversity on Campuses

I happened to come cross a few articles on the topic of ideological diversity on college and university campuses. This topic interests me.

Nicholas Kristof writes at the New York Times,

"Universities are the bedrock of progressive values, but the one kind of diversity that universities disregard is ideological and religious. We’re fine with people who don’t look like us, as long as they think like us."

In parallel, Fredrik deBoer argues at the Los Angles Times,

"But academics are at fault, too, because we’ve pushed mainstream conservatism out of our institutions. Sociologists Neil Gross and Solon Simmons have found that about half of professors identify as liberal, versus only 14% who identify as Republican. (At the time of their study, in 2006, only a fifth of American adults described themselves as liberal.)

......

In my network of professional academics, almost no one recognizes that our lopsided liberalism presents a threat to academia itself."

and suggests that,

"Our public universities are under massive pressure and at immense risk, and those who should be defenders of public universities still don’t understand that they’ve created the conditions for their destruction."