top of page

PostgreSQL-How to Add a new column and Update it in same stored procedure

Before jumping into the topic let’s revise the basics of Stored Procedure like definition, advantages, disadvantages, syntax etc.

Definition of Stored Procedure:

A stored procedure in PostgreSQL is a set of SQL statements that can be stored in the database.These are pre-compiled objects which are compiled for the first time and their compiled format is saved, which executes (compiled code) whenever it is called.

The main reason for using Stored Procedures is to make it easier to work with databases. They help to make database operations, like getting or changing data, faster and more efficient.

Advantages of Stored Procedures:

  1. Ease of Use: Once we create a Stored Procedure, we can use it over and over without needing to rewrite the commands.

  2. Speed: Stored Procedures are already prepared, so the database can run them faster.

  3. Less Data Sent Over Network: Because the commands are stored in the database, only the results need to be sent over the network, which can make things faster.

  4. Reusability: We can use a Stored Procedure in many parts of our program.

  5. Security: Stored Procedures can help protect our data by controlling who can access it.

Disadvantages of Stored Procedures:

  1. Hard to Find Errors: If there’s a mistake in a Stored Procedure, it can be hard to figure out what went wrong.

  2. Dependent on Database: Stored Procedures are tied to the specific database system we’re using.

  3. Not Easy to Move: If we want to switch to a different database system, we might have to rewrite our Stored Procedures.

  4. Costly: Creating and maintaining Stored Procedures can require skilled developers, which can be expensive.

PostgreSQL Stored Procedure Syntax:

LANGUAGE plpgsql
AS $$
 - variable declaration
 - PROCEDURE body - all logics


LANGUAGE : PostgreSQL supports writing stored procedures in several different languages.
Plpgsql : Stands for Procedural Language PostgreSQL
$$ : To replace multiple single quotation marks and print as it is whatever we write between $$ $$ symbols.
DECLARE: Where we can declare all your variables.
BEGIN: Inside BEGIN we can write procedure body and logic

Calling a stored procedure:

To call a stored procedure, we can use the CALL statement as follows:

Call stored_procedure_name(argument_list);

Now, let’s learn how to create a Stored Procedure for adding a new column to an existing table and updating it with specific criteria.

To make things clearer, let’s imagine we’re working with a table called ‘covid_details’ in a database focused on COVID information. This table already has 11 columns. We want to add a new column called ‘year_extracted’, and this column will be updated with the year extracted from the existing ‘date_of_onset’ column.

Step #1:

Before adding the new column to the ‘covid_details’ table, let’s first check all the existing columns. We can do this in two ways.

Option #1:

  1. Open PostgreSQL (pgAdmin 4).

  2. Navigate to the ‘covid_details’ table in the COVID database.

  3. We’ll see all 11 columns listed there.

Option #2:

We can see all the columns by executing the following SELECT query in the pgAdmin 4 query tool:

Step #2:

Creating a stored procedure ‘pr_add_year_column()’ that alters the table covid_details to add the new column ‘year_extracted’.Now the ‘year_extracted’ column updated by using a Date Function ‘EXTRACT()’ to populate with the Year value from ‘date_of_onset’ column.

Let’s understand how the ‘EXTRACT()’ function works:

The ‘EXTRACT()’ function is a Date Function in PostgreSQL used for dealing with dates and times. It helps us pull out specific parts, like the day, month, minute, hour, etc., from a date or time value. We give it a ‘source’ (the date or time we want to extract from) and a ‘field’ (what we want to extract), and it gives us back that specific part. The result it gives is a number, usually a whole number.

Here’s the syntax for using the ‘EXTRACT()’ function:

EXTRACT(field FROM source)

The PostgreSQL EXTRACT() function requires two arguments: field and source

1) field

The field argument specifies which information we want to extract from the date/time value.

The following table illustrates the valid field values:

Field Value




The century

The number of centuries


The day of the month (1-31)

The number of days


The decade that is the year field divided by 10


The day of the week (Sunday (0), Monday (1) … Saturday (6)).



The day of the year (1-365/366)



The number of seconds since 1970-01-01 00:00:00 UTC

The total number of seconds in the interval


The hour (0-23)

The number of hours


The day of the week, Monday (1) to Sunday (7)



The ISO 8601 week number of year



The second field, including fractional parts, multiplied by 1000000



The millennium

The number of millennium


The second field, including fractional parts, multiplied by 1000



The minute (0-59)

The number of minutes


The month 1-12

The number of months, modulo (0-11)


The quarter of the year (1 – 4)

The number of quarters


The second field, including any fractional seconds

The number of seconds


The timezone offset from UTC, measured in seconds



The hour component of the time zone offset



The minute component of the time zone offset



The number of the ISO 8601 week-numbering week of the year



The year


2) source

The source is a value of type TIMESTAMP or INTERVAL. If you pass a DATE value, the function will cast it to a TIMESTAMP value.

For instance, if the value of ‘date_of_onset’ is ‘2022–04–29’, then the new column ‘year_extracted’ should store ‘2022’.

When we run the following query, the Stored Procedure ‘pr_add_year_column()’ should be created without any issues.

We can check that the Stored Procedure ‘pr_add_year_column’ has been successfully added by looking under the “Procedures” section.

Calling this Procedure ‘pr_add_year_column

Step #3:

To make sure that the new column ‘year_extracted’ is added correctly and updated with only the year, we need to check it.

Either we can check in left panel of pgAdmin 4.

Or can execute a querry in Query Tool to check the added column and value of that column as expected.

So, in this way, we’re able to both add a new column and update it within the same stored procedure. I hope you found this explanation helpful in understanding Stored Procedures in PostgreSQL.

Thank you for taking the time to read this.

63 views0 comments

Recent Posts

See All


Obtuvo 0 de 5 estrellas.
Aún no hay calificaciones

Agrega una calificación
bottom of page