PostgreSQL basic commands

PostgreSQL Commands and Queries

We are working in Ubuntu;

• Check if there is PostgreSQL installed by using the command:

service postgresql status

• Check if psql command line for postgreSQL is installed:

psql -V

• Access posegreSQL command-line interface:

sudo su - postgres

• Connect to a database:

once we are in the postgreSQL command line we can connect to a Database using command:

psql DATABASE-NAME USERNAME;

Use database and user names instead of DATABASE-NAME USERNAME. If the username is forgotten try the command without username.

• Show databases command:

There are two ways to show all the databases.

1.

\l

2.

SELECT datname FROM pg_database;

• Switch / Connect to a different database:

\c DATABASE-NAME

Show Tables:

\dt

For more details on table:

\dt+

• Check the table and column details and description:

Three commands can be used in order to see the table description;

1.
\dt TABLE-NAME

2.
\dt+ TABLE-NAME

3.
SELECT
   table_name,
   column_name,
   data_type
FROM
   information_schema.columns
WHERE
   table_name = 'TABLE-NAME';

Change the TABLE-NAME to the name of the table that you want to check details for.

• Get information about which user is connected to the DB now:

\conninfo

 

• Check current user:

SELECT current_user;

 

• Connect / Change the user

\c - user-name

Change the user-name with the user you want to connect.

 above command will change the user connection to the current database. If we want to connect to a different database as a new / different user then use:

\c DATABASE-NAME user-name

Change the user-name and DATABASE-NAME to the desired user and database names.

• Also to set the current session and user:

SET SESSION AUTHORIZATION user-name;

 

• To check current user and session:

SELECT session_user, current_user;