Introductory to Tables
A table in PostgreSQL is a database object that can organize and store data in a structured format: in rows and columns making it easier to track and manage the data you are inputting. PostgreSQL tables allow information to flow and be quickly accessed and retrieved. in this guide, you will learn how to create tables in PostgreSQL in different ways, giving you a lot of options for managing your database.
SQL Command Line
For starters, a table can be created simply from the command line in SQL Shell by just simply running:
and to see all the tables and if your table has been successfully created, run:
with this command, you will see all the tables you have created.
Another option in the command allows you to create an empty table based on another table’s traits, from its column attributes to its indexes defined on its original table. Simply use:
Up next we will use a SYNTAX to create tables and here is an example:
in this SYNTAX, the table_name should be unique in the database, or else, it will return an error if another table exists with the same name. The command IF NOT EXISTS serves as a fail-safe if there was a table with the same name because, without it, it will terminate the statement.
Now let’s learn about Constraints and how you can use them on your tables.
PostgreSQL supports both table and column constraints. Constraints are rules or restrictions used for data in a table or a column. A table constraint specifies restrictions that can apply to the whole table while a column constraint will affect a single specific column.
Here are the constraints you can use in managing your tables:
- UNIQUE: this constraint ensures that the column will not contain repeated values in a statement
- NOT NULL: this will enforce a column to reject NULL values
- PRIMARY KEY: this one will indicate that the column will be used to identify uniquely a record within the table. It is important that while creating a PostgreSQL table that when you used a PRIMARY KEY constraint on a column, it can only contain unique values and it won’t accept NULL values. here’s an example:
- FOREIGN KEY: This constraint is used to relate tables to one another in a database, it comprises a group of columns in a single table that references the prime key column in another table. this will specify the value in that column must match the value in the specified table, as such database referential integrity will be maintained.
- CHECK: this constraint makes sure that the values in a specified column meet its specific requirement. CHECK uses Boolean expression to evaluate the values before being inserted into a table, if a value doesn’t pass the initial check, PostgreSQL will not insert the value and will issue a constraint violation error. Here’s an example of a CHECK constraint:
in the example above, we made sure that the price value must always be greater than 0.
Viewing Created Tables
This simple command in the pg_admin schema will let you view all your existing tables so you can check them for errors:
And there you have it, Now you know the different and popular ways to make a table in PostgreSQL, but you can also tap other resources like Ottomatik to secure, manage and create backups for hassle-free management of your databases making you perform tasks simpler and more efficient.