PostgreSQL Local Database

Faruk Erdem

2 years ago

In today’s article we will learn PostgreSQL Local Database Settings (LC_COLLATE, LC_CTYPE).

First, What is Encoding? Let’s start with this.

Encoding:

Encoding is the character sets that are used to translate every letter and character we press from the keyboard into computer language. We make choices because of the characters they contain.

So why utf-8 ?

UTF-8 is an encoding method (Unicode Transformation Format). When data is to be written to a file or passed over the network, it must be converted to bytes. This conversion process is called encoding.

The most commonly used method for encoding Unicode characters is UTF-8.

UTF-8 tries to encode the most Unicode character set in the most efficient way, i.e. taking up the fewest bytes.

That’s why UTF8 is usually chosen.

LC_COLLATE :

Collation is the part where we make the settings about how and according to which language the columns in the table are sorted.

LC_TYPE:

In the queries selected in this option, it has an important place in the features such as order by, that is, querying on text data.

Database Example:

CREATE DATABASE f1
    WITH
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

CREATE DATABASE f1

WITH

OWNER = postgres

ENCODING = 'UTF8'

LC_COLLATE = 'en_US.UTF-8'

LC_CTYPE = 'en_US.UTF-8'

TABLESPACE = pg_default

CONNECTION LIMIT = -1;

Table Example:

CREATE TABLE test (
  a text COLLATE"tr_TR",
  b text COLLATE"de_DE",
);

CREATE TABLE test (

a text COLLATE"tr_TR",

b text COLLATE"de_DE",

);

In the Table example above, we have given the columns in different collate types.

Our B column will be sorted in German.

Our column A will be sorted in Turkish.

This database will be useful when you want to use a different collate than the Collate.