Creating databases and connecting in PostgreSQL - Visual Walkthrough

Concept Flow - Creating databases and connecting

Start psql shell

↓

Run CREATE DATABASE command

↓

Database created

↓

Connect to new database with \c

↓

Connected to new database

↓

Ready to run queries

This flow shows how to create a new database and then connect to it using PostgreSQL commands.

Execution Sample

PostgreSQL

CREATE DATABASE mydb;
\c mydb

Creates a new database named 'mydb' and then connects to it.

Execution Table

Step	Command	Action	Result	Notes
1	CREATE DATABASE mydb;	Create new database named mydb	Database 'mydb' created	Success message shown
2	\c mydb	Connect to database mydb	You are now connected to database "mydb".	Prompt changes to mydb=#
3	N/A	Ready to run queries	Prompt ready	User can now run queries on mydb

💡 Database created and connection established; ready for queries.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Current Database	postgres (default)	postgres (still)	mydb	mydb

Key Moments - 2 Insights

Why do we need to connect to the new database after creating it?

Creating a database only makes it available. To run queries inside it, you must connect to it, as shown in step 2 of the execution_table.

What happens if you try to create a database with a name that already exists?

PostgreSQL will return an error and not create a new database. This is not shown here but is important to know.

Visual Quiz - 3 Questions

Look at the execution_table, what is the current database after step 2?

A postgres

B mydb

C template1

D default

💡 Hint

Check the 'Current Database' row in variable_tracker after step 2.

At which step does the prompt change to show the new database name?

A Step 2

B Step 3

C Step 1

D Never

💡 Hint

Look at the 'Result' and 'Notes' columns in execution_table for step 2.

If you skip the \c command after creating the database, what happens?

A The database is deleted

B You are connected to the new database automatically

C You remain connected to the old database

D You get an error

💡 Hint

Refer to the 'Current Database' variable in variable_tracker and the explanation in key_moments.

Concept Snapshot

CREATE DATABASE dbname;  -- creates a new database named dbname
\c dbname               -- connects to the database named dbname
You must connect to run queries inside the new database.
If the database exists, creation fails.
Prompt changes to show current database after connection.

Full Transcript

This visual execution shows how to create a new PostgreSQL database and connect to it. First, the CREATE DATABASE command makes a new database available. Then, the \c command connects you to that database. The prompt changes to show the new database name, indicating you are connected and ready to run queries. Variables track the current database name before and after each step. Key moments clarify why connection is needed after creation and what happens if you skip it. The quiz tests understanding of the connection step and prompt change.

Practice

1. What does the CREATE DATABASE command do in PostgreSQL?

easy

A. It deletes a database permanently.

B. It connects to an existing database.

C. It creates a new database to store data.

D. It lists all databases available.

Solution

1. Step 1: Understand the purpose of CREATE DATABASE

   This command is used to create a new database in PostgreSQL where data can be stored.

2. Step 2: Differentiate from other commands

   Connecting to a database uses \c, deleting uses DROP DATABASE, and listing uses \l.

3. Final Answer:

   It creates a new database to store data. -> Option C

4. Quick Check:

   CREATE DATABASE = create new database [OK]

Hint: Remember: CREATE DATABASE makes a new database [OK]

Common Mistakes:

• Confusing CREATE DATABASE with connecting commands
• Thinking it deletes or lists databases
• Using wrong command for creating database

2. Which of the following is the correct syntax to connect to a database named shopdb in PostgreSQL?

easy

A. \c shopdb

B. \\c shopdb

C. CONNECT TO shopdb;

D. USE shopdb;

Solution

1. Step 1: Identify the correct psql command for connecting

   In PostgreSQL's psql tool, the command to connect to a database is \c database_name.

2. Step 2: Check syntax correctness

   \c shopdb uses the correct backslash and no extra backslashes or SQL keywords.

3. Final Answer:

   \c shopdb -> Option A

4. Quick Check:

   Connect command = \c [OK]

Hint: Use single backslash \c to connect in psql [OK]

Common Mistakes:

• Using double backslashes instead of one
• Using SQL commands like USE which is not PostgreSQL
• Adding semicolons to psql meta-commands

3. What will be the result of running the following commands in psql?

CREATE DATABASE testdb;
\c testdb
SELECT current_database();

medium

A. An error because \c cannot be used after CREATE DATABASE

B. It connects to testdb and shows 'testdb' as current database

C. It shows the default database name instead of testdb

D. It creates testdb but does not connect, so current_database() shows nothing

Solution

1. Step 1: Understand command sequence

   The first command creates a new database named testdb. The second command connects to it. The third command queries the current database name.

2. Step 2: Predict output of current_database()

   Since connection switched to testdb, current_database() returns 'testdb'.

3. Final Answer:

   It connects to testdb and shows 'testdb' as current database -> Option B

4. Quick Check:

   CREATE + \c + current_database() = 'testdb' [OK]

Hint: After CREATE DATABASE, use \c to switch and check with current_database() [OK]

Common Mistakes:

• Thinking \c cannot be used after CREATE DATABASE
• Assuming current_database() shows default database
• Believing connection does not change automatically

4. You run the command CREATE DATABASE mydb but get an error. Which of these is the most likely cause?

medium

A. You do not have permission to create a database.

B. You forgot the semicolon at the end of the command.

C. You are already connected to the database you want to create.

D. You used the wrong command; CREATE DATABASE is not valid in PostgreSQL.

Solution

1. Step 1: Check command syntax

   The command CREATE DATABASE mydb is valid and semicolon is optional in some clients but recommended.

2. Step 2: Identify common permission issues

   Most errors creating databases happen because the user lacks the CREATE DATABASE privilege.

3. Final Answer:

   You do not have permission to create a database. -> Option A

4. Quick Check:

   Permission error = CREATE DATABASE fails [OK]

Hint: Check user permissions if CREATE DATABASE fails [OK]

Common Mistakes:

• Assuming missing semicolon always causes error
• Thinking you cannot create database while connected to it
• Believing CREATE DATABASE is invalid in PostgreSQL

5. You want to create a new database named salesdb and immediately connect to it using psql commands. Which sequence is correct?

hard

A. CREATE DATABASE salesdb; CONNECT salesdb;

B. \c salesdb; CREATE DATABASE salesdb;

C. CREATE DATABASE salesdb \c salesdb;

D. CREATE DATABASE salesdb; \c salesdb

Solution

1. Step 1: Understand command order

   You must first create the database, then connect to it. So CREATE DATABASE salesdb; comes before \c salesdb.

2. Step 2: Verify command syntax

   CREATE DATABASE salesdb; \c salesdb uses correct SQL and psql meta-command syntax. \c salesdb; CREATE DATABASE salesdb; tries to connect before creation. CREATE DATABASE salesdb \c salesdb; combines commands incorrectly. CREATE DATABASE salesdb; CONNECT salesdb; uses invalid CONNECT command.

3. Final Answer:

   CREATE DATABASE salesdb; \c salesdb -> Option D

4. Quick Check:

   Create then connect = correct sequence [OK]

Hint: Create first, then connect with \c [OK]

Common Mistakes:

• Trying to connect before database exists
• Using invalid commands like CONNECT
• Combining commands on one line incorrectly