In order to effectively work with data in PostgreSQL, it’s essential to have a solid grasp of the fundamental methods for querying data, displaying table data, linking data across multiple tables, modifying and deleting data within tables, and utilizing essential PostgreSQL functions.
To accomplish this, it’s important to familiarize yourself with the basics of SQL, including the SELECT statement for querying data and displaying tables, JOIN statements for linking data across tables, and UPDATE and DELETE statements for modifying and deleting data within tables.
Additionally, gaining knowledge of essential PostgreSQL functions can greatly enhance your data manipulation capabilities. By mastering these core skills, you’ll be well on your way to becoming proficient in PostgreSQL data management.
In this lesson, we will learn how to create tables, add constraints, and enable the hstore extension to enhance our database management skills in PostgreSQL.
PostgreSQL pg_dump Command
pg_dump is a utility program that is used to create a backup of a PostgreSQL database, which creates a text file containing SQL commands that can be used to recreate the database at a later time. The output of pg_dump includes the schema, data, and other database objects such as indexes, views, and triggers.
pg_dump tool is useful for creating backups, migrating data between servers, and copying a database to a development environment. It can be run from the command line or through a graphical user interface such as pgAdmin.
In the previous lesson, we learned how to list tables and their data using a psql command. In this lesson, we will explain the manual process of creating tables in the PostgreSQL database.
If you have downloaded the database from the previous lesson, you can see the instructions to create the tables by running the following command in your Psql shell:
pg_dump -st users geeksveda;
This command will display all the necessary instructions to create the mentioned table (users) in its current state. By following these instructions, you can manually create the tables from scratch.
Creates Tables in PostgreSQL Database
Let’s start by creating a new database named geeksveda2:
CREATE DATABASE geeksveda2;
Next, we connect to the geeksveda2 database using the command:
psql --dbname geeksveda2
We can create a new table in the database using the CREATE TABLE command, where we specify the table name, column names, and their data types, along with any additional attributes.
Here’s an example of creating a “courses” table:
CREATE TABLE courses( id integer NOT NULL , title character varying(255), price numeric, created_at timestamp with time zone, deleted_at timestamp with time zone, tags character varying(255)[] );
In this example, we created a table named “courses” with the following columns:
id– an integer type that cannot be null and is set as the primary key.title– a character-varying type with a maximum length of 255 characters.price– a numeric type that can store large numbers but is slower in calculations and requires more storage space than integers.created_at– a timestamp with a timezone type that stores both date and time data along with the time zone information.deleted_at– a timestamp with a timezone type similar to created_at.tags– a character varying array type.
To view information about the “courses” table, we can use the command \d:
\d courses
After creating the table, we set the id column as the primary key, which means its value will be unique and cannot be null.
Add Constraint to Table in PostgreSQL
A constraint in PostgreSQL is a rule or restriction that is applied to a table’s columns or rows to maintain data integrity and consistency. Constraints can be added to a table during its creation or added later using the ALTER TABLE command.
There are several types of constraints in PostgreSQL, such as:
- Primary Key – A primary key constraint ensures that a column or a group of columns in a table uniquely identifies each row. It prevents duplicate records and ensures data consistency.
- Foreign Key – A foreign key constraint establishes a relationship between two tables by referencing the primary key of another table. It ensures referential integrity and prevents the deletion of records that are referenced by other records.
- Unique – A unique constraint ensures that a column or a group of columns in a table contains unique values. It prevents duplicate records but does not necessarily enforce data consistency.
- Not Null – A not-null constraint ensures that a column in a table cannot contain null values. It ensures data consistency by preventing missing or incomplete data.
- Check – A check constraint ensures that a column’s value satisfies a specified condition. It can be used to enforce business rules or data validation rules.
Constraints are essential for maintaining data integrity and consistency in a database, and they play a vital role in ensuring that the data stored in a table is accurate and reliable.
In this section, we will learn about the ALTER TABLE command, which enables us to modify the definition of tables. We will use the ADD CONSTRAINT modification to add a primary key constraint to our “courses” table, as shown below:
ALTER TABLE ONLY public.courses ADD CONSTRAINT pkey_courses PRIMARY KEY (id);
In this example, we added a constraint named “pkey_courses” to the “courses” table, which makes the “id” column a primary key.
We can use the ALTER statement to modify other table properties, such as changing its name, renaming a column, adding or deleting columns, and more.
Now that we have learned how to create tables and modify their properties, let’s create a new table named “purchase_items” with the following command:
CREATE TABLE purchase_items ( id integer NOT NULL, purchase_id integer, product_id integer, price numeric, quantity integer, state character varying(255) );
In this example, we created a table named “purchase_items” with columns for “id“, “purchase_id“, “product_id“, “price“, “quantity“, and “state“. The “id” column is set as not null, indicating that it cannot be empty.
With these instructions, we now have the knowledge to create and modify tables in our PostgreSQL database.
Add Hstore Extension in PostgreSQL
Let’s create a new table called users with the following instructions:
CREATE TABLE users (
id integer NOT NULL PRIMARY KEY,
email character varying(255),
password character varying(255),
details hstore,
created_at timestamp with time zone,
deleted_at timestamp with time zone
);
However, when we try to execute this command, an error will occur, stating that the hstore data type does not exist in PostgreSQL. This is a unique data type that we’ll explore further in the following lessons.
ERROR: type "hstore" does not exist
To make the hstore data type available, we need to add it as an extension. To check whether hstore is installed, we can use the command:
\dx hstore
As we can see, hstore is not currently installed. To add it, we can use the following command:
CREATE EXTENSION hstore;
After running this command, we can use \dx hstore again to confirm that the extension has been installed:
\dx hstore
Now that hstore is installed, we can create our users‘ table with the details column using the hstore data type.
To verify that our tables have been created successfully, we can list them using the command:
\dt
As we can see, our users‘ table has been created successfully.
Conclusion
In conclusion, to work effectively with data in PostgreSQL, one should have a strong foundation in the fundamental methods for querying data, displaying tables, linking data across multiple tables, modifying and deleting data within tables, and utilizing essential PostgreSQL functions.
This requires familiarity with SQL and various PostgreSQL statements such as SELECT, JOIN, UPDATE, and DELETE. Additionally, understanding essential PostgreSQL functions can greatly enhance data manipulation capabilities.
In this lesson, we learned how to create tables, add constraints, and enable the hstore extension to enhance our database management skills. We also explained the manual process of creating tables and modifying their properties using the ALTER TABLE command. By mastering these skills, one can become proficient in PostgreSQL data management.
