Top PostgreSQL Commands

20 PostgreSQL Commands You Need to Learn

Big Data / By

Aspiring to gain expertise in PostgreSQL and level up your database career? Here are the most common PostgreSQL commands for beginners to start with.

The growth of the digital world has resulted in a promising rise in the need for open-source database management systems. PostgreSQL has been one of the trusted alternatives for the same for many years. However, the recent decade has witnessed a formidable rise in the popularity of PostgreSQL. It is presently a powerful choice for a significant role in various integrated data centers worldwide.

PostgreSQL is the go-to enterprise-level RDBMS with support for JSON and SQL alongside exceptionally high reliability. So, the demand for learning about PostgreSQL and PostgreSQL commands has increased considerably in recent times. PostgreSQL has been able to resolve the cost barriers for enterprises to access exclusive features available only in commercial databases like Oracle that are highly-priced.

Check Now: Big Data Training Courses

Professionals working in the field of IT could use PostgreSQL expertise to pursue better career prospects in database-related roles. The following discussion provides you a detailed outline of some of the must-know PostgreSQL commands. Most important of all, you can reflect on the necessity of learning PostgreSQL. As a result, you can also understand the significance of learning commands for PostgreSQL before diving into a list of commands.

Why Should You Learn PostgreSQL?

There is no doubt that relational databases have served as the backbone for applications for many years, irrespective of the arrival of many new technologies. PostgreSQL provides a free and open-source RDBMS that helps various multinational enterprises worldwide for helping developers in creating fault-tolerant environments while building apps.

The features of PostgreSQL, such as compatibility, unparalleled performance, replication, SQL, compliance, security features, geo-tagging, and other notable features, have promoted its popularity and demand. So, if you are fluent enough to use the PostgreSQL commands effectively, then you can leverage all these features for improving your capabilities as database administrators.

As a result, you can leverage the most from PostgreSQL while also improving your credibility in various database-related roles. PostgreSQL is also a prominent skill requirement in the field of data science, which is undoubtedly a treasure trove of career opportunities. Basically, everything related to data would be within your access with PostgreSQL expertise, and the best way to start would be to learn PostgreSQL commands. Here is an outline of the general commands in PostgreSQL. You must learn to develop your fluency in PostgreSQL.

Top PostgreSQL Commands

The main tool for extracting the best potential of PostgreSQL is the command-line interface. If you know PostgreSQL commands well, then you can use PostgreSQL for achieving task objectives faster. In addition, fluency in commands also helps professionals in clearing up new opportunities for innovative use of PostgreSQL for different use cases.

As a result, professionals can showcase better potential to make the best of practical applications of PostgreSQL. Here is an outline of the top common PostgreSQL commands that will help every beginner dive deeper into the world of PostgreSQL.

1. Connecting to a Specific PostgreSQL Database

You can use the following command to connect with a specific database pertaining to a specific user. After you finish pressing ‘Enter,’ PostgreSQL would demand a password for the user.

psql –d database –U user –W

If you want to connect with a database ‘students’ related to the user ‘academic,’ then you can use the following command,

C:\Program Files\PostgreSQL\9.5\bin>psql -d students -U academic -W

Password for user academic:

students=#

2. Connecting with PostgreSQL Database on Another Database

On the other hand, if users want to connect with the database residing on another host, then they can use the –h option. The example for such case would be as follows,

psql –h host –d database –U user –W

3. Connecting to a Database in SSL Mode

Users can also implement SSL mode for the connection by specifying as mentioned in the following command,

psql –U user –h host “dbname=dbsslmode=require”

4. Listing the Available Databases

Users can find this command as a general entry in almost any PSQL commands cheat sheet. You can use the following command for listing all the available databases in the existing PostgreSQL database server,

\l

5. Listing All the Available Tables

Users should also remember PostgreSQL commands with this functionality. The following command can help you list out all the tables in the existing database.

\dt

However, it is also important to remember that this command can display only the tables in the database connected currently.

SQL is the base of all the database languages. Enroll in the SQL Basics Training Course and start learning.

6. Switching Connection to a New Database

After connecting to a database, users could shift the connection to another database under a new user that the ‘user’ specifies. In addition, the previous connection is terminated, and the omission of the ‘user’ parameter implies that the existing ‘user’ is just implied. Here is the example of the command for switching a connection to a new database. The following command helps in connecting to the ‘students’ database under the ‘academic’ user,

academic=# \c students

You are now connected to database “students” as user “academic.”

students=#

7. Listing the Available Views in a PostgreSQL Database

The following PostgreSQL command can help you list all the available views in the existing database,

\dv

8. Listing the Available Schema

Users can find all the available schemas in the database they are presently connected to through the following command,

\dn

9. Describing a Table

Users should also know about the PostgreSQL commands for describing the specifications of a table. The following command can help in describing a table with the help of attributes such as column, modifiers of columns and type of table,

