How to Create and Manipulate PostgreSQL Tables?

Almost every data-driven organization uses relational database management systems (RDBMS) in one way or another. These are the best tools to store and manage data in a structured format. One of the widely popular RDBMS is PostgreSQL. However, most beginners assume storing data in this database is challenging by creating tables and querying in SQL. But that’s not the case. In this article, you will learn how easy it is to create and manipulate tables in PostgreSQL. 

PostgreSQL Overview

PostgreSQL or Postgres is an enterprise-grade open-source RDBMS. The database was developed in 1986 at the University of California, Berkeley. Since then, Postgres has been recognized for its scalability, reliability, and compliance measures.

Postgres has a robust architecture that accommodates JSON (non-relational) and SQL (relational) queries. You can also use a spectrum of SQL functions like sub-queries, triggers, foreign keys, and user-defined functions. All these capabilities help your business develop scalable analytical applications. 

Being an open-source platform, Postgres remains free from private or corporate control and supports various operating systems. This integration eliminates the need for multiple licenses, thus reducing costs and making it an ideal DBMS for organizations. 

Key features of PostgreSQL:

• ACID Compliance: ACID or Atomicity, Consistency, Isolation, and Durability is a four-layer framework in Postgres that ensures transactions in the database are reliable. This makes it an ideal choice for applications that demand robust data integrity, such as financial systems. 
• ‍Rich Data Types: Besides basic numeric, text, and date data types, PostgreSQL supports various advanced data types. These include arrays, hstore (for key-value pairs), PostGIS for geographic objects, and JSON/BJSON (for unstructured data). 
• ‍Vibrant Community: The development of Postgres is driven by a dedicated community of contributors and professionals. This helps in making the tool a lot better through regular updates, bug fixes, and reporting. 

What are Postgres Tables?

As mentioned above, relational databases have the structure of rows and columns. A table in Postgres is a database object that stores and handles data in a structured format that includes rows and columns. The number of columns is fixed in every table of Postgres, while the rows can be unlimited. 

In these tables, each column has a fixed data type, which limits the set of possible values that can be assigned in the column. Supported data types in Postgres tables include boolean, character, and numeric. However, it also gives you the flexibility to define your data type. 

You need to use DDL and DML commands to deal with tables in Postgres manually. DDL, or data definition language, consists of SQL commands that you can use to define database structure or schema. These commands include CREATE, DROP, ALTER, and RENAME statements. On the other hand, DML, or data manipulation language, is an SQL command that deals with manipulating data in Postgres. It includes INSERT, UPDATE, and DELETE statements. 

Methods to Create and Manipulate Tables in Postgres

There are different ways of dealing with tables in Postgres. Below are two of the common ways

• Using SQL Commands to Create And Manipulate Postgres Tables. 
• Using GUI Tool to Create And Manipulate Tables in Postgres. 

Using SQL Commands to Create And Manipulate Postgres Tables

SQL commands are the most common method for creating and managing tables in Postgres. This approach of creating tables is very straightforward in application. Here’s a detailed guide:

Prerequisites

• PostgreSQL. 
• Windows Powershell.

Step 1: Create a New Database in Postgres

The first step is to connect with the Postgres instance. After connecting, create a database using the command below: 

CREATE DATABASE store;

Replace the store with your desired database name for Postgres. 

Note: If you already have an existing database and you want to create a table in it, you can skip this step. 

Step 2: Create Table Within Database

After you have a database, you can begin defining tables to store your data. A simplified syntax for this task looks like the following:

CREATE TABLE your_table (column_name TYPE [column_constraint],[table_constraint,] );

In the above code, you create a table named your_table and define a column called column_name by defining the TYPE, which specifies the data type for the column.

Let’s take an example to explain table creation in Postgres in a better way: Say we want a table called order in store database from step 1 with the following fields: ID, Name, Product, Address, Quantity, Phone. The code for this table will look like the following: 

CREATE TABLE order ( id INT PRIMARY KEY, name VARCHAR, product VARCHAR, address VARCHAR, price INT,phone INT);

In the above code, the PRIMARY KEY is a special constraint that is used to indicate columns that uniquely identify records within a table. Further, you can see each column has specified data types.

Step 3: Manipulating Tables

