How to Use Database in PostgreSQL
- Available Databases in PostgreSQL
- Connect to a Database in PostgreSQL
- Create a New Database in PostgreSQL
- Create a Table in a Connected Database in PostgreSQL
This article demonstrates connecting to a database, creating a new database, and creating a table in PostgreSQL.
Available Databases in PostgreSQL
You can perform the following command after opening the Postgres command line to see all the available databases present and connected.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
Here, you can see 3 rows, meaning we have three databases in Postgres. You need to understand that the database doesn’t mean the tables.
A database can have multiple tables inside it. Also, the table might be relational to other tables.
Let’s see how you can connect to a database.
When you install Postgres for the first time, you’ll find these three databases created for you by default. And if you don’t connect any database, by default, all the tables you’ll make later on will go to the database named Postgres.
Connect to a Database in PostgreSQL
You need to open the psql shell or open psql from the terminal to connect to a database. Then using the credentials, log in to Postgres; after that, use the following command.
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
So, the terminal says you’re now connected to a database named Postgres. Let’s see what tables we have inside this database.
To see the list of tables available in a database, we need to write the command \dt <database_name>. For example:
postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | accounts | table | postgres
(1 row)
Create a New Database in PostgreSQL
Let’s say you need a database of your own, and you’ll manage some tables there. The basic syntax of creating a database is CREATE DATABASE <database_name>.
After creating it, let’s create a database named titan and see the list of available databases.
postgres=# CREATE DATABASE TITAN;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+----------------------------+----------------------------+-----------------------
postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres +
| | | | | postgres=CTc/postgres
titan | postgres | UTF8 | English_United States.1252 | English_United States.1252 |
(4 rows)
postgres=# \c titan
You are now connected to database "titan" as user "postgres".
Also, there’s another method to create the database. The format is createdb [options...] [database_name [description of database]].
Here’s a list of available options in PostgreSQL.
| Options | Descriptions |
|---|---|
| -D | Default tablespace for the database |
| -h | hostname of the machine where the server is sunning |
| -e | Echo the commands that createdb generates and sends to the server |
| -E | Specifies the character encoding scheme to be used in this database |
Here’s the full list of options from the PostgreSQL official documentation.
Create a Table in a Connected Database in PostgreSQL
As we’ve connected to the database named titan, let’s see if any table exists or not.
postgres=# \c titan
You are now connected to database "titan" as user "postgres".
titan=# \dt
Did not find any relations.
As you can see, there is no table in the database. Also, if you notice, here are some minor changes.
When we’ve connected to the titan database, the line starts with titan=#, meaning the console runs on the titan database.
Let’s create a table here, like the following.
CREATE TABLE Colossal (
titan_id serial PRIMARY KEY,
titan_name VARCHAR ( 50 ) NOT NULL,
strength_level INT NOT NULL
);
CREATE TABLE
titan=# \dt
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | colossal | table | postgres
(1 row)
titan=#
Now, you can perform the CRUD operation in the table. We can see that the colossal table is now available in the titan database.
For more about database creating and setup, please follow the official documentation.