\d table_name

10. Listing Out the Available Functions

Functions are an important component in the operations of PostgreSQL. You can utilize the following command for finding out the list of available functions in a PostgreSQL database,

\df

11. Querying Data from a PostgreSQL Table

You can use PostgreSQL by querying or basically asking a question to the database. Users can use the following command for querying data from a table for the two specific columns c1 and c2,

SELECT c1, c2 FROM t;

You can also use the following command for querying all the rows and columns from a specific table,

SELECT * FROM t;

Users can also choose to implement certain conditions for querying data while filtering out certain rows with the following command,

SELECT c1, c2 FROM t

WHERE condition;

You should also know the following command for querying specific rows from a table along with the above-mentioned PostgreSQL commands,

SELECT DISTINCT c1 FROM t

WHERE condition;

12. Querying Data from Multiple Tables in PostgreSQL

Users should also learn the PostgreSQL commands that help in querying data from various tables. Here is an example of a command for querying data from two tables with an inner join,

SELECT c1, c2

FROM t1

INNER JOIN t2 ON condition;

You can query data from two tables with left join by using the following command,

SELECT c1, c2

FROM t1

LEFT JOIN t2 ON condition;

Users can also make the most of the following command for querying data from two tables with right join,

SELECT c1, c2

FROM t1

RIGHT JOIN t2 ON condition;

You can also perform a total outer join of two tables for querying data from them with the help of the following command,

SELECT c1, c2

FROM t1

FULL OUTER JOIN t2 ON condition;

13. Index Management

The PostgreSQL commands for managing indexes are one of the most advanced topics in ensuring the database management expertise with PostgreSQL. Here is an example command for creating an index of a table‘t’ on c1 and c2,

CREATE INDEX idx_name

ON t(c1,c2);

The following command can help users in creating a specific index of the table ‘t’ on c3 and c4,

CREATE UNIQUE INDEX idx_name

ON t(c3,c4);

You can also use the following command for deleting an index,

DROP INDEX idx_name;

14. Creation of Table in PostgreSQL

Tables are undoubtedly a significant aspect of PostgreSQL utilization. So, it is important to know about the commands for creating a table. Here is the example for PostgreSQL commands for creating a new table that has three distinct columns,

CREATE TABLE t (

            id SERIAL PRIMARY KEY,

            subject VARCHAR NOT NULL,

            grade NUMERIC (10,2) DEFAULT 0

);

15. Management of Tables in PostgreSQL

Professionals with expertise over a wide range of PostgreSQL commands for managing tables can achieve better control over the language. You can use the following example commands for various operations on tables.

Here is an example code for deleting a table from a concerned database

DROP TABLE t CASCADE;

You can delete a column ‘c’ from a table with the following command,

ALTER TABLE t DROP COLUMN c;

Users can add a new column to a specific table by leveraging the following command,

ALTER TABLE t ADD column;

You also have the option for adding or removing constraints on a table by using the following commands respectively,

ALTER TABLE t ADD constraint;

ALTER TABLE t DROP constraint;

If you want to remove all data in a specific table, then you can use the following command,

TRUNCATE TABLE t CASCADE;

Users can also rename tables or columns by using the following commands respectively,

ALTER TABLE t1 RENAME TO t2;

ALTER TABLE t1 RENAME c1 to c2;

16. Commands for Management of Views in PostgreSQL

PostgreSQL users should be acquainted with the significance of views in the management of relational databases. Here are the must-know PostgreSQL commands for management of views for faster and efficient use of database management systems.

The following command example can help you develop a new view that includes c1 and c2,

CREATE VIEW v(c1,c2)

AS

SELECT c1, c2

FROM t;

Users can also prefer to associate a check option while creating a new view. The following command example can help you achieve the same,

CREATE VIEW v(c1,c2)

AS

SELECT c1, c2

FROM t;

WITH [CASCADED | LOCAL] CHECK OPTION;

You could also create a temporary view with the help of the following command,

CREATE TEMPORARY VIEW v

AS

SELECT c1, c2

FROM t;

In addition, users could also develop a recursive view in PostgreSQL with the following command,

CREATE RECURSIVE VIEW v

AS

select-statement – anchor part

UNION [ALL]

select-statement; -- recursive part

The final mention among PostgreSQL commands for managing views is the command for deleting a view. The example command for deleting a view is as follows,

DROP VIEW view_name

17. User-related PostgreSQL Commands

The PostgreSQL commands related to users are essential for maintaining efficient and faster processing. The commands help in strengthening the control over user administration for database management professionals. Here is a command for listing the users in the current database,

\du

The following command example would list out a username if it is present,

\du_username_

 You can also create a role having an already existing username by using the following command,

create role _test1_

You can also add username and password while creating a role with the help of the following command,

