SQLDatabase Fundamentalsbeginner35 min read

Creating Your First Database

Learn how to create, inspect, connect to, and safely delete PostgreSQL databases. Understand database naming, character encoding, locale settings, and the difference between SQL statements and psql commands.

On This Page (Table of Contents)

In this lesson, you will learn

✓Explain the purpose of Data Definition Language (DDL).
✓Create a PostgreSQL database using CREATE DATABASE.
✓Follow consistent database naming conventions.
✓Understand UTF-8 encoding and database locale settings.
✓List databases and inspect their metadata.
✓Switch databases using psql and connect to a specific database.
✓Explain the risks and requirements of DROP DATABASE.
✓Identify common errors when creating or deleting databases.

1. What Is Data Definition Language?

SQL commands serve different purposes. Data Definition Language (DDL) is used to define or change database structures.

Common DDL commands include:

- CREATE: creates a database object.
- ALTER: changes the definition of an existing object.
- DROP: removes a database object.

For example, CREATE TABLE defines a table, while CREATE DATABASE creates a database.

This lesson focuses on database-level operations in PostgreSQL. Some database commands have special execution requirements, so do not assume every DDL command behaves exactly like CREATE TABLE.

Note

PostgreSQL's CREATE DATABASE and DROP DATABASE commands cannot run inside a transaction block. Run them as standalone commands.

2. Understand What a Database Contains

Think of a database as a container for related database objects and data.

For example, an online store might use a database named codenivo_store to hold information about customers, products, orders, and payments. These objects are commonly organized into schemas and tables.

A database is not the same as a table. A database can contain many schemas, and each schema can contain tables and other database objects.

In PostgreSQL, separate databases normally have separate connection contexts. A standard query cannot directly refer to an ordinary table in another database in the same way it refers to a table in the current database. Cross-database access requires additional mechanisms or extensions.

Note

Use one database for a clearly defined application or environment when appropriate, then organize its tables into schemas as your design grows.

3. Choose Meaningful Database Names

A database name should make its purpose easy to understand.

Examples of useful names include:

- codenivo_store
- student_management
- library_system
- ecommerce_dev

Lowercase snake_case is a practical convention because it avoids unnecessary quoting in PostgreSQL. Names with spaces, mixed capitalization, or special characters can require quoted identifiers.

For example, a database created with a quoted mixed-case name may need the same exact quoting and capitalization when referenced later.

Also distinguish environments clearly. A development database should not be confused with a production database.

Create a database with a clear name

sql

1
CREATE DATABASE codenivo_store;

Output

CREATE DATABASE

PostgreSQL creates a database named codenivo_store. The command must be run by a role with the CREATEDB privilege or a superuser, and it must be executed outside a transaction block.

4. Create Your First Database

The simplest way to create a PostgreSQL database is with CREATE DATABASE followed by the database name.

For this lesson, use a practice database rather than a database that contains important data.

Create a practice database

sql

1
CREATE DATABASE codenivo_store;

Output

CREATE DATABASE

The command creates the database if the name is available and the current role has sufficient privileges. If the database already exists, PostgreSQL reports an error.

Note

CREATE DATABASE is a PostgreSQL command, not a command that works identically in every database system. The syntax and administrative requirements vary between products.

5. Character Encoding and UTF-8

Character encoding determines how text characters are represented in a database.

UTF-8 is widely used because it supports text from many writing systems, including English, Hindi, and other languages, as well as many symbols and emoji.

PostgreSQL databases have an encoding setting. A database commonly inherits its encoding from the template database used during creation.

For many new projects, the existing default UTF8 configuration is suitable. You do not need to specify the encoding every time. If you explicitly choose an encoding or locale, ensure the requested settings are compatible with the template and server configuration.

Specify UTF8 explicitly

sql

1
2
3
CREATE DATABASE codenivo_store_utf8
    WITH ENCODING = 'UTF8'
    TEMPLATE = template0;

This requests a database with UTF8 encoding using template0. template0 is useful when choosing database settings that differ from those of the normal template database. The operation can still fail if the requested configuration is unsupported or incompatible with the server.

Note

