PostgreSQL is a highly stable object-relational database that is backed by 30+ years of active development. Postgres uses different database objects to enhance data usage and management efficiency. The renowned database objects include tables, views, sequences, indexes, stored procedures, and functions. Each database object serves a unique functionality. However, to use any database object, it must be created first in the desired database.

Quick Outline

This Postgres guide will explain the complete procedure of creating Database objects using the following outlines:

• What is a Database and How to Create it in Postgres?
• What are Database Objects?
• How to Create Database Objects in Postgres Using CREATE Command?
• Bonus Tip 1: CREATE USER
• Bonus Tip 2: CREATE SCHEMA
• Bonus Tip 3: Drop Database Objects
• Conclusion

What is a Database and How to Create it in Postgres?

Databases are the systematic collection of structured data/information, which is controlled by a database management system. To create a new database, execute the CREATE statement followed by the DATABASE keyword, as shown in the following syntax:

CREATE DATABASE name_of_database
 WITH
 [OWNER = name_of_user]
 [TEMPLATE = template_name]
 [ENCODING = encoding]
 [LC_COLLATE = collate]
 [LC_CTYPE = ctype]
 [TABLESPACE = name_of_tablespace]
 [ALLOW_CONNECTIONS = true | false]
 [CONNECTION LIMIT = maximum_connections]
 [IS_TEMPLATE = true | false ]

The syntax seems a little bit complicated, let’s comprehend it line-by-line.

- The “CREATE DATABASE name_of_database” creates a new database. Replace the “name_of_database” with any valid and easily rememberable/understandable database name.

- The database name is followed by the “WITH” clause that is used to specify the parameters for the database.

- Specify the user/owner name using the “OWNER” clause.

- Specify the template name from which the new database will be created using the TEMPLATE keyword.

- Specify the character set encoding scheme to be used in the new database.

- Specify the default collation order and character classification for the new database using the LC_COLLATE parameter.

- Specify the character classification for the new database using the “LC_CTYPE” parameter.

- Specify the tablespace of your choice using the TABLESPACE parameter.

- Specify a Boolean TRUE or FALSE to manage the connections. If FALSE no one can connect to the database, otherwise, users can connect to the database.

- Define the connection limit via the CONNECTION LIMIT parameter. If you want unlimited connections then use the “-1” value for the “CONNECTION LIMIT” parameter.

- Specify a Boolean TRUE or FALSE in the “IS_TEMPLATE” parameter. Specifying the TRUE value means anyone can clone/duplicate the database while specifying FALSE means only superusers/owners can clone the database.

Example: Create New Database

In the following code, the “CREATE DATABASE” command is executed to create a new database named “postgres_db”:

CREATE DATABASE postgres_db;

The output confirms the database creation:

What Are Database Objects?

Any entity that is defined in a database and is utilized to store or reference data is called a database object. Postgres supports several database objects, such as a table, view, sequence, index, etc. The most frequently used object is a table that keeps the data in a well-structured manner. Other objects include views, sequences, indexes, tablespaces, functions, etc.

How to Create Database Objects in Postgres Using CREATE Command?

In PostgreSQL, the database objects are created using the CREATE command. In this write-up, we will discuss how to use the Postgres “CREATE” command for Table, View, Sequence, INDEX, Function, and Tablespace Creation.

Case 1: Use the CREATE Command For Table Creation

Tables are among the most frequently utilized database objects that keep the data in the form of rows and columns. In Postgres, the CREATE command can be executed with the “TABLE” keyword to create a table in the desired database. For this purpose, the following syntax is used in Postgres:

CREATE TABLE [IF NOT EXISTS] tab_name (
 col1 data_type col_contraint,
 col2 data_type col_contraint,
 col3 data_type col_contraint,
 …
 tab_constraints
 );

Let’s break down this syntax line-by-line to get a better understanding:

- “CREATE TABLE” is a statement that creates a new table.

