How to Check Your PostgreSQL Server Version?

How to Check Your PostgreSQL Server Version?

In this PostgreSQL server tutorial, we’ll explain how to find what version of the PostgreSQL server is running on your system.

PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system.

Knowing what version of the PostgreSQL server is installed and running on your system can be important in some situations. For example, if you are installing an application that requires a specific PostgreSQL version, you’ll need to find out the version of your PostgreSQL server.

In this article, we’ll explain how to find what version of the PostgreSQL server is running on your system.

PostgreSQL Versioning

PostgreSQL releases are versioned using the following scheme:

MAJOR.MINOR

For example, in PostgreSQL 12.1, 12 is a major version, and 1 is a minor version.

  • MAJOR - Starting with PostgreSQL 10, each new major release increases the MAJOR part of the version by one, e.g., 10, 11 or 12. Before PostgreSQL 10, major versions were represented with a decimal number e.g., 9.0 or 9.6.

  • MINOR - Minor release number is the last part of the version number. For example, 11.4 and 11.6 are minor versions that are part of the PostgreSQL version 11, and 9.6.15 and 9.6.16 are part of the PostgreSQL version 9.6.

PostgreSQL major releases with new features are usually delivered once a year. Each major release is supported for 5 years.

Using the Command Line

To find out what version of PostgreSQL is running on your system, invoke the postgres command with the --version or -V option:

postgres --version

The command will print the PostgreSQL version:

postgres (PostgreSQL) 10.6

In this example, the version of the PostgreSQL server is 10.6.

If the postgres binary is not in system’s PATH, you’ll get an error saying “postgres: command not found”. This usually happens when the PostgreSQL package is not installed from the distribution’s standard repositories.

You can find the path to the binary either with the locate or find command:

sudo find /usr -wholename '*/bin/postgres'
sudo updatedb
locate bin/postgres

The output should look something like this:

/usr/lib/postgresql/9.6/bin/postgres

Once you find the path to the binary, you can use it to get the version of the PostgreSQL server:

/usr/lib/postgresql/9.6/bin/postgres -V

The version of the PostgreSQL client utility, psql can be found using the following command:

psql --version

The output will look something like this:

postgres (PostgreSQL) 10.6

psql is an interactive command-line utility that allows you to interact with the PostgreSQL server.

Using the SQL Shell

Another way to determine the PostgreSQL server version is to log in to the server SQL prompt and use an SQL statement to print out the version.

You can access the PostgreSQL shell using a GUI client like pgAdmin or with psql:

sudo -u postgres psql

The following statement displays the PostgreSQL server version along with the build information:

SELECT version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit
(1 row)

If you want to get only the PostgreSQL server version number use the following query:

SHOW server_version;
 server_version 
----------------
 10.6
(1 row)
Conclusion

In this article, we have shown several different options about how to find the version of the PostgreSQL server running on your system.

Feel free to leave a comment if you have any questions.

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch

PostgreSQL Tutorial - Learn PostgreSQL Database from Scratch. Learn how to use PostgreSQL in this full course. PostgreSQL is a general purpose and object-relational database management system. It is the most advanced open source database system widely used to build back-end systems. What is a Database? What is SQL And Relational Database? What is PostreSQL AKA Postrgres? PostgreSQL Installation. How to Create Database.

Learn PostgreSQL Tutorial - Full Course for Beginners

Learn how to use PostgreSQL Database in this full course.

PostgreSQL is a general purpose and object-relational database management system. It is the most advanced open source database system widely used to build back-end systems.