Use template0 when you need a clean template to specify compatible encoding or locale settings that differ from template1. For a normal database, the simple CREATE DATABASE statement is usually enough.

6. Understand Locale and Collation

Encoding and locale are related, but they are not the same thing.

**Encoding** determines how characters are represented.

**Collation** influences how strings are compared and sorted. For example, locale rules can affect the order of text values in an ORDER BY query.

**Character classification** influences how characters are categorized, such as uppercase and lowercase letters.

PostgreSQL may use operating-system locales or other supported locale providers. Names such as en_US.UTF-8 are not guaranteed to be available on every machine.

For this reason, do not copy a locale-specific CREATE DATABASE statement blindly. If your application does not need custom locale behavior, use the server's appropriate default settings.

Inspect database locale settings

sql

1
2
3
4
5
6
7
SELECT
    datname,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate,
    datctype
FROM pg_database
WHERE datname = 'codenivo_store';

The query reads database metadata. pg_encoding_to_char converts PostgreSQL's numeric encoding identifier into a readable name. datcollate and datctype show the database's collation and character-classification settings.

7. List and Inspect Databases

After creating a database, verify that it exists.

PostgreSQL stores database metadata in the system catalog pg_database. You can query that catalog to inspect database names and settings.

The following example is SQL and can be run in a PostgreSQL query editor or in psql.

List database names

sql

1
2
3
SELECT datname
FROM pg_database
ORDER BY datname;

This returns database names from PostgreSQL's database catalog. Access to database metadata and visibility may depend on permissions and server configuration.

Note

Inside psql, \l is a convenient shortcut for listing databases. The SQL query is useful when you want to understand the underlying catalog.

8. Connect to the New Database

Creating a database does not automatically move your current psql session into it.

Use the psql meta-command \c to connect to a different database. This command is handled by psql, not by the PostgreSQL SQL parser.

If you are connecting through a GUI, select or configure the database in that client's connection settings instead.

Switch databases in psql

sql

1
2
3
4
5
\c codenivo_store
 
SELECT
    current_database(),
    current_user;

Output

current_database | current_user ------------------+------------- codenivo_store | (your role)

The \c command asks psql to reconnect to codenivo_store. The SELECT query then reports the database and role for the new session. The exact role name depends on your connection.

Note

The \c command is a psql meta-command. It is not a normal SQL statement and will not work in every graphical SQL editor.

9. Create a Database Using the Command Line

PostgreSQL also provides the createdb operating-system utility. It is a command-line wrapper around database creation.

This can be useful in scripts and terminal workflows. The command must be run in your operating-system terminal, not entered as a SQL statement in a query editor.

Create a database from the terminal

bash

1
createdb -h localhost -p 5432 -U postgres codenivo_practice

The command connects to the PostgreSQL server on localhost port 5432 and requests a database named codenivo_practice. Authentication and permissions must be configured correctly. Depending on the environment, the utility may prompt for credentials or use the configured authentication method.

10. Drop a Database Safely

DROP DATABASE removes a database and its stored contents. This is a destructive operation and cannot be undone by PostgreSQL's normal transaction rollback.

Before deleting a database:

1. Verify the exact database name.
2. Confirm it is a disposable practice database or that an approved backup exists.
3. Connect to a different database, such as postgres.
4. Check whether other sessions are using the target database.
5. Execute the drop operation only when you are certain.

IF EXISTS avoids an error if the named database does not exist. It does not provide a backup, restore point, or safety confirmation.

Drop a disposable practice database

sql

1
2
-- Run while connected to a different database.
DROP DATABASE IF EXISTS codenivo_practice;

Output

DROP DATABASE

The command removes codenivo_practice if it exists. It fails if the session is connected to that target database or if other conditions, such as remaining connections or insufficient privileges, prevent the operation.

Note

Never run DROP DATABASE against a production database merely to test a command. Confirm the target, permissions, active connections, and recovery plan first.

11. Handle Active Connections

PostgreSQL normally refuses to drop a database while other sessions are connected to it.

The simplest beginner-friendly approach is to close the clients using your practice database, connect to another database, and then retry.

