Brad Morton

This guide will demonstrate and provide examples on how to use the generate_series function in PostgreSQL to generate sequential data such as numbers and dates and use them in your queries. The PostgreSQL generate_series Function Syntax The generate_series function generates sequential data ranging from a starting value to a stopping value, incremented with a given step value. The syntax for the generate_series function is as follows: Note that: Generating a Series of Numbers The simplest example of using the PostgreSQL generate_series function is generating a range of integer numbers: Above, a SELECT statement outputs the … Read more

In PostgreSQL, sequences are database objects that generate unique sequential numbers. This article will show you how to create and use them using the CREATE SEQUENCE statement, and provide code examples. Sequences in PostgreSQL Sequences are most often used to generate primary keys in tables, or other unique identifiers. Temporary sequences can also be used when generating values or building loops for SELECT queries, but shouldn’t be confused with the generate_series function, which offers more flexibility and can be better suited for generating sequential data for querying … Read more

PostgreSQL’s stored functions allow you to place code in reusable containers, allowing you to write less code. This article explains how to use this convenient and powerful tool. This article will show you how to create, use, and manage Stored Procedures in PostgreSQL, with easy to follow examples. Stored procedures are one of the most useful and powerful PostgreSQL features. Stored procedures encapsulate multiple SQL statements into a single reusable code block. This lets you simplify your code, and provide a reliable way to repeat … Read more

PostgreSQL’s stored functions allow you to write SQL queries and code contained within re-usable functions. This article will show you how to use them, with code examples. Stored functions are similar to, but not the same as, stored procedures. How to Create a Stored Function You will need to use the CREATE FUNCTION statement to create new stored functions in PostgreSQL. The syntax for creating a function is as follows: Note that: In the below example, a PostgreSQL stored function is created that calculates the area of a … Read more

This article provides a reference for all of the built-in data types available in PostgreSQL databases. PostgreSQL Data Types Data Type Description Example Boolean TRUE/FALSE Boolean value TRUE Character (CHAR) Fixed-length string of characters ‘Hello there’ Character Varying (VARCHAR) Variable-length string of characters ‘Hello there’ Text String of characters with unlimited length ‘This text can be as long as you want it to be!’ Smallint Integer value ranging from -32768 to +32767 56 Integer (INT) Integer value ranging from -2147483648 to +2147483647 654321 Bigint Integer value ranging … Read more

This tutorial will show you how to create and use custom data types in PostgreSQL by using the CREATE TYPE statement, and provide examples. Creating a custom data type is not something most users will need to do (or need to do frequently, at least), however it’s useful to understand how types and custom types work in PostgreSQL should the need to use them arise, or should you be working on someone elses database that uses them. Custom data types are used when you need to represent … Read more

This article will explain the difference between stored functions and procedures in PostgreSQL databases. Both stored functions and procedures serve similar purposes – containing code that you want to call repeatedly – but they are slightly different, and that difference can be important depending on what you are trying to achieve. What are Stored Functions in PostgreSQL? Functions are containers for a repeatable set of code or SQL statements that accept parameters and return a value. Functions can be called from SQL queries, and from within other functions. Functions can … Read more

This article will show you how to find dependent objects in a PostgreSQL database. This is useful when deleting tables, functions, columns, and other PostgreSQL objects, allowing you to make sure that no other database objects reference them. Finding dependent objects is especially important if you are deleting a column, table, function, procedure, or other object using the CASCADE option, so that you can check in advance what may be deleted by the operation. Using the pg_depend Catalog to find Dependent Objects The pg_depend catalog contains a record … Read more