Databases

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

This article will show you how to check the version (or versions) of PostgreSQL running on your Linux system. As it is possible to install multiple versions of PostgreSQL on the same machine, there are a few ways to check the running version, which are detailed below. Checking PostgreSQL Server Version To check the running PostgreSQL server version, use the following pg_config command: Check PostgreSQL Client Version To check which version of the PostgreSQL client you are running, run the following psql command: Finding the PostgreSQL Executables If you … Read more

This short tutorial will demonstrate how to test the connection to a PostgreSQL database locally and remotely from Linux. This article is part of our series on getting started with PostgreSQL, head back to our index that lists our full PostgreSQL guide. Testing PostgreSQL Database Connection on Ubuntu/Debian To test connectivity to a PostgreSQL server, you’ll need the PostgreSQL client installed if it is not already. Do this by running: Then, you can use the pg_isready command to test the connection to a database: One of the following … Read more