How to create a table in PostgreSQL?

Creating a table in PostgreSQL is pretty straightforward. The syntax is −

CREATE TABLE table_name(
   Column1_name type optional_constraint,
   Column2_name type optional_constraint,
   .
   .
   .
   ColumnN_name type optional constraint
);

If you want to make sure that your table is created only if it doesn’t already exist, you can specify that explicitly −

CREATE TABLE IF NOT EXISTS table_name(…);

An example of table creation is given below −

CREATE TABLE marks(
   serial_no SERIAL PRIMARY KEY,
   name VARCHAR,
   roll_no INTEGER,
   marks_obtained INTEGER,
   perc_marks DOUBLE PRECISION,
   max_marks INTEGER,
   date_of_entry DATE
);

The above command will just create the table, and not insert values into the table. We’ll see how to do that shortly.

You can see that several data types have been used here. You can find the entire list of data types that can be used in PostgreSQL here − https://www.postgresql.org/docs/9.5/datatype.html

I want to highlight one: SERIAL. It is an auto-incrementing four-byte integer. Therefore, you don’t even need to specify it when adding values to the table, it will auto-increment. Since it is only 4 bytes long, the maximum possible value is 2147483647. Use bigserial if you expect more rows in the database than this value.

Note that we only have one column with constraints: serial_no. It is constrained to be the PRIMARY KEY. In other words, no two rows can have the same value of serial_no. If you forcefully try to add a row with a value of serial_no that already exists, PostgreSQL will throw up an error.

Now let’s discuss inserting values into the table.

You can do that using the INSERT statement. The syntax is as follows −

INSERT INTO table_name(column1_name, column2_name, …,columnN_name)
VALUES (column1_value, column2_value, …, columnN_value),
(column1_value, column2_value, …, columnN_value),
(column1_value, column2_value, …, columnN_value);

As you can see above, you can insert multiple values into a table using a single INSERT statement.

Now let us insert some values in our newly created table

INSERT INTO marks(name, roll_no, marks_obtained, perc_marks,
max_marks, date_of_entry)
VALUES ('Yash', 26, 42, 42.0, 100, current_date),
('Isha', 56, 175, 87.5, 200, current_date);

Notice that we did not specify the value for serial_no. This is because it is an auto-incrementing column, and we need not explicitly specify its value.

The INSERT statement return is generally of the following format −

INSERT oid count

OID is an object identifier, generally 0 for INSERT statements. The count is the number of rows successfully inserted.

In the above example, the INSERT statement will return

INSERT 0 2

You can read more about inserting into a PostgreSQL table here −https://www.postgresqltutorial.com/postgresql-insert/

Now, if you query the table,

SELECT * from marks

The output will be −

Yash Sanghvi

Updated on: 02-Feb-2021

201 Views

Kickstart Your Career

Get certified by completing the course

Get Started