Psql Commands for PostgreSql
June 6, 2011 4 Comments
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 192.168.1.65 -d shankar
Writing the query output to a file:
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:
This command will display the function in the default command line editor.
This will prompt for a filename to write.
The function will be written to the specified file in the specified location.
To execute the query or function that is stored in file,
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:
View help comment:
This command will show the syntax for the command specified.
Reset Query Buffer:
Write Query buffer to file:
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.