- “[IF NOT EXISTS]” is an optional clause that ensures the table creation only if it is not already created.

- “tab_name” represents the table name to be created.

- “col1, col2, col3, …, ” are the column names to be created.

- “data_type” represents any valid data type, such as int, float, text, etc.

- “col_contraint” represents column constraints like a PRIMARY KEY, NOT NULL, etc.

- “tab_constraints” indicates the table rules/constraints, such as PRIMARY KEY, FOREIGN KEY, etc.

Example: Create Table Using CREATE Command

In this example, a table named “athlete” will be created with four columns: “athlete_id”, “athlete_name”, “athlete_age”, “athlete_weight”:

CREATE TABLE IF NOT EXISTS athlete(
 athlete_id INT PRIMARY KEY,
 athlete_name TEXT,
 athlete_age SMALLINT,
 athlete_weight SMALLINT
 );

Here,

- A column named “athlete_id” of type “INT” is created as a “PRIMARY KEY”.

- After that, an “athlete_name” column of type “TEXT” is created.

- Finally, the “athlete_age” and “athlete_weight” columns of type SMALLINT are created.

The “CREATE TABLE” message in the output confirms the successful creation of the selected table, i.e., “athlete”.

Case 2: Use the CREATE Command For View Creation

A View is a virtual/logical table that is used to simplify a complex query. Views represent the data of single or multiple tables using a SELECT query. Views (except for a materialized view) didn't store the data physically like regular/normal tables.

In PostgreSQL, the “CREATE” command can be executed with the “VIEW” keyword to create a view, as demonstrated in the following syntax:

CREATE VIEW name_of_view AS select_query;

Here,

- “CREATE VIEW” is a command that creates a new virtual table.

- “name_of_view” is a view to be created.

- “select_query” can be a simple select statement or a complex one with joins.

All in all, the above statement will create a view based on the query specified after the “AS” keyword.

Example: Create View Using CREATE Command

In this example, we will create a view named “athlete_info” based on the “athlete” table”

CREATE VIEW athlete_info
AS SELECT athlete_id, athlete_name
FROM athlete;

The SELECT query fetches the “athlete_id”, and “athlete_name” columns of the athlete table. The “CREATE VIEW AS” statement creates a new view based on the fetched column.

This way, a view can be created in Postgres.

Case 3: Use the CREATE Command For Sequence Creation

A sequence in Postgres is a schema-bound object that generates a series of integers according to the given specifications. A sequence can be created in Postgres by executing a CREATE statement followed by a “SEQUENCE” keyword.

CREATE SEQUENCE [ IF NOT EXISTS ] name_of_sequence
 [ AS { INT | BIGINT | SMALLINT} ]
 [ INCREMENT [ BY ] increment_val ]
 [ MINVALUE min_val | NO MINVALUE ]
 [ MAXVALUE max_val | NO MAXVALUE ]
 [ START [ WITH ] initial_value ]
 [ CACHE cache ]
 [CYCLE | NO CYCLE]
 [ OWNED BY { table_name.col_name | NONE } ]

Let’s comprehend this syntax line-by-line,

- “CREATE SEQUENCE [IF NOT EXISTS] name_of_sequence” creates a new sequence if it doesn’t exist already.

- “[AS { INT | BIGINT | SMALLINT}]” specifies the data type of the sequence.

- “[INCREMENT [ BY ] increment_val]”, “[ MINVALUE min_val | NO MINVALUE ]”, [ MAXVALUE max_val | NO MAXVALUE ]”, [ START [ WITH ] initial_value ] specifies the increment, minimum, maximum, and initial value of the sequence.

- [ CACHE cache ] is used to determine sequence numbers that are pre-allocated or stored in the memory for faster/quick access.

- [CYCLE | NO CYCLE] determines the restarting behavior of the sequence. If you specify “CYCLE”, the sequence will restart once it reaches the maximum limit. By default, the “NO CYCLE” behavior is enabled for the sequence.

