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
psql -U postgres -d shankar
Connecting to Remote Database:
psql -U username -h hostname -d databasename
psql -U postgres -h -d shankar
Writing the query output to a file:
\o filename
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,
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
\i samplefunction.txt
This command will execute all the queries that exists in the file samplefunction.txt
List the existing database:
List the tables,view in a database:
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:
Display History:
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:
Would like to hear feedback or help on any more commands.
Happy Working...


Popular posts from this blog

Changing Timezone in Postgresql, Ubuntu

My 10 years in Application Development

Dissecting Openbravo 3.0 UI Architecture