Databases

This short tutorial will show you how to completely delete/destroy/drop a table in PostgreSQL using the DROP TABLE statement, and provide examples. Before you Delete a Table… There are a few things you should check before you try and destroy a table in PostgreSQL, especially if you’re working on a production database that is serving users. First and most important, take a backup of your PostgreSQL server – just in case you delete the wrong table, or change your mind later. Next, make sure you’re logged in as the default … Read more

This short tutorial will show you how to connect to a PostgreSQL database server from the Linux command line. Instructions are included for Ubuntu, Fedora, and Arch Linux. The PostgreSQL Client – psql Command To connect to a PostgreSQL database server from the Linux command, you need to install and use the psql program – the PostgreSQL client. By default, when installing the PostgreSQL server on Linux, the client is also installed – but you may want to install the client without the server if you are only planning … Read more

This post will demonstrate how to list all users, permissions, and roles on a PostgreSQL database server. Maintaining data security is paramount in any application or database deployment, and making sure users only have access to the data they need is the cornerstone of this security. PostgreSQL has robust user, role, and permission management features, particularly with role based access that allows you to assign permissions to roles, and roles to users, making management tasks more straightforward and reducing the chance of accidentally assigning the … Read more

This question gets asked quite a lot… How do you pronounce PostgreSQL? For that matter, how do you pronounce SQL? This is often debated among tech-types, this article will take a look at the pronunciations for these two data terms. PostgreSQL is a database management system which uses SQL syntax. As there is argument over how SQL is pronounced, so too is there for PostgreSQL. How Do You Pronounce ‘SQL’? There are two common pronunciations for SQL – pronouncing it like the word “sequel”: …and spelling out the letters: I’m partial to the latter as … Read more

PostgreSQL is one of the most popular open-source database systems. This article will show you how to list all databases in PostgreSQL from the command line with the \l command. Prerequisites To use PostgreSQL, you’ll need to have a PostgreSQL database server set up, and have the psql PostgreSQL command line tools installed. Connecting to the Server If you are running your database on the same machine that you are connecting from and are connecting as the default postgres user, you can simply log in using: If you … Read more

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