Programming

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

This article will explain what PostgreSQL schemas are, and show you how to use them using the CREATE SCHEMA statement, as well as showing some example code. What is a Schema in PostgreSQL? In PostgreSQL, a schema is an entity within a database that holds other database objects such as tables, views, and sequences. They are an organizational unit that is used to group objects together and can also be used to manage permissions for them collectively. How to Create a PostgreSQL Schema using CREATE SCHEMA The CREATE … Read more

This tutorial will show you how to create a table in a PostgreSQL database using the CREATE TABLE statement, and provide a code example. In relational databases, tables hold all of the data stored in the database, so creating them is a fundamental task that you will need to perform regularly. If you are using databases already created by other software, understanding how tables are created is still useful for troubleshooting and understanding how your systems behave. The PostgreSQL CREATE TABLE Statement The CREATE TABLE statement is … Read more

This short guide will show you how to create a database, and a user that can access it, in PostgreSQL. This is useful if you are installing an app that provides its own table structures and data during installation. Many applications support PostgreSQL, for example the popular frameworks Django, Drupal, and Ruby on Rails can all connect to PostgreSQL databases and store data in them. These apps and frameworks usually supply either their own pre-defined tables, or their own tools for building your own table structure, leaving your only … Read more

This article will instruct you how to install the PostgreSQL database server on Linux – Red Hat/Fedora, Ubuntu, Debian, and Arch Linux. Installing PostgreSQL on Debian Linux Here’s now to install PostgreSQL on the Debian Linux distribution (and distributions based on it): From the Default apt Repository You can install PostgreSQL server straight by the apt software repository on Debian Linux by running: Note the use of the sudo command to run the package installation as the root (administrative) user. Installing More Recent Versions From the PostgreSQL apt … Read more

This article will explain the differences between PostgreSQL and Oracle Database, and help you to decide which you should use in your project (The answer is PostgreSQL). Choosing the right database for your application, whether it’s a webapp, mobile app, or desktop application is vital. Changing database backend mid-development could be a costly (in both your time or money) and frustrating experience. Trying to decide between other databases like MySQL, MariaDB, MongoDB, SQLite and MSSQL? Check out our other guide here. Oracle Database Oracle Database, otherwise … Read more