How to Create Your First PostgreSQL Database Using the Terminal (psql Command Line)
In this article, you will learn how to create your very first postgreSQL database using psql, the built in command-line tool. If it's your first time, don't worry. I will walk you through each step so you get up and running with confidence.
just a quick reminer: In case you don't have postgress installed, then check out the PostgreSQL installation guide . If you allready have postgres installed, Then lets now see how to create our very first database
step 1: Open your terminal and start the postgres server using the command below
shellpsql -U postgres
enter password if asked and hit enter
If you don't yet have psql setup, or you are getting an error saying
diff- psql : The term 'psql' is not recognized as the name of a cmdlet, function, script file, or operable program.
please check out this post on How to setup psql. It will help you get rid of the error.
If you have everything set up correctly, then your postgreSQL server should be working as shown in the image below
Note: By default you will be in the postgress database as it is the default database you are sent to when you start the server without specifying any database name. This postgres database is commonly used as the default connection point.
Also, before creating a new database, you want to make sure you are in the Default postgres database or any exiting database if any. PostgreSQL won't let you start ```psql`` and immediately connect to a database that doen't exist.
Since you started the psql using the command psql -U postgres. Then you will definetely be directed to the default postgres database.
step 2: Creating a new database
Now that you are in the default postgress database. To create your own database you need to use the command
sqlCREATE DATABASE my_first_db;
This command will create a new database in the server called my_first_db.
If the command is successful, then you will see a message below the prompt saying
CREATE DATABASE. This means you have successfully create your first database.
step 3: Check if your databse is available
After creating your new database, the next step is to check if the database is actually present amongs the list of available database in the posgreSQL server. To do this , you use the \l command. This command lists out all the available databases currently on the server.
In the above image you now see that we have 4 databases my_first_db included alongside postgres, template0, template1.
Step 3: Connect to the new database (my_first_db)
To connect to the database you just created, you use the \c db_name command as shown below.
psql\c my_first_db;
This command will connect you to the database "my_first_db". For a successfull connect, you will get an output message saying
You are now connected to database "my_first_db" as user "postgres". and the prompt changes from postgres=# to my_first_db=#
Congratulations. you just create your first database. Want to move farward with table creation ??. check this post out How to create table in postgress
Frequently Asked Questions (FAQ)
Q: What is psql in PostgreSQL?
A: psql is PostgreSQL’s built-in command-line interface which allows you to interact with your database through the terminal. It lets you run SQL commands, view results, manage tables, and perform admin tasks, all from the terminal.
Q: When I try to create the database, I get a "permission denied" error. Why?
A: You may not have the right privileges. To create a new database, make sure you are connected as the default superuser postgres. Try running psql -U postgres to connect as the default superuser.
Q: Do I need to be in the default postgres database to create a new one?
A: No, you don’t have to be in the postgres database specifically to create a new database. But you must be connected to any existing database. PostgreSQL requires you to be connected to an existing database before you can create a new one. By default, psql -U postgres connects you to the postgres database, which makes it a common starting point.
Q: What does CREATE DATABASE do in PostgreSQL?
A: The CREATE DATABASE command sets up a new, empty database on your server. For example:
sqlCREATE DATABASE my_first_db;
This creates a database named my_first_db that you can later use to create tables and store data.
Q: How can I check if my new database was created?
A: Run the \l command. this will show the list of all databases on the PostgreSQL server.
shell\l
If your database was successfully created, it will appear in the list.
Q: How do I connect to a database after creating it?
A: Use the \c database_name command to connect to your database. For example:
shell\c my_first_db
If successful, you'll see a message which says:
You are now connected to database "my_first_db" as user "postgres".
This shows that you have connected to "my_first_db" database.
Q: I got an error saying 'psql' is not recognized as an internal or external command. What should I do?
A: This error means the psql tool is not installed or not added to your system's PATH. Please refer to the psql setup guide to install and configure it correctly.
Q: Can I create a database with a different user instead of postgres?
A: Yes, Yes, as long as that user has the right privileges.