Skip to main content

Database - PostgreSQL

Before you can start working with PostgreSQL, you need to create a new database.

Creating a New Database​

Using CLI​

Start PostgreSQL command-line interface
psql

Type the following command to create a new database.

CREATE DATABASE dbname;

Additional options for creating database.

CREATE DATABASE <database_name>
    WITH OWNER = <owner_name>
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

Collation and Character classification​

LC_COLLATE and LC_CTYPE are two settings in PostgreSQL that control how text data is sorted, compared, and indexed.

  • LC_COLLATE: The LC_COLLATE setting controls the collation rules used for sorting and comparing text data in a particular locale. A collation is a set of rules that define how text data should be ordered based on the language and cultural conventions of a particular locale.

There are many collations available in PostgreSQL, including ones for different languages and locales. For example, the en_US.utf8 collation is designed for English text data in the United States, while the fr_FR.utf8 collation is designed for French text data in France. When creating a database or table, you can specify the collation to use for text data using the LC_COLLATE parameter.

  • LC_CTYPE: The LC_CTYPE setting controls the character classification and case conversion rules used for text data in a particular locale. Character classification refers to the process of identifying and categorizing characters based on their type, such as letters, digits, punctuation, and whitespace. Case conversion refers to the process of converting characters between upper- and lowercase forms.

In PostgreSQL, you can set the LC_CTYPE parameter when creating a database, schema, or column to specify the character classification and case conversion rules to use for text data. There are many character sets available in PostgreSQL, including ASCII, UTF-8, and many others. Choosing an appropriate character set is important to ensure that all of the characters needed for the data being stored are supported.