Create table in PostgreSQL
The goal of this tutorial is to introduce you to basic concepts of the PostgreSQL
CREATE TABLE statement using a simple business scenario. In this tutorial, you will learn to :
- Create simple tables with basic data types.
- Create basic constraints: primary key, foreign key and check constraint.
Our business scenario
John Smith, Mary Smith, Arnold Jackson, and Jeffery Westman are employees of a particular company. Each employee is identified by their SSN and employee ID.
John works in Sales, Mary works in Research, and Arnold and Jeffery work in Accounting. There are other departments that do not currently have employees, and there are yet other employees that have not yet been associated with a department. All departments have a unique department number which is auto-generated every time a new department is added.
The current payroll system captures salary history of all employees, and all salaries are greater than 1000 dollars.
Below is the data model that depicts the business scenario. Typically, we use nouns to identify tables, and verbs for relationships, especially when modeling a business scenario like above.
In this tutorial, you'll use psql to interact with a PostgreSQL database. You'll use the following credentials to connect to the workshop database:
- Username: groot
- Password: password (same password for the postgres user as well)
A database named: workshop
Let's get started.
We hope you feel more comfortable beginning to create tables in a PostgreSQL database. We have only covered the basics, so please feel free to refer to the official PostgreSQL documentation, which has a lot more examples and explanation.
Let's summarize what we did:
CREATE TABLEwith basic data types like char, varchar, date, timestamp, serial, and identity
- Used constraints such as Not Null, Primary Key, Foreign Key, and Check
- Inserted data to understand how data types and constraints work
And with that, we are finished. We wish you Happy Learning!
Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.
Introduction to creating tables in PostgreSQL
Introduction to Creating table
Before we start to create tables based on our business scenario, let's review some brief points:
a) To create a new table in PostgreSQL, you use the
CREATE TABLE statement.
CREATE TABLE will create a new, initially empty table in the current database. The user issuing the create command will be the owner of the table.
c) The name of the table must be distinct from the name of any other table, sequence, index, view, or foreign table in the same schema.
d) A constraint is an SQL object that helps define the set of valid values that can be stored in the table.
Let's try creating a test table with one field (i.e. column). We'll also go ahead and insert a row, and then review the data.
Execute the following SQL statement in the console:
CREATE TABLE test_table (name varchar); INSERT INTO test_table VALUES ('TestTable 1'); SELECT * from test_table;