- [ OWNED BY { table_name.col_name | NONE } ] is used to associate the sequence with a specific table column. This way, when a user drops a table or column it will automatically delete the associated sequence.

Example: Create Sequence Using CREATE Command

In this example, we will create a sequence named “athlete_sequence” with SMALLINT data type, as shown in the following snippet:

CREATE SEQUENCE athlete_sequence
 AS SMALLINT
 INCREMENT 1
 MAXVALUE 15
 START 1;

Here, the initial value of the sequence is 1, the maximum value is 15, and the increment value is 1. The below output confirms the sequence creation:

Case 4: Use the CREATE Command For INDEX Creation

In databases, when a user fetches a record the query optimizer searches for that record from the entire table and loads it when the perfect match is found. This procedure might take/consume a lot of time. To overcome this problem, indexes can be created that enable us to mark a particular table or table columns that are used frequently in our queries. To create an index, execute the CREATE statement along with the INDEX keyword, as demonstrated in the following syntax:

CREATE INDEX <name_of_index>
 ON <table> <USING method>
 (
 col_name [ASC | DESC] [NULLS {FIRST | LAST }],
 …
 );

In the above-stated syntax:

- Specify the “CREATE INDEX” statement followed by the name of the index to be created.

- After that, specify the ON clause and then the name of the associated/linked table.

- Next, specify the USING keyword followed by the index method, such as “btree”, “spgist”, “hash”, etc. If you don’t specify any method, Postgres will use the default method i.e., “btree”.

- After that, specify a single or multiple columns of the index followed by their sorting order, i.e., ASC or DESC.

- Finally, you can specify the “NULLS FIRST” or “NULLS LAST” option to sort the column that contains NULL values.

Example: Create INDEX Using CREATE Command

Execute the SELECT query with the “EXPLAIN” statement to get the query plan:

EXPLAIN SELECT *
 FROM athlete
 WHERE athlete_name = 'Null';

The following screenshot demonstrates that the query doesn’t use an index:

Let’s create an INDEX by executing the following query:

CREATE INDEX index_athlete_name
ON athlete(athlete_name);

The following snippet ensures that an index named “index_launch_date” has been successfully created on the “athlete_name” column of the “athlete” table:

Now the database will use this index to pursue the athlete_name column of the athlete table.

EXPLAIN SELECT *
 FROM athlete
 WHERE athlete_name = 'Null';

Case 5: Use the CREATE Command For Function Creation

Functions are one of the most significant database objects that ensure the efficient reusability of the code. To create a user-defined function in PostgreSQL, the CREATE command can be executed with the “FUNCTION” keyword, as illustrated in the following syntax:

CREATE [ OR REPLACE ] FUNCTION name_of_function(arg_list)
 RETURNS return_data_type
 LANGUAGE plpgsql
 AS
 $$
 DECLARE
 -- Declare the variable here
 BEGIN
 -- user-defined logic
 END;
 $$

In the given syntax,

- The “CREATE [ OR REPLACE ] FUNCTION” statement ensures the creation of a new or updation of an existing function.

- The “name_of_function” is the function name to be created.

- “arg_list” represents the arguments to be accepted by the function.

- Inside the function’s body, use the “RETURNS” keyword followed by the returned data type.

- After that, specify the procedural language of the function such as plpgsql.

- Finally, use the dollar-quoted string constant “$$” to specify the rest of the code/logic.

Example: Create Function Using CREATE Command

In the following example, a simple function named “age_counter” is created that counts the athletes whose age is between “start_range” and “end_range” :

CREATE OR REPLACE FUNCTION age_counter(start_range INT, end_range INT)
 RETURNS INT
 LANGUAGE plpgsql
 AS
 $$
 DECLARE
 age_count INT;
 BEGIN
 SELECT COUNT(*)
 INTO age_count
 FROM athlete
 WHERE athlete_age between start_range and end_range;
 RETURN age_count;
 END;
 $$

