- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
- Create Primary Key on an existing table in PostgreSQL?
- How to add column to an existing table in PostgreSQL?
- What all constraints can be added to a PostgreSQL table?
- How to insert values from one table into another in PostgreSQL?
- How to create a table in JDBC using another table?
- How to combine different columns of a table to yield a single column in query output in PostgreSQL?
- How to create a temporary MySQL table in a SELECT statement without a separate CREATE TABLE?
- How to Query a DB in pgAdmin in PostgreSQL?
- What kind of indexing can be done on a PostgreSQL table?
- How to create a Hash Table in PowerShell?
- How to insert a Python tuple in a PostgreSql database?
- How to create a table with a caption?
- How to create a MySQL table with InnoDB engine table?
- How to create a MySQL table with MyISAM engine table?
- Create MySQL query to create a table from an existing table?