Psql Commands for PostgreSql

PostgreSql is an open source  Enterprise Relational Database Management System (RDBMS). To access postgres database, you can either use the graphical user interface pgAdmin or the command line interface psql. Though pgAdmin is lot easier with the GUI, for command prompt freaks, psql is THE tool for accessing database. Though it is little tough to get used to, once you are in, I am sure nothing tastes better. I have provided the basic steps here for basic operations with the database. Refer here for configuring PostgreSql for access from psql.

Connecting to database:

psql -U username -d databasename

Eg:

psql -U postgres -d shankar

Connecting to Remote Database:

psql -U username -h hostname -d databasename

Eg:

psql -U postgres -h 192.168.1.65 -d shankar

Writing the query output to a file:

\o filename

<<Query>>

Eg:

shankar=# \o /home/shankar/person.txt

shankar=#SELECT * FROM ad_client;

This command will write the output of the query to the file person.txt

This will keep recording all the outputs till we exit recording using ‘\o’ command.

\o without any argument will stop recording the outputs.

Writing the function to a file:

\ef functionname

This command will display the function in the default command line editor.

The use,

^o

This will prompt for a filename to write.

The function will be written to the specified file in the specified location.

Executing queries

To execute the query or function that is stored in file,

\i filename

Eg:

\i samplefunction.txt

This command will execute all the queries that exists in the file samplefunction.txt

List the existing database:

\l

List the tables,view in a database:

\d

View details of a table structure:

\d tablename

View help comment:

\h command

This command will show the syntax for the command specified.

Reset Query Buffer:

\r

Display History:

\s

Write Query buffer to file:

\w filename

This command will write the last query string to a file.

General help for all commands

\?

Disconnect from psql:

\q

Would like to hear feedback or help on any more commands.

Happy Working…

Advertisements

About Shankar Balachandran
Technical Manager, Openbravo operations, Kailao Consulting Private Limited

4 Responses to Psql Commands for PostgreSql

  1. Matias Colli says:

    Connecting to Remote Database specifying remote port:
    psql -U username -h hostname -d databasename -p port
    by default in postgres is 5432 (TCP).
    Matias Colli

  2. Matias Colli says:

    Ingresar a una base de datos

    psql -U nombreUsuario -d nombreBaseDatos -h SERVIDOR
    si sale el siguiente error:

    psql: FATAL: la autentificación Ident falló para el usuario «nombreUsuario»;
    entrar al archivo /var/lib/pgsql/data/pg_hba.conf y revisar los métodos de autenticación

    Matias Colli

  3. Matias Colli says:

    For connect to database:

    psql -U userName -d databaseName -h HOSTNAME
    si sale el siguiente error:

    psql: FATAL: autentification Ident fail to user «userName»;
    enter to /var/lib/pgsql/data/pg_hba.conf file and then analyze the authentications methods.

    Matias Colli
    Perito Informático Fornse
    M.N. A-128 COPITEC
    http://estudiopericialinformatico.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: