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. Similarly, just as the template1 database in PostgreSQL serves as a template for creating new databases, the model database in SQL Server operates in much the same way. In this article, you will learn how easy it is to create, manipulate tables, and retrieve data 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. Additionally, PostgreSQL offers robust backup and restore capabilities, ensuring data integrity and recovery in case of data loss.

A powerful command-line tool called ‘psql’ enables users to interact with a PostgreSQL database through raw SQL queries and specific meta-commands. These commands can access various system tables to retrieve important information while performing routine database tasks.

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, and the column definitions limit 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. Columns can also be defined to allow or disallow NULL values. Additionally, defining a table constraint, such as primary keys, foreign keys, and unique constraints, is crucial for maintaining the structure and integrity of the table. Clauses like the WHERE clause and CHECK clause are used to define conditions and constraints.

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. The referenced columns in foreign key constraints should be part of a unique or primary key constraint.

Database Connection and Setup

To start working with PostgreSQL, establishing a connection to the database is the first crucial step. You can connect to PostgreSQL using the command line or a graphical user interface (GUI) like Azure Data Studio (ADS). Once connected, you can execute SQL commands to create a database and tables.

To create a new database, use the ``` CREATE DATABASE

command:

CREATE DATABASE my_database;

Replace ```
my_database

with your desired database name. This command initializes a new database where you can store your data.

statement. This involves defining the table structure, including column definitions, data types, and constraints to ensure data consistency and integrity. Here’s an example:

CREATE TABLE my_table ( id SERIAL PRIMARY KEY, name VARCHAR(100), age INT, email VARCHAR(255) UNIQUE );

In this example, ```
my_table

is created with columns ``` id

, ```name

, ``` age

, and ```email

, each with specified data types and constraints. The ``` PRIMARY KEY

 constraint ensures that each row has a unique identifier, while the ```UNIQUE

constraint ensures that email addresses are not duplicated.

By following these steps, you can set up a robust database structure that supports efficient data management and retrieval.

Methods to Create and Manipulate Tables in Postgres

There are different ways of dealing with tables in Postgres. SQL commands allow you to perform various operations such as creating tables, inserting data, and defining constraints. The DROP DATABASE command is used to remove a database and must not have any active connections when executed.

Alternatively, you can use the command line to interact with PostgreSQL, providing flexibility for users who prefer this method over a graphical user interface (GUI). Listing commands like '\d' for tables and '\list' for databases facilitate the retrieval and presentation of structured data.

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, allowing you to query and manipulate data efficiently. For a moment, let's simplify the topic before we expand on more complex operations. These commands are evaluated by PostgreSQL to ensure they are correctly processed and executed. This approach of creating tables is very straightforward in application. Here’s a detailed guide:

CREATE TABLE example_table (    id SERIAL PRIMARY KEY,    data TEXT);

The result of executing the CREATE TABLE command is the creation of a new table named ‘example_table’ with columns ‘id’ and ‘data’.

Prerequisites

• PostgreSQL.
• Windows Powershell.

Users can open the Command Prompt on Windows or Terminal on Unix-like systems to execute SQL commands.

Step 1: Create a New Database in Postgres

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

CREATE DATABASE store;

Replace the store with your desired database name for Postgres.

It is important to ensure there are no existing connections to the database when creating a new one.

PostgreSQL comes with a default database named ‘postgres’, which can be used for initial connections and managing multiple databases.

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. Constraints are used to enforce rules on the data in the table, such as ensuring unique values or maintaining relationships between tables.

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. Foreign key constraints can also be added to ensure that the values in one table correspond to values in another, maintaining referential integrity within the database schema.

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, you can insert data one row at a time using the INSERT command. 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;

After updating, it is crucial to verify the data integrity to ensure that the updates are correctly applied and no data is misplaced.

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;

Proper handling of the DELETE command is essential to avoid errors, such as attempting to delete rows that do not exist, which could compromise database integrity.

Table Components (Structure, Data Types, Constraints)

When creating a table in PostgreSQL, defining its structure is essential. This includes specifying the data types for each column and setting constraints to maintain data integrity. PostgreSQL supports a variety of data types, such as ``` INTEGER