⭐️ Contents ⭐️
⌨️ (0:03:16) What is a Database
⌨️ (0:05:17) What is SQL And Relational Database
⌨️ (0:09:10) What is PostreSQL AKA Postrgres
⌨️ (0:10:53) PostgreSQL Installation (Mac OS)
⌨️ (0:14:21) PostgreSQL Installation (Windows)
⌨️ (0:17:38) GUI Clients vs Terminal/CMD Clients
⌨️ (0:21:39) Setup PSQL (MAC OS)
⌨️ (0:25:22) Setup PSQL (Windows)
⌨️ (0:30:15) How to Create Database
⌨️ (0:33:35) How to Connect to Databases
⌨️ (0:38:12) A Very Dangerous Command
⌨️ (0:41:37) How To Create Tables
⌨️ (0:45:46) Creating Tables Without Constraints
⌨️ (0:49:12) Creating Tables with Constraints
⌨️ (0:55:55) Insert Into
⌨️ (0:59:14) Insert Into Example
⌨️ (1:02:36) Generate 1000 Rows with Mockaroo
⌨️ (1:12:28) Select From
⌨️ (1:15:18) Order By
⌨️ (1:19:53) Distinct
⌨️ (1:21:59) Where Clause and AND
⌨️ (1:25:29) Comparison Operators
⌨️ (1:29:35) Limit, Offset & Fetch
⌨️ (1:32:43) IN
⌨️ (1:35:43) Between
⌨️ (1:37:45) Like And iLike
⌨️ (1:43:10) Group By
⌨️ (1:46:41) Group By Having
⌨️ (1:52:08) Adding New Table And Data Using Mockaroo
⌨️ (1:55:40) Calculating Min, Max & Average
⌨️ (1:59:48) Sum
⌨️ (2:01:55) Basics of Arithmetic Operators
⌨️ (2:05:59) Arithmetic Operators (ROUND)
⌨️ (2:09:43) Alias
⌨️ (2:12:32) Coalesce
⌨️ (2:16:15) NULLIF
⌨️ (2:20:21) Timestamps And Dates Course
⌨️ (2:23:21) Adding And Subtracting With Dates
⌨️ (2:25:58) Extracting Fields From Timestamp
⌨️ (2:27:28) Age Function
⌨️ (2:29:24) What Are Primary Keys
⌨️ (2:31:23) Understanding Primary Keys
⌨️ (2:36:26) Adding Primary Key
⌨️ (2:40:55) Unique Constraints
⌨️ (2:49:15) Check Constraints
⌨️ (2:54:45) How to Delete Records
⌨️ (3:01:36) How to Update Records
⌨️ (3:05:55) On Conflict Do Nothing
⌨️ (3:11:09) Upsert
⌨️ (3:16:41) What Is A Relationship/Foreign Keys
⌨️ (3:19:48) Adding Relationship Between Tables
⌨️ (3:25:04) Updating Foreign Keys Columns
⌨️ (3:29:30) Inner Joins
⌨️ (3:35:17) Left Joins
⌨️ (3:40:53) Deleting Records With Foreign Keys
⌨️ (3:47:27) Exporting Query Results to CSV
⌨️ (3:50:42) Serial & Sequences
⌨️ (3:57:18) Extensions
⌨️ (3:59:39) Understanding UUID Data Type
⌨️ (4:05:54) UUID As Primary Keys
⌨️ (4:16:30) Conclusion

Learn & Use PostgreSQL - Getting Started with Sequelize and PostgreSQL

Learn & Use PostgreSQL - Getting Started with Sequelize and PostgreSQL

In this tutorial, we will learn about NodeJS ORM called Sequelize interfacing with PostgreSQL Database. We are going to learn and use PostgreSQL. Sequelize is the NodeJS module which provides common functionalities and utilities to manipulate SQL based databases. Technically, it is an ORM ( Object relational mapping ) module for NodeJS. It supports the following databases: MySQL, PostgreSQL, MariaDB, SQLite, MSSQL

In this tutorial, we will learn about NodeJS ORM called Sequelize interfacing with PostgreSQL Database.

Introducing Sequelize

Sequelize is the NodeJS module which provides common functionalities and utilities to manipulate SQL based databases. Technically, it is an ORM ( Object relational mapping ) module for NodeJS. It supports the following databases:

  • MySQL
  • PostgreSQL
  • MariaDB.
  • SQLite.
  • MSSQL

We have already covered tutorials on MySQL, SQLite, and MariaDB. In this tutorial, we are going to learn and use PostgreSQL.

About PostgreSQL