create role _test2_ noinherit login password _password_;

It is also possible to switch roles with the help of the following command,

Grant _test2_ to _test1_;

You could also change the role for a current session to _test_ with the following command,

set role _test_

One of the most important PostgreSQL commands for user management is the following mention for listing out the entire user mapping on the server,

\deu+

18. Utilizing SQL Constraints

Professionals aspiring for expertise in PostgreSQL must also have comprehensive fluency in using SQL constraints to their advantage. Here are some of the notable PostgreSQL commands that can help you make the most of SQL constraints in various use cases.

You can establish c1 and c2 as the primary key for a table by using the following command,

CREATE TABLE t(

            c1 INT, c2 INT, c3 VARCHAR,

            PRIMARY KEY (c1,c2)

);

Users can also opt for setting a specific column ‘c2’ in the form of a foreign key by using the following command,

CREATE TABLE t1(

            c1 SERIAL PRIMARY KEY,

            c2 INT,

            FOREIGN KEY (c2) REFERENCES t2(c2)

);

You can also use the following command for establishing uniqueness in the values of c1 and c2,

CREATE TABLE t9

            c1 INT, c1 INT,

            UNIQUE (c2,c3)

);

The following PostgreSQL command can help in establishing the values in a column ‘c2’ as non-NULL,

CREATE TABLE t(

            c1 SERIAL PRIMARY KEY,

            c2 VARCHAR NOT NUL

);

Users could also improve the control of the constraints in PostgreSQL databases by ensuring that c1 is greater than 0, and the values in c1 are greater than or equal to that in c2. Here is the example code for achieving the same,

CREATE TABLE t(

            c1 INT, c2 INT,

            CHECK (c1> 0 AND c1 >= c2)

);

19. Setting Operations for SQL Databases

PostgreSQL commands for SQL databases would also help professionals develop their fluency in leveraging for PostgreSQL to address various use cases productively.

The following example would help you in combining the result set from two or multiple queries by using the UNION operator,

SELECT * FROM table1

UNION

SELECT * FROM table2;

Users can also find an intersection of the result sets of two queries by the following command,

SELECT * FROM table1

INTERSECT

SELECT * FROM table2;

Another notable use of PostgreSQL commands could also help in excluding a result set from the queries with the help of EXCEPT operator. The following example command can show how,

SELECT * FROM table1

EXCEPT

SELECT * FROM table2;

20. Data Modifications

If you want to work with PostgreSQL, then you have to deal with data. So, the PostgreSQL commands for data modifications are very crucial for all PostgreSQL professionals and learners. Here are some of the commands for data modification that you would find in a PSQL commands cheat sheet.

The example command for inserting a new row into a specific table is as follows,

INSERT INTO table(column1, column2,…)

VALUES (values_1, value_2,…);

Users can find the following command useful for updating the data for all rows,

UPDATE table_name

SET column_1 = value_1,

            …;

The following command example can showcase the use of PostgreSQL commands for inserting multiple rows in a specific table.

INSERT INTO table_name(column1, column2,…)

VALUES (value_1, value_2, …),

                   (value_1, value_2, …),

                   (value_1, value_2, …)…

The following command is also important as it can help you delete all the rows of a specific table,

DELETE FROM table_name;

Users could also find the following helpful PostgreSQL command for updating data according to the requirements of a specific set of rows according to conditions specified with a ‘WHERE’ clause.

UPDATE table

SET column_1 = value_1,

        …

WHERE condition;

Users can also find PostgreSQL commands for deleting specific rows according to specific conditions as the following example,

DELETE FROM table_name

WHERE condition;

Preparing for a Big Data Interview? Go through these frequently-asked Big Data Interview Questions and get ready to ace the interview.

Conclusion

On a final note, it is clearly evident that PostgreSQL provides efficient functionalities with very simple coding requirements. The easy-to-remember PostgreSQL commands, along with the regular hands-on practice, could provide the much-needed confidence for beginners. In addition, the cheat sheet of commands for PostgreSQL can also be a great asset for database administrators and developers.

Therefore, knowledge of commands for using PostgreSQL can give substantial advantages to the preparation of candidates for different job interviews. Expertise in commands of PostgreSQL also strengthens the hands-on experience of aspiring database professionals. With the demand for PostgreSQL sustaining the arrival of new database management technologies, candidates should start mastering more commands right now!

If you’re preparing to enhance your Big Data skills

About Aditi Malhotra

Aditi Malhotra is the Content Marketing Manager at Whizlabs. Having a Master in Journalism and Mass Communication, she helps businesses stop playing around with Content Marketing and start seeing tangible ROI. A writer by day and a reader by night, she is a fine blend of both reality and fantasy. Apart from her professional commitments, she is also endearing to publish a book authored by her very soon.

Related Posts

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top