Recent PostgreSQL versions also support DROP DATABASE with the FORCE option. It attempts to terminate eligible connections, but it can still fail when certain conditions remain, such as prepared transactions or logical replication objects. It also requires suitable privileges.

Do not terminate sessions on a shared or production database without understanding the impact on the applications using it.

PostgreSQL option for a disposable database

sql

1
2
3
-- First connect to a different database.
DROP DATABASE IF EXISTS codenivo_practice
    WITH (FORCE);

FORCE attempts to terminate eligible connections before dropping the target. It is not a guarantee that every database can be dropped, and it should not be used casually on databases with active application workloads.

12. Common Errors and How to Fix Them

**Database already exists:** Choose a different name or verify whether the existing database is the one you intended to use. CREATE DATABASE does not support IF NOT EXISTS in the same form as some other database systems.

**Permission denied:** The connected role needs the CREATEDB privilege or superuser privileges to create a database.

**Cannot drop the currently open database:** Connect to another database before issuing DROP DATABASE.

**Database is being accessed by other users:** Close other clients or investigate the active connections. FORCE may help in suitable circumstances, but it has limitations.

**Invalid locale:** The locale may not be installed or supported by the server. Check the available locale configuration and use compatible settings.

**Cannot run inside a transaction block:** Execute CREATE DATABASE or DROP DATABASE as a standalone command, rather than inside an explicit transaction.

Note

Read the exact PostgreSQL error message before changing settings. The error usually identifies whether the problem is permissions, a connection, an unsupported setting, or an existing database.

13. Knowledge Check: MCQs

Answer each question before checking its explanation.

14. Guided Practice

Complete these tasks using a local PostgreSQL installation. Use a disposable practice environment.

**Task 1:** Create a database named sql_practice.

**Task 2:** List the databases and confirm that sql_practice exists.

**Task 3:** Connect to sql_practice using psql.

**Task 4:** Display the current database and current user.

**Task 5:** Inspect the encoding and locale metadata for sql_practice.

**Task 6:** Connect to a different database and remove sql_practice.

**Task 7:** Explain why IF EXISTS does not guarantee that DROP DATABASE will succeed.

Reference solution

sql

1
2
-- Task 1
CREATE DATABASE sql_practice;

Run the CREATE DATABASE statement as a standalone command with sufficient privileges. The remaining tasks are completed using the commands and queries in the following section.

15. Guided Practice: Solutions

The following examples are reference answers. Database names, roles, and output values depend on your environment.

Verify and remove the practice database

sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Task 2
SELECT datname
FROM pg_database
WHERE datname = 'sql_practice';
 
-- Task 4
SELECT current_database(), current_user;
 
-- Task 5
SELECT
    datname,
    pg_encoding_to_char(encoding) AS encoding,
    datcollate,
    datctype
FROM pg_database
WHERE datname = 'sql_practice';
 
-- Task 6:
-- First switch to another database in psql:
-- \c postgres
 
-- Then execute this as a standalone statement:
DROP DATABASE IF EXISTS sql_practice;

Task 3 uses the psql meta-command \c sql_practice. Task 6 requires a connection to a different database before the DROP statement. Task 7: IF EXISTS only handles an absent target; active connections, permissions, and other restrictions can still prevent deletion.

16. Interview Questions

**Q1. What is DDL?**

DDL stands for Data Definition Language. It includes commands that define or change database structures, such as CREATE, ALTER, and DROP.

**Q2. What is the difference between a database and a table?**

A database contains schemas and other database objects. A table stores structured records within a schema.

**Q3. Why is UTF8 commonly used?**

It supports a broad range of characters and languages, making it suitable for multilingual applications.

**Q4. What is collation?**

Collation defines rules used to compare and sort text values.

**Q5. Why might CREATE DATABASE fail?**

Possible causes include insufficient privileges, an existing database with the same name, incompatible locale or encoding settings, or attempting to run the command inside a transaction block.

**Q6. Why must you connect to a different database before dropping one?**

PostgreSQL cannot drop the database that the current session is using.

**Q7. What is the purpose of IF EXISTS?**

It avoids an error when the specified database does not exist. It does not prevent data loss or override other restrictions.

**Q8. What does DROP DATABASE ... WITH (FORCE) do?**

