PostgreSQL CREATE DATABASE Statement

PostgreSQL CREATE DATABASE Statement

The PostgreSQL CREATE DATABASE statement is used to create a new database in a PostgreSQL server. This statement is essential for setting up a new environment for storing and managing data.

---

Syntax

CREATE DATABASE database_name
[WITH [OWNER = owner_name]
      [TEMPLATE = template]
      [ENCODING = encoding]
      [LC_COLLATE = lc_collate]
      [LC_CTYPE = lc_ctype]
      [TABLESPACE = tablespace_name]
      [CONNECTION LIMIT = limit]];

The CREATE DATABASE statement has the following components:

• database_name: The name of the database to be created.
• OWNER: Optional. The name of the database owner.
• TEMPLATE: Optional. The template database to be used for creating the new database.
• ENCODING: Optional. The character set encoding for the new database.
• LC_COLLATE: Optional. The collation order (sort order) for the new database.
• LC_CTYPE: Optional. The character classification for the new database.
• TABLESPACE: Optional. The tablespace for the new database.
• CONNECTION LIMIT: Optional. The maximum number of concurrent connections to the new database.

---

Example PostgreSQL CREATE DATABASE Statement Queries

Let's look at some examples of PostgreSQL CREATE DATABASE statement queries:

1. Basic CREATE DATABASE Example

CREATE DATABASE mydb;

This query creates a new database named mydb with default settings.

2. CREATE DATABASE with Owner

CREATE DATABASE mydb WITH OWNER = myuser;

This query creates a new database named mydb and assigns ownership to the user myuser.

3. CREATE DATABASE with Encoding and Locale

CREATE DATABASE mydb WITH ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';

This query creates a new database named mydb with UTF-8 encoding and US English locale settings.

---

Full Example

Let's go through a complete example that includes creating a database, connecting to it, and creating a table.

Step 1: Creating a Database

This step involves creating a new database named mydb with specific settings.

CREATE DATABASE mydb WITH OWNER = myuser
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = pg_default
CONNECTION LIMIT = 10;

In this example, we create a database named mydb with ownership assigned to myuser, UTF-8 encoding, US English locale settings, the default tablespace, and a connection limit of 10.

Step 2: Connecting to the Database

This step involves connecting to the newly created database mydb.

\c mydb;

Here, we use the \c command in the psql command-line interface to connect to the database mydb.

Step 3: Creating a Table in the Database

This step involves creating a new table named employees in the mydb database.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

Here, we create a table named employees with columns for id, first_name, last_name, and email in the mydb database.

Conclusion

The PostgreSQL CREATE DATABASE statement is a fundamental tool for setting up new databases. Understanding how to use the CREATE DATABASE statement and its syntax is essential for effective database management and organization in PostgreSQL.