JIYIK CN >

This article demonstrates connecting to a database, creating a new database, and creating a table in PostgreSQL.

Available databases in PostgreSQL

You can run the following command after opening the Postgres command line to view all available databases that exist and are 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 行that this means we have three databases in Postgres. You need to understand that database does not mean table.

There can be multiple tables in a database. In addition, the table may be related to other tables.

Let's see how to connect to the database.

When you first install Postgres, you will find these three databases created for you by default. If you do not connect to any database, by default all the tables you create later will go to Postgresthe database named .

Connecting to a database in PostgreSQL

You need to open psqla shell or open from the terminal psqlto connect to the 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 that you are now connected to a Postgresdatabase called . Let's see what tables we have in this database.

To see the list of tables available in the 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

Suppose you need a database of your own and you will manage some tables there. The basic syntax to create a database is CREATE DATABASE <database_name>.

Once created, let's create a titandatabase called and view 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".

In addition, there is another way to create a database. The format is createdb [options...] [database_name [description of database]].

Here is a list of the options available in [PostgreSQL](https://postgrespro.com/docs/postgresql/9.6/app-createdb).

Here is the complete list of options from the official PostgreSQL documentation.

Create a table in the PostgreSQL connection database

While we are connected to titana database named , let's see if any tables exist.

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 are no tables in the database. Also, if you notice, there are some minor changes here.

When we are connected to titana database, the line titan=#starts with , which means that the console titanis running on the database.

Let us create a table here as shown below.

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 operations on the table CRUD. We can see colossalthat the table is now available in titanthe database.

For more information on database creation and setup, follow the official documentation .