It attempts to terminate eligible connections and drop the database. Certain conditions and permission limitations can still cause the operation to fail.

17. Lesson Summary

- DDL defines and changes database structures.
- CREATE DATABASE creates a new PostgreSQL database.
- Clear lowercase database names are easy to read and use consistently.
- UTF8 is a character encoding; locale and collation affect text classification and ordering.
- Database encoding and locale settings must be compatible with the chosen template and server configuration.
- pg_database exposes metadata about PostgreSQL databases.
- The psql command \c switches to another database.
- DROP DATABASE permanently removes the target database.
- You must connect to a different database before dropping the target.
- IF EXISTS handles a missing database but does not guarantee successful or reversible deletion.
- FORCE can attempt to terminate eligible connections, but it has limitations.

You are now ready to create and manage practice databases before learning how to create schemas and tables.

Common pitfalls to avoid

Real debugging lessons and exact syntax contrasts to avoid runtime bugs

#1 Assuming CREATE DATABASE works inside a transaction block

Why it happens: Many SQL statements can be included in a transaction, so beginners may assume database creation behaves the same way.

Incorrect Approach

sql

BEGIN; CREATE DATABASE sql_practice; COMMIT;

Correct Solution

sql

CREATE DATABASE sql_practice;

Key takeaway: PostgreSQL does not allow CREATE DATABASE inside a transaction block. Execute it as a standalone command.

#2 Assuming a particular locale exists on every operating system

Why it happens: Tutorials often copy locale names from one machine without explaining platform differences.

Incorrect Approach

sql

Assuming LC_COLLATE = 'en_US.UTF-8' must work on every PostgreSQL server.

Correct Solution

sql

Use compatible settings available to the server, or rely on the appropriate template defaults.

Key takeaway: Locale availability and naming depend on the server environment and locale provider.

#3 Confusing encoding with collation

Why it happens: Both settings affect text, but they solve different problems.

Incorrect Approach

sql

Assuming UTF8 alone determines alphabetical sorting rules.

Correct Solution

sql

Use encoding to represent characters and collation settings to control text comparison and sorting.

Key takeaway: Encoding and collation are distinct database settings and must be considered separately.

#4 Trying to drop the currently connected database

Why it happens: The user can see and query the database, so they may expect to delete it from the same session.

Incorrect Approach

sql

Connecting to sql_practice and then running DROP DATABASE sql_practice;

Correct Solution

sql

Connect to another database, such as postgres, and then drop sql_practice.

Key takeaway: PostgreSQL does not permit a session to drop its own current database.

#5 Treating IF EXISTS as a complete safety mechanism

Why it happens: The word IF can make the statement appear safer than it actually is.

Incorrect Approach

sql

Assuming DROP DATABASE IF EXISTS production_db is safe to run.

Correct Solution

sql

Verify the target database, confirm the recovery plan, and only then issue a destructive command.

Key takeaway: IF EXISTS handles only the case where the target database is absent. A successful DROP still permanently removes the database.

#6 Assuming active connections can always be terminated

Why it happens: The administrator may expect every session to be removable by a single command.

Incorrect Approach

sql

Assuming a connection-termination query always makes DROP DATABASE succeed.

Correct Solution

sql

Inspect connections and permissions; use FORCE only when appropriate and understand its limitations.

Key takeaway: Permissions and database objects such as prepared transactions or logical replication objects can prevent a forced drop.

Ready for hands-on challenges?

Test your skills with shuffled coding challenges in the Practice Arena.

Practice Arena →

Key takeaways

CREATE DATABASE creates a new database in PostgreSQL.
Database names should be clear and consistent; lowercase snake_case is a useful convention.
UTF8 supports a broad range of international characters, but encoding and locale settings must be compatible.
PostgreSQL normally creates a database from template1 unless another template is specified.
PostgreSQL database metadata can be inspected through pg_database.
The psql command \c switches the current connection to another database.
DROP DATABASE permanently removes a database and its stored contents.
You cannot drop the database to which your current session is connected.
IF EXISTS avoids an error when the target database is absent; it does not make deletion reversible.

Was this lesson helpful?

Found an error? Report an issue