# Using psql

Lesson Progress
0% Complete
Topic
Materials

Let us understand how to use psql utility to perform database operations.

• psql is command line utility to connect to the Postgres database server. It is typically used for the following by advanced Database users:

• Manage Databases

• Manage Tables

• Load data into tables for testing purposes

• We need to have at least Postgres Client installed on the server from which you want to use psql to connect to Postgres Server.

• If you are on the server where Postgres Database Server is installed, psql will be automatically available.

• We can run sudo -u postgres psql -U postgres from the server provided you have sudo permissions on the server. Otherwise we need to go with psql -U postgres -W which will prompt for the password.

• postgres is the super user for the postgres server and hence typically developers will not have access to it in non development environments.

• As a developer, we can use following command to connect to a database setup on postgres server using user credentials.

psql  -h  -d  -U  -W

# Here is the example to connect to itversity_sms_db using itversity_sms_user
psql -h localhost -p 5432 -d itversity_sms_db -U itversity_sms_user -W


• We typically use psql to troubleshoot the issues in non development servers. IDEs such as SQL Alchemy might be better for regular usage as part of development and unit testing process.

• For this course, we will be primarily using Jupyter based environment for practice.

• However, we will go through some of the important commands to get comfortable with psql.

• Listing Databases - l

• Switching to a Database - c

• Get help for psql - ?

• Listing tables - d

• Create table - CREATE TABLE t (i SERIAL PRIMARY KEY)

• Get details related to a table - d

• Running Scripts - i

• You will go through some of the commands over a period of time.