There can be many ways to manipulate a table in Postgres. This can include inserting new columns, deleting existing columns, updating the existing column data, etc. Below are some of the common ways: 

Inserting Rows in Table

When the table is created, it has no data. In Postgres, data is inserted one row at a time. To create a new row, you have to use the INSERT command in the example table created in Step 2. Below is a working example: 

INSERT INTO order(1, ‘Andrew’, ‘shirt’, ‘Brooklyn, new york’, 15, 1234567890);

The above code will create a new row in the order table with the name of Andrew and other attributes, as mentioned above. 

Updating Table Data 

You can update individual rows, all the rows, and a subset of all rows in Postgres. For this, you can use the UPDATE command. 

Below is a command that updates the phone field in the order table you created in Step 2: 

UPDATE order SET phone =0987654321 WHERE phone=1234567890;

Deleting Table Data 

Just as adding data is possible in rows, you can remove data from the Postgres table only in entire rows. Here, you can utilize the DELETE command. 

For example, to delete all the rows from the table, simply write: 

DELETE FROM your_table;

However, you can also provide conditions to delete specific rows. Below is an example from the order table: 

DELETE FROM order WHERE phone=0987654321;

With this command, all the rows with the phone field 0987654321 will be deleted. 

Using GUI Tool to Create And Manipulate Tables in Postgres

In this approach, you will learn to deal with Postgres tables using one of the most common GUI tools for managing Postgres database, pgAdmin. Below is a step-by-step process: 

Prerequisites

• pgAdmin. 

Step 1: Create a Postgres Table

• Open the instance of pgAdmin in your local system.
• Select the database where you want to create the table in the object tree from the left navigation bar. 
• Click on Schemas > public > Create > Table. 
• A popup window will appear to create a table. Provide the table name in the  Name field. You can also select Owner, Schema, Tablespace, Partitioned table, and Comment according to your requirements. 
• To create a column, click on the Columns tab. 
• There, you'll see a + button on the right corner; click on that and provide column details such as Name, Data type, and Primary key. 
• After adding the required columns, click on the Save button. 
• You'll see the table you created in the object tree. 

Step 2: Manipulate Postgres Tables in pgAdmin

For this, let's consider similar examples taken above to manipulate tables. To ensure this works in pgAdmin, add a primary key to the table you created in step 1.

Insert Rows in Table

• To perform this task, simply right-click on your table > View/Edit Data > All Rows.
• At the end of the last row of data, simply click enter to start inserting new data into the table. 
• Click on the Save button from the top left menu. 

Update Table Data 

• To update table data, go to the same page by right-clicking on your table > View/Edit Data > All Rows.
• You can select any data you want and update the content from here. 
• Click on the Save icon.

Delete Table Data

Deleting table data is only a click task using pgAdmin. 

• To delete an existing column, go to the columns you created from Step 1, right-click on the one you want to delete, and click Save. 
• To delete a row, follow the update data steps, and instead of updating, click on the delete option. 
• Lastly, to remove an entire table. Simply right-click on the table name, then the Delete/Drop option. 

That's it. If you have carefully followed the above-mentioned steps, you now know how to deal with tables in Postgres. 

Now that you know the basics of Postgres, you might want to leverage the database to its full potential. That's where tools like Airbyte come into play.

Airbyte Integration With Postgres

Airbyte is a data integration tool that allows you to connect with various data sources and destinations of your choice. With data integration, the platform also provides features like Change Data Capture to keep track of changes in the Postgres database. 

Airbyte offers a certified connector for PostgreSQL with many modern features to leverage the database. These features include: 

• Multiple methods of keeping data fresh, including replication using the xmin system column and CDC. 
• Over 350+ pre-built connectors to automate the migration or loading of data to Postgres from any data source or storage system of your choice. 
• Reliable data replication at any table size with checkpointing and chunking of database reads. 

More than 40,000+ engineers use Airbyte to replicate data with the most comprehensive catalog of connectors. Join the robust community today, and sign up for Airbyte if you haven't already.  

Conclusion

Mastering Postgres allows you to leverage the full potential of your data assets by efficiently managing and utilizing data. By now, you should be convinced of how easy it is to use this RDBMS without having prior technical experience. You can seamlessly create and manipulate tables in the Postgres database using the information mentioned above.