Easy table and database creation examples in PostgreSQL.

This post will demonstrate basic concepts for creating a simple database and table in PostgreSQL. These examples will brush the surface of the CREATE DATABASE and CREATE TABLE commands, providing a solid base, to begin with, and build on moving forward.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, instruction and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

I will be using Xubuntu Linux 16.04.2 LTS (Xenial Xerus), PostgreSQL 9.6.2 and pgAdmin for these exercises.

I will assume you have a running instance of PostgreSQL 9.6 installed on your machine.

The CREATE DATABASE command is used to create a database in PostgreSQL.

The syntax is as follows:

CREATE DATABASE database_name;

Running the below command in pgAdmin, creates a database I have named instructional:

The following output is displayed in pgAdmin upon completion.

You use the CREATE TABLE command to create a new database table. Syntax-wise, it looks like this:

CREATE TABLE table_name_here(
column_name DATA TYPE optional column_constraint,
column_name DATA TYPE optional column_constraint,
column_name DATA TYPE optional column_constraint
optional table_constraint);

Now I will create an example table for demonstration purposes. The gamer information table I am mimicking is basic in design and functionality:

This command is overflowing with interesting things that I will explain below.
At line 1:

CREATE TABLE galaxy_user

simply creates a table named galaxy_user with the CREATE TABLE command.
Next on line 2:

user_id serial PRIMARY KEY,

Establishes a column named user_id with the data type serial that has the PRIMARY KEY constraint.
Line 3:

user_name VARCHAR(30) UNIQUE NOT NULL,

A column named user_name of the VARCHAR data type. This column has two constraints, UNIQUE and NOT NULL.
Line 4:

pass_word VARCHAR(30) UNIQUE NOT NULL,

Identical to Line 3 except this column is named pass_word.
Line 5:

email VARCHAR(100) UNIQUE NOT NULL,

Another similar column. This one is named email. The main difference here is the number 100 just after VARCHAR.
Line 6:

galaxy_coins INTEGER

Different altogether. The column name is galaxy_coins with a data type of type INTEGER.
Line 7:

);

This line completes the parenthesis and ends the CREATE TABLE command with a semicolon. SQL commands are terminated with a semicolon (‘;’).

Running this CREATE TABLE command in pgAdmin returns:

I can further confirm the galaxy_user table is present in the instructional database by running the ‘\dt’ (list tables) command with the psql command line utility tool in the Linux terminal:

---

FUNDAMENTAL INFORMATION: SQL keywords and commands or identifiers, are case-insensitive, meaning: CREATE TABLE, create table or CrEaTe TaBlE are all the same command to SQL. However, it is recommended to follow convention by typing SQL commands and keywords in all uppercase letters. They can be double quoted to preserve the case.

---

In this section, I will briefly cover all of the data types and constraints used in this example. I encourage you to peruse the spectacular PostgreSQL documentation on the different ones available for an in-depth look. The information provided there is incredibly informative.

Data Types:

•  SERIAL: Creates unique identifier columns. Similar to AUTO_INCREMENT common in other SQL versions. SERIAL has the NOT NULL constraint applied (behind the scenes) to prohibit a NULL value from being inserted for this column. Holds an integer value.
• VARCHAR: A character varying type that can hold up to a specified number of characters in length. Example: VARCHAR(25) can hold up to 25 characters. Essentially a string value.
• INTEGER: A whole number value.

---

Constraints:

• PRIMARY KEY: A column with this constraint signifies it can be a unique identifier for all the rows in a table. This calls for the value to be both UNIQUE and NOT NULL. A PRIMARY KEY can be implemented in more than one column.
• NOT NULL: A column with this constraint cannot contain the NULL value. A field that has NULL for the value, means the field has no value. Check online for more information concerning the NULL value.
• UNIQUE: This constraint indicates that the column value across the table rows cannot contain identical values. In other words not match any other values for that same column. For example, the user_name column cannot contain two user names ‘John_Q_doe’.

---

What does it all mean?

A final description of the galaxy_user table.

1. A user_id column that can auto-increment (advance to the next value automatically) each time a record is inserted. Without you having to manually advance the next value. Since this column has the PRIMARY KEY constraint, it can be used as a unique identifier for the rows of the table thus it cannot be identical to any other values present in this column across the table.
2. A user_name column that holds a string not longer than 30 characters. This column cannot be left blank (NOT NULL constraint) and cannot be identical (i.e. not match) to any other value found in this column (UNIQUE constraint).
3. A pass_word column with identical rules and specifications as found in the user_name column.
4. An email column with all the identical constraints and data type as the user_name and pass_word columns with the exception that the email value entered can be up to 100 characters in length.
5. A galaxy_coins column of type integer (e.g. 2, 33, 100) that has no constraints whatsoever. This column can be left blank or contain any whole number value, even identical values found in other rows for this column.

---

Over the horizon…

Following this example should provide you with the basic information needed to create a database and table in PostgreSQL. However, make your experience your own. I encourage you to explore the PostgreSQL documentation. Numerous options are available for both commands that cannot be completely covered in this post alone.

I will expand further on these commands by exploring the ALTER TABLE command and the INSERT statement in a forthcoming post, visiting options to change an existing table and populating it with data. Subscribe for more great PostgreSQL content so you don’t miss out.

---

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

To receive notifications for the latest post from this blog via email, please subscribe by clicking the ‘Follow Digital Owl’s Prose’ button in the sidebar!

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.