How to Create a Table in PostgreSQL & Perform CRUD Operations

Let's learn to create tables in PostgreSQL and how to perform basic operations, such as insert or create, read, update, and delete, on tables. In databases, a table is like a container that stores the data in the forms of columns and rows, much like excel or google sheets.

How to Create a Table in PostgreSQL?

To create a table in PostgreSQL, you have to use a specific syntax consisting of specific keywords responsible for table creation. Let's jump directly into it and create a table so that you can understand:

CREATE TABLE workers(
id SERIAL PRIMARY KEY,
first_name VARCHAR(40),
email VARCHAR(255),
age INT
);

Now, let’s discuss the table creation syntax step by step:

CREATE TABLE workers: CREATE TABLE is the keyword which creates the table and workers is the table name. Table name could be of your choice.
Note: In SQL, every uppercase is a syntax keyword, lowercase is not the keyword and its name can be the choice of programmer.
id SERIAL PRIMARY KEY: id is the column name, SERIAL is a special type which increments integer value with +1 each time a new data entry takes place, PRIMARY KEY makes id column a unique identifier which is used to reference to specific rows.
first_name VARCHAR(40): first_name is the column name in the table and VARCHAR is the data type which simply is text and 40 is the character limit. First name is not going to be very big so it’s a good idea to optimize with the character limit.
- email VARCHAR(255): email is the column name, VARCHAR is the datatype which is text and emails could be long so 255 is the character limit.
- age INT: age is column name and INT is simply the integer.
- () are used to enclose the column names and datatypes.
- ; is used to end the statement in SQL, which means the query ends here.

As other programming languages have their conventions, SQL also has its own conventions. The convention is to write all the keywords in uppercase and everything relating to table such as column name in lowercase. However, SQL doesn’t care about uppercase or lowercase. For example, the keywords CREATE TABLE can also be written as create table and it would still work but we write code for people and other programmers are used to these conventions which make the code readable.

Let’s run the query in psql shell and create a table:

To verify if the table has been created, write in psql shell:

\dt

CRUD Operations (CREATE, READ, UPDATE, and DELETE)

Let’s practically perform CRUD operations on the table we just created.

1- CREATE OR INSERT OPERATION

In order to insert data into the table we are going to write following query:

  INSERT INTO workers (first_name, email, age) VALUES ('Robert', 'bob@commandprompt.com', 26);

See the result:

In the first parenthesis, followed by the table name, we entered column names, and after the keyword VALUES, we entered the values we wanted to insert into the table.

Note: Single colons ‘’ and double colons “” do not mean the same thing in SQL.

2- READ Operation

Let’s see the data that we inserted previously in the table. To read, simply write the following query:

SELECT * FROM workers;

In the above query, * refers to column. You can use column names to fetch data against a single column or even a combination of multiple columns:

SELECT email FROM workers;

In the above query, * refers to column. You can use column names to fetch data against a single column or even a combination of multiple columns:

SELECT email FROM workers;

You can also refer to row after adding a WHERE keyword. You can pick up a single or multiple rows using WHERE and a condition. Let’s see how you can do that:

SELECT * FROM workers WHERE age = 26;

It would simply pick up the row/s having age 26.

3- Update Operation

Let’s say we want to update a specific name in our table. In that case we would write:

UPDATE workers SET first_name = 'BOB' WHERE id = 1;

Here we can see that first_name column has been updated successfully.

4- DELETE OPERATION

I have added an extra record in the table and now the table looks like this:

To delete the column, I’d write the following query:

DELETE FROM workers WHERE id = 2;

Here, you can see the 2nd record or row has been deleted successfully.

How to Drop a Table in PostgreSQL?

We learned how to create a table and perform CRUD operations in the table. Now, let’s learn about removing a table from database.

DROP TABLE workers;

By running the above query, table is now dropped. Afterwards, running \dt would ensure this as you can see above.

Conclusion

To create tables in PostgreSQL, you have to use a specific syntax with specific keywords along with the column names and datatypes. You can perform CRUD operations on the table by writing similar queries discussed in the article. In PostgreSQL, similar to other programming languages, you must follow conventions while writing queries, so it's easy for other programmers to understand your work. If you want to get rid of any table in your database, you can use the DROP TABLE keyword.

How to Create a Table in PostgreSQL & Perform CRUD Operations