How to Create and Show a Table in PostgreSQL

Table of Contents

What is PostgreSQL?

Commonly abbreviated as “Postgres”, PostgreSQL is a popular open-source relational database management system (RDBMS) known for its high performance, extensibility, and support for a myriad of advanced features including asynchronous replication, table inheritance, complex SQL queries, and many more.

Prerequisites

To follow along in this tutorial, ensure you have the following set of requirements:

PostgreSQL server installed on your Linux instance. Check out our comprehensive guide on how to install PostgreSQL on Ubuntu.
SSH access to the server with a sudo user configured.

How to show tables in Postgres: Various ways

The steps below demonstrate how to create a database, create a table in Postgres, insert records, and make queries to retrieve information from a table.

1. Create a database in the PostgreSQL server

With the PostgreSQL server already installed, the first step is to create a database.

PostgreSQL provides some default databases out of the box. To list them, run the command:

To create a database, use the syntax:

CREATE DATABASE database_name;

For example, to create a database called my_testdb:

CREATE DATABASE my_testdb;

create-database-in-postgresql

Verify that the database was created with the command:

list-of-all-databases-in-postgresql

2. Postgres: Show tables in a database

To create a table inside the database, connect to the database using the c command:

c my_testdb

connect-to-a-database-in-postgresql

Next, use the following syntax to create a table:


CREATE TABLE [IF NOT EXISTS] table_name (
   column_name1 datatype(length) column_constraint,
   column_name2 datatype(length) column_constraint,
   column_name3 datatype(length) column_constraint
);

For demonstration, here is an example to create a table employee_details:


CREATE TABLE employee_details (
   user_id serial PRIMARY KEY,
   first_name VARCHAR (50) NOT NULL,
   last_name VARCHAR (50) NOT NULL,
   email VARCHAR (255) UNIQUE NOT NULL,
   department VARCHAR (50) NOT NULL,
   birth_date DATE
);

create-table-in-postgresql

Display the created table with:

dt

list-created-table-in-postgresql

3. PostgreSQL: Create a table using the ‘LIKE’ option

Create a new table by inheriting the structure of an existing table using the LIKE clause:


CREATE TABLE new_table_name (LIKE old_table_name INCLUDING ALL);

For example:


CREATE TABLE customer_details (LIKE employee_details INCLUDING ALL);

Verify the table was created with:

dt

create-table-using-like-in-postgresql

4. PostgreSQL: Create a temporary table

To create a temporary table, use the TEMPORARY TABLE clause:


CREATE TEMPORARY TABLE temp_table_name(
   column_list
);

For example:


CREATE TEMPORARY TABLE IF NOT EXISTS myTemporaryTable(
    id serial PRIMARY KEY,
    first_name VARCHAR(50),    
    last_name VARCHAR(50)
);

create-temporary-table-in-postgresql

Note: The temporary table will be dropped after the session ends.

5. Insert data and view a table

Insert data into the employee_details table:


INSERT INTO employee_details VALUES (1001, 'Alice', 'Smith', '[email protected]', 'Developer', '1988-05-10');

insert-values-in-a-table-in-postgresql

Query the table to display all records:

SELECT * FROM employee_details;

display-table-details-in-postgresql

Conclusion

In this tutorial, we explored how to create and display tables in PostgreSQL, alongside other advanced techniques. For more information, check out PostgreSQL’s official documentation.

Looking for reliable cloud hosting? With Cherry Servers, enjoy dedicated bare metal, virtual servers, and anonymous cloud hosting with an SLA uptime guarantee of 99.97%.