Home » Programming

How to Drop/Delete/Destroy a Table in PostgreSQL

PostgreSQL drop table

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

Home » Programming

How to Connect to PostgreSQL from the Linux Command Line

How to Connect to PostgreSQL from the Linux Command Line

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

Home » Programming

How to List All Users, Permissions, and Roles in PostgreSQL

PostgreSQL list permissions and roles

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

Home » Programming

How Do You Pronounce ‘PostgreSQL’?

How do you pronounce PostgreSQL

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

Home » Programming

How to List All Databases in PostgreSQL

How to list all PostgreSQL databases

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

Home » Programming

How to Clear the Terminal/Screen in Python

How to Clear the Terminal/Screen in Python

This tutorial will show you several ways to clear the terminal/screen in your Python scripts, with code examples that you can copy and paste into your own project. Clearing the screen will remove all text from the screen, blanking/resetting it. This is useful to hide output from the command line or previous Python statements so that you can more easily see newly added output, and keep your command line apps looking tidy. There are a few ways to achieve this, so here they are: Clearing … Read more

Home » Programming

How to Create Sequences in PostgreSQL Using generate_series

How to Use generate_series to Create Sequences in PostgreSQL, with Examples

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

Home » Programming

How to Create a Dictionary in Python from a JSON API/URL

How to Create a Dictionary in Python from a JSON API/URL

This tutorial will show you how to create Python dictionary from JSON data retrieved from an API via HTTP. This is useful for retrieving data to use in your Python scripts and applications. Step 1: Install the request Library The easiest way to interact with web resources in Python is using the requests library. Install it by running: You may want to use a virtual environment to keep the dependencies for all of your Python projects separate. Step 2: Know the Format of the JSON Data you are Retrieving JSON … Read more

Home » Programming

How to use the PostgreSQL CREATE SEQUENCE Statement [Examples]

How to Create Sequences in PosgreSQL using the CREATE SEQUENCE Statement, With Examples

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

Home » Programming

How to Create/Use/Delete Stored Procedures in PostgreSQL

How to Create, Use, Delete, Update/Modify Stored Procedures in PostgreSQL

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