In this example,

- A function is created as “age_counter” that accepts two arguments “start_range” and “end_range” of type INT.

- The return type of age_counter() is INT.

- A variable of type INT is declared as age_count.

- The COUNT() function is implemented to count/compute the age between the specified range.

- In the end, the RETURN keyword is used to return the “age_count”.

Execute the following line of code to get the list of all those athletes whose age is greater than 28 but less than 32:

SELECT age_counter(28, 32);

The output illustrates that there are five athletes whose age is between 28 and 32 (both inclusive).

Case 6: Use the CREATE Command For Tablespace Creation

Tablespace is a place/location on the disk where all the database objects are stored/located. To create a new tablespace in Postgres, run the CREATE command with the TABLESPACE keyword, as shown in the following syntax:

CREATE TABLESPACE name_of_tablespace
 OWNER name_of_user
 LOCATION directory_location;

Here,

- The CREATE TABLESPACE statement is followed by the “name_of_tablespace” to be created.

- Next, the OWNER clause is used to specify the user’s name who owns the tablespace.

- Finally, the path at which the tablespace will be created is specified using the LOCATION keyword.

Example: Use the CREATE Command to Create a Tablespace

In the following example, the CREATE TABLESPACE statement is executed to create a new tablespace named “postgres_tablespace”:

CREATE TABLESPACE postgres_tablespace
 LOCATION 'C:\New folder';

The output snippet illustrates that a tablespace has been successfully created at the desired location

Bonus Tip 1: CREATE USER

Every database has several users and each user has different roles and access privileges. These users are different/separate from the OS users. The database users own different database objects, such as tables, indexes, functions, etc. These users help us manage the database objects’ access privileges efficiently. To create a user in Postgres, the CREATE command can be executed with the USER keyword, as illustrated in the following syntax

CREATE USER name_of_user;

Replace the “name_of_user” with any name of your choice.

Example: Use the CREATE Command to Create a User

The following code creates a new database user named “db_user”:

CREATE USER db_user;

The output confirms the creation of the desired user:

Bonus Tip 2: CREATE SCHEMA

A Postgres schema is a named collection of different objects like tables, functions, views, sequences, etc. Postgres allows us to create several schemas in a single database, which helps us manage database objects into logical groups efficiently. To create a new Postgres schema, execute the CREATE command with the SCHEMA keyword, as we did in the following syntax:

CREATE SCHEMA <name_of_schema>;

Specify a valid schema name of your choice in place of “name_of_schema”.

Example: Use CREATE Command to Create a Schema

In the following example, a new schema named “emp_schema” will be created:

CREATE SCHEMA emp_schema;

The output snippet confirms the schema creation:

Bonus Tip 3: Drop Database Objects

In databases, each database object carries a specific amount of space. In case, an object is no longer needed, you can drop/delete it from the database. Doing so will clean the database, which will eventually help you manage your database efficiently. To drop a database object, specify the DROP command followed by the database object to be dropped, as demonstrated in the following syntax:

DROP datababse_object name_of_object;

Here,

- Replace the “database_object” with any valid object, such as “TABLE”, “VIEW”, “INDEX”, etc.

- After that, specify the name of the object (view name, table name, index name, etc.) to be dropped in place of “name_of_object”.

Example: Drop Database Object Using DROP Command

In the following example, a tablespace will be dropped using the DROP statement:

DROP TABLESPACE postgres_tablespace;

The output confirms that the selected tablespace has been successfully dropped:

That’s all about creating or dropping a database object in PostgreSQL.

Conclusion

Database objects are entities that are defined in a database and used to store or reference data. In PostgreSQL, the database objects are created using the CREATE command. The most frequently used object is a table that stores the data in a well-structured format. The other renowned database objects include tablespaces, views, sequences, indexes, stored procedures, and functions. Each database object serves a unique functionality. This Postgres blog has illustrated the use of each database object with suitable examples.