, ```VARCHAR

, and ``` DATE

, allowing you to store different kinds of data.‍For example:

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), birth_date DATE, email VARCHAR(100) UNIQUE, department_id INT, CONSTRAINT fk_department FOREIGN KEY(department_id) REFERENCES departments(department_id) );

In this table, ```employee_id

is defined as a ``` SERIAL

 data type with a ```
PRIMARY KEY

constraint, ensuring each employee has a unique identifier. The ``` email

 column is defined as ```VARCHAR(100)

with a ``` UNIQUE

 constraint to prevent duplicate email addresses. The ```
FOREIGN KEY

constraint on ``` department_id

ensures that each value corresponds to a valid ```department_id

in the ``` departments

 table, maintaining referential integrity.

By carefully defining the structure, data types, and constraints, you can create tables that are both efficient and reliable.

Indexes and Performance

Indexes are crucial for enhancing the performance of your PostgreSQL queries. They allow the database to quickly locate specific data, significantly speeding up data retrieval operations. You can create indexes on one or more columns of a table using the ``` CREATE INDEX

 statement.‍ For example:

CREATE INDEX idx_employee_email ON employees(email);

This command creates an index on the ```email

column of the ``` employees

 table, which can speed up queries that search for employees by email.‍To analyze the performance of your queries, you can use the ```EXPLAIN

command. This command provides insights into the execution plan of a query, helping you identify potential performance bottlenecks:

EXPLAIN SELECT * FROM employees WHERE email = 'john.doe@example.com';

The output of the ``` EXPLAIN

 command will show how PostgreSQL plans to execute the query, allowing you to make informed decisions about indexing and query optimization.

By leveraging indexes and analyzing query performance, you can ensure that your PostgreSQL database operates efficiently, even as the volume of data grows.

Using GUI Tool to Create And Manipulate Tables in Postgres

In this tutorial, you will learn to create and manipulate Postgres tables using pgAdmin, a GUI tool that allows you to display and manage table structures easily. Below is a step-by-step process:

This section can be part of a larger course on PostgreSQL.

Prerequisites

• pgAdmin.

It is important to select the current database in the GUI tool before performing any operations.

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, Primary key, and identity if you need a unique identifier for the column.
• You can also set the collation for text data to define how sorting and comparison will be handled.
• 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 to see the results and start inserting new data into the table.
• 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 to see the output of the inserted rows.

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. The updated data is stored in the database for future retrieval.

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.

Best Practices for Table Management

Effective table management is key to maintaining a robust and scalable PostgreSQL database. Here are some best practices to follow:

1. Regular Backups: Regularly back up your database to prevent data loss. Use tools like ``` pg_dump

 for backups and ```pg_restore

for restoring data. 2. Data Integrity Verification: Regularly verify the integrity of your data to ensure it remains accurate and consistent. Use constraints and triggers to enforce data integrity rules. 3. Optimizing Queries: Optimize your SQL queries for better performance. Use indexes to speed up data retrieval and the ``` EXPLAIN

 command to analyze and improve query execution plans.
4. **Clear Schema Definition**: Define a clear schema for your database, including the relationships between tables. This ensures data consistency and makes it easier to scale your database as needed.
5. **Monitoring and Analysis**: Use tools like Azure Data Studio (ADS) to monitor and analyze your database performance. Identify bottlenecks and optimize your queries to ensure efficient operation.

By following these best practices, you can manage your PostgreSQL tables effectively, ensuring data integrity, performance, and scalability.

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. Sequences are used in data replication to ensure that auto-incrementing values are correctly managed across different tables.

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, ensuring data is stored efficiently in the database.

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. Each chapter of this article has guided you through different aspects of Postgres, from basic setup to advanced table manipulation. You can seamlessly create and manipulate tables in the Postgres database using the information mentioned above. These concepts will be familiar to those with experience in other RDBMS systems, ensuring a smooth transition.

💡Related Reads

• MSSQL Vs. MySQL
• Redshift Vs. Postgres
• PostgreSQL vs MySQL

‍