PostgreSQL

Postgres Default Values

The word “Default” suggests that it must be some unique value allocated to a particular variable when it has no other specific value. In PostgreSQL, we utilize the default values to assign a unique value to a particular column when there are no values assigned to that particular column upon the use of the Insert command. Let’s see some examples in PostgreSQL to see how a default value can be set for a particular column. Start with the GUI PostgreSQL first. Open the pgAdmin and add your password for the server. Launch the query editor for the specific database i.e., aqsayasin.

Example 01:

We have to generate a new table with some default values set for its columns. Take a look at below CREATE TABLE command to create table “test” having integer-type “id” column, integer-type “age” column, and character-type “name” column. The default values for three columns are set to “0000”, “0000”, and “c” with the use of the DEFAULT keyword.

After running the SELECT query, we have got the below empty table in a grid view.

Let’s insert 5 record values to the column “id” starting from 1 to 5 as primary keys. Save this update in the database with the use of the “Save Record” button on pgAdmin of PostgreSQL.

After saving the records, we fetched the table again with the SELECT query and got the below-shown result. You will see that the default values for columns “age” and “name” will be reflected in the table automatically as “0” and ‘c’.

Example 02:

Let’s have a look at some of the queries to set the default values for a particular column using the PostgreSQL Shell. The PostgreSQL shell black screen will be opened up on your desktop as shown. It must be asking for your localhost server information from SQL. Therefore, you have to add your localhost name i.e., Server name, Database name located in your server to work within i.e., aqsayasin, port number i.e., 5432, username owning the particular database i.e., aqsayasin, and password for a particular user to work within the PostgreSQL Shell. After adding all the necessary data required to use the PostgreSQL shell, the shell is ready to work on as shown.

We have to start our first example with the creation of a new table named “New” in the database “aqsayasin”. The CREATE TABLE command has been used so far. The table “New” has been created with three columns. The column “ID” is of integer type, column “Name” is of text type, and the column “salary” is of numeric type. While the default value for the column “salary” has been set to “000” as shown in the query below. The table is created perfectly.

Right now, we haven’t added any data in the table and hence have no values in any of its columns. Let’s insert 5 values in the first 5 rows of the column “id” of table “New”. The INSERT command has been used with the “INTO” keyword. The column “id” is specified in brackets. The keyword “VALUES” indicates that the value after it will be inserted into a particular column. All the 5 records of data for a single column have been successfully added to the table “New”.

It’s time to see the newly created table “New” along with its values on the PostgreSQL shell. For this, we need to utilize the “SELECT” instruction in the command shell. The “*” will let us fetch all the records of this table “New” from the database “aqsayasin”. The records have been displayed on the PostgreSQL shell. After adding the 5 records, we have got the default value “0” for column “salary” as we have made it default at the time of table creation. On the other hand, the column “name” has been fully empty because no default value was set for it.

Let’s alter our table “new” and add a new column to it with some default value set. So, we have been using the “ALTER TABLE” command here to do so. The keyword “ADD COLUMN” is utilized in the mentioned command to add a new column named “Status” in the table “new” of database “aqsayasin”. This column is of “BOOLEAN” type and it is set to “NOT NULL” i.e., must have some value in it. We have set “FALSE” as the default value for this column “Status”. After fetching the table “New” all records, we have got the “f” as a default value to column “status”.

As you can see that, after setting default values for a new column and inserting values for other columns doesn’t make any change to column “name”. Let’s set the default value “c” for the column “name” using the same “ALTER TABLE” command with the keyword “SET DEFAULT”. We have also altered the default value for the column “Salary” as shown in the query.

To make PostgreSQL reflect the new changes, we need to insert some data. Therefore, we have inserted 5 records in the “id” and “status” columns using the INSERT INTO instruction.

After the insertion of records, we have to fetch all the data of table “new” to see if the changes reflect or not. The SELECT query is here to achieve this task. In return, we have got the default values “c” and “10” already set for columns “name” and “salary” in the output table.

Let’s set the default value “888.888” for the column “salary” of table “New” using the ALTER TABLE command followed by the keyword “ALTER COLUMN” as below. After that, we have added 3 new records for the “id” and “name” columns.

The changes have been reflected in the output image for table “New” below.

Example 03:

Let’s see a little similar example as above. We have created a new table “Omg” with CREATE TABLE instruction with no default value set for any column. After that, 3 records have been inserted in this table with the INSERT INTO command.

The first look of the table is shown below.

We have altered this table by setting its column “Price” to the default value “000”. 2 new records are inserted for columns “ID” and “Color”.

After this, we are setting the default value “ “ for column “Color” and added two records in the “id” and “price” column through the ALTER TABLE and INSERT INTO commands.

After two alterations, our table looks like below. Empty last 2 records in the “color” column and “0” in the “price” column are the default values.

Let’s change the default value for column “Color” by setting it to “*****” with the ALTER TABLE command. Three more records have been inserted into “id” and “price” columns.

The last update is showing the default value “*****” has been automatically set for column “color” mentioned in last three records.

Conclusion

This article demonstrates the use of default values in PostgreSQL using the PostgreSQL different commands with the “SET DEFAULT” keyword. The purpose of this demonstration is to let users know how the default values are different from the rest of the records in a database. It has been accomplished with the help of CREATE TABLE, INSERT, and ALTER TABLE commands. This article would be helpful to every database user despite his/her experience level.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.