PostgreSQL is considered to be the most advanced relational database system. It offers amazing features such as multi-version concurrency control, reliability, availability and strong data types backed by the 15 years of research and development.

I have not personally used it in a production system hence cannot provide any live experience. If any of you used it, please provide your feedback in comments.

We will use this to test out the Sequelize ORM. To begin with, visit the official site of PostgreSQL and download it. It supports all Major operating system.

Once you have installed it, open up the PostgreSQL admin screen. It will look something like this.


Awesome! PostgreSQL is up and running!

Getting Started with Sequelize and PostgreSQL

Let’s get started with Sequelize. To begin with, create a new folder and switch to it using Terminal.

Then run the following command to generate a new fresh NodeJS project.

npm init --y

This generate the following:

{
  "name": "sequalize-postgres",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo "Error: no test specified" && exit 1"
  },
  "keywords": [],
  "author": "",
  "license": "ISC"
}

Then, install node dependencies for Sequelize and PostgreSQL using the following command.

npm i --save sequelize pg pg-hstore

We will do the following task with Sequelize and PostgreSQL:

  • Connecting to the Database
  • Creating the model
  • Performing CRUD Operation
  • Monitoring Queries

Connecting to the Database

Create new file say app.js and add the following code.

const Sequelize = require('sequelize');
const sequelize = new Sequelize('codeforgeek', 'postgres', 'shahid', {
  host: 'localhost',
  dialect: 'postgres',
  pool: {
    max: 9,
    min: 0,
    idle: 10000
  }
});

sequelize.authenticate().then(() => {
  console.log("Success!");
}).catch((err) => {
  console.log(err);
});

Run the code using the following command.

node app.js

Since we haven’t created the database in PostgreSQL, it will return the following error ( You will receive more fields ).

{
  name: 'SequelizeConnectionError',
  message: 'database "codeforgeek" does not exist'
}

To create the database, open your PostgreSQL Admin.

Give your database a proper name and choose the user.

Now, run the same code and you should see the message as shown below.

Let’s create tables inside the PostgreSQL using Sequelize.

Creating model using Sequelize

Place this code inside the authenticate() success block.

sequelize.authenticate().then(() => {
  console.log("Success!");
  var Posts = sequelize.define('posts', {
    title: {
      type: Sequelize.STRING
    },
    content: {
      type: Sequelize.STRING
    }
  }, {
    freezeTableName: true
  });

  Posts.sync({force: true}).then(function () {
    return Posts.create({
      title: 'Getting Started with PostgreSQL and Sequelize',
      content: 'Hello there'
    });
  });
}).catch((err) => {
  console.log(err);
});

Run the code and you should be seeing queries running in the terminal. Similar to what is shown below.

Performing CRUD Operation

We have already created a data in the table using the code shown above. Let’s do some reading.

To read something from SQL based databases, we use SELECT query. Something like this.

SELECT * FROM `posts` WHERE `id`=2019;

Sequelize provides functions to perform database reads. The function is findAll() which accepts various parameters to cover clauses such as WHERE, JOINS etc and returns promise function.

To read the data from the Posts table, we will use the following code.

 Posts.findAll({}).then((data) => {
    console.log(data);
 }).catch((err) => {
    console.log(err);
 });

If we want to add WHERE clause in it, then it can be tweaked to the following code.

 Posts.findAll({
  where: {
    id: '100'
  }
 }).then((data) => {
    console.log(data);
 }).catch((err) => {
    console.log(err);
 });

You can find all read operation combinations here.

You can update any fields using update() method. Here is the sample code.

 Posts.update({
    content: 'This is a tutorial to learn Sequelize and PostgreSQL'
  }, {
    where: {
      id: 1
    }
  }).then(() => {
    console.log('Updated');
  }).catch((e) => {
    console.log("Error"+e);
  });

You can delete the rows using destroy() function.

  Posts.destroy({where: {
    id: 1
  }}).then(() => {
    console.log("Deleted");
  }).catch((e) => {
    console.log("Error"+e);
  });

This covers all four CRUD operation.

Monitoring Queries

Sequelize runs SQL queries in the background to deal with the database. In the terminal, you can view those queries as well. For example, refer to the screenshot below.

You can monitor those queries live, on each method call, Sequelize executes these queries.

Conclusion

I have tried and tested various node modules to interface with a variety of databases. It’s tough to maintain the track and keeping in touch with the development of these modules because there is too many of them. I am glad something like Sequelize came up to solve these issue. One module, multiple databases is what I needed!

Let me know if you are going to work with Sequelize or share your experience if you have already used it.

Database Design Tutorial - How to Design & Plan Database for Beginners

Database Design Tutorial - How to Design & Plan Database for Beginners

Learn how to design and plan a database for beginners. This database design course will help you understand database concepts and give you a deeper grasp of database design. Database design is the organisation of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.

Database Design Tutorial - How to Design & Plan Database for Beginners

This database design course will help you understand database concepts and give you a deeper grasp of database design.

Database design is the organisation of data according to a database model. The designer determines what data must be stored and how the data elements interrelate. With this information, they can begin to fit the data to the database model.

⭐️ Contents ⭐
⌨️ (0:00:00) Introduction
⌨️ (0:03:12) What is a Database?
⌨️ (0:11:04) What is a Relational Database?
⌨️ (0:23:42) RDBMS
⌨️ (0:37:32) Introduction to SQL
⌨️ (0:44:01) Naming Conventions
⌨️ (0:47:16) What is Database Design?
⌨️ (1:00:26) Data Integrity
⌨️ (1:13:28) Database Terms
⌨️ (1:28:28) More Database Terms
⌨️ (1:38:46) Atomic Values
⌨️ (1:44:25) Relationships
⌨️ (1:50:35) One-to-One Relationships
⌨️ (1:53:45) One-to-Many Relationships
⌨️ (1:57:50) Many-to-Many Relationships
⌨️ (2:02:24) Designing One-to-One Relationships
⌨️ (2:13:40) Designing One-to-Many Relationships
⌨️ (2:23:50) Parent Tables and Child Tables
⌨️ (2:30:42) Designing Many-to-Many Relationships
⌨️ (2:46:23) Summary of Relationships
⌨️ (2:54:42) Introduction to Keys
⌨️ (3:07:24) Primary Key Index
⌨️ (3:13:42) Look up Table
⌨️ (3:30:19) Superkey and Candidate Key
⌨️ (3:48:59) Primary Key and Alternate Key
⌨️ (3:56:34) Surrogate Key and Natural Key
⌨️ (4:03:43) Should I use Surrogate Keys or Natural Keys?
⌨️ (4:13:07) Foreign Key
⌨️ (4:25:15) NOT NULL Foreign Key
⌨️ (4:38:17) Foreign Key Constraints
⌨️ (4:49:50) Simple Key, Composite Key, Compound Key
⌨️ (5:01:54) Review and Key Points....HA GET IT? KEY points!
⌨️ (5:10:28) Introduction to Entity Relationship Modeling
⌨️ (5:17:34) Cardinality
⌨️ (5:24:41) Modality
⌨️ (5:35:14) Introduction to Database Normalization
⌨️ (5:39:48) 1NF (First Normal Form of Database Normalization)
⌨️ (5:46:34) 2NF (Second Normal Form of Database Normalization)
⌨️ (5:55:00) 3NF (Third Normal Form of Database Normalization)
⌨️ (6:01:12) Indexes (Clustered, Nonclustered, Composite Index)
⌨️ (6:14:36) Data Types
⌨️ (6:25:55) Introduction to Joins
⌨️ (6:39:23) Inner Join
⌨️ (6:54:48) Inner Join on 3 Tables
⌨️ (7:07:41) Inner Join on 3 Tables (Example)
⌨️ (7:23:53) Introduction to Outer Joins
⌨️ (7:29:46) Right Outer Join
⌨️ (7:35:33) JOIN with NOT NULL Columns
⌨️ (7:42:40) Outer Join Across 3 Tables
⌨️ (7:48:24) Alias
⌨️ (7:52:13) Self Join