<img height="1" width="1" style="display:none;" alt="" src="https://px.ads.linkedin.com/collect/?pid=2826169&amp;fmt=gif">
Start  trial

    Start trial

      If the PostgreSQL server and client have different character encoding, PostgreSQL will automatically convert the character set, and this may cause unexpected results.

      Let's say that the character set of the PostgreSQL server is UTF-8 and you connect to the server via a Windows client, which uses the SJIS encoding by default. In such a case, the client encoding will be used for the data unless you specify a different one.

      If you want to use the encoding of the server when extracting data to a file, use the COPY command and specify the PostgreSQL server encoding in the ENCODING option.

      Execution example

      In this example, the character encoding of the PostgreSQL server is UTF-8 and you want to extract the data  from myschema.table1 to C:\tmp\table1.txt.

      mydb=# \copy myschema.table1 to 'C:\tmp\table1.txt' encoding 'UTF8';
      Use the meta command (\copy) to access the client file. The encoding of the data output to C:\tmp\table1.txt will be UTF-8 as specified above.

      Key points

      • The data is copied using the encoding specified in the ENCODING option or the current encoding used on the client.
        If you omit the ENCODING option, the client encoding is used. An error will occur if the table contains characters that are not supported by the client encoding.
      • There are the following ways to change the client encoding after connection. You can also use the PGCLIENTENCODING environment variable to set the client encoding when connecting to the server.
        • Use the \encoding command in psql
        • Use functions that control the client encoding of libpq
        • Use SET client_encoding TO
      • Note that the encoding conversion occurs in the same way even when storing data from a file to a table with the COPY command.
      • To query the current client encoding, run SHOW client_encoding in psql.
        mydb=# SHOW client_encoding;
        client_encoding
        -----------------
        SJIS
        (1 row)
      For more information on how to copy data between a table and a file, refer to the PostgreSQL documentation, section COPY.

       

      Related PostgreSQL Insider articles

      PostgreSQL backup and recovery
      This article introduces the basic concepts and types of backup and recovery in PostgreSQL, as well as examples of how to choose a method for a desired outcome.
      Logical backup and recovery
      This article covers the various methods of logical backup and recovery, and which method is useful in what kind of use cases.
      logcial-backup-and-recoery
      Restoring a database to another database cluster
      This article provides the steps and key points to keep in mind when restoring a database to another database cluster.
      restore-to-another-cluster

      Fujitsu Enterprise Postgres is designed for developers, data architects, and DBAs seeking the efficiency of a leading open source based database technology with the enhanced security and performance features plus support required in a serious business environment.

      Brochure

      See how FUJITSU Enterprise Postgres enhances PostgreSQL providing 5 key enterprise capabilities for your mission-critical applications.

      1st  page brochure FUJITSU Enterprise Postgeres - Fujitsu's enhanced open source PostgreSQL
      View brochure

      Read our latest blogs

      Read our most recent articles regarding all aspects of PostgreSQL and Fujitsu Enterprise Postgres.