Skip to main content

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...

Comments

Popular posts from this blog

My 10 years in Application Development

It's been around 10 years since I started programming. May be a bit more, if I have to include my first attempts at C programming in school. But it would be a grave insult to call remembering ten lines of code and just replicating it to put a star on the screen. I also did a course on Visual basic, but it was more gimmicks that Application development. But it was truly in the year 2007 when I started my Master in Computer Applications that I found a new flare for developing applications. 
I was very late to computers in general and even when I joined my Masters, computers were not really accessible to me. But when I joined my Masters where one is actually expected to hone the skills which they already have, but in case it was were I was learning the skills, initially it was very difficult. I was not sure what computers had for me nor I had a natural flair for programming. But the thing about computers that attracted me was the visual medium it gave. Initially it was just a door t…

Changing Timezone in Postgresql, Ubuntu

Timezone and locale are integral concepts that one should be aware of when implementing a System. For example. if you create a database in PostgreSql, it will use the default System settings for the database. Lets say you have a server in UK and your client is US. If the client tries to retrieve the current date or time, it is going to show the UK time and not the US one.
Today I encountered a different issue in our client place where the Ubuntu timezone and PostgreSql timezone were showing a incorrect values. Instead of Indian Standard Time (IST) it was showing Pacific Daylight Time(PDT). These are the steps that I followed to change the timezone to IST in PostgreSql and Ubuntu.
Changing timezone in Ubuntu:
sudo dpkg-reconfigure tzdataYou will be prompted to choose the continent and then the area. Provided screen shots where we configure the location.
Use TAB key to navigate to the OK button and then press Enter.

The changes will get reflected immediately. To check it you can open a n…

கிறுக்கனின் கிறுக்கல்கள் - அழுகையின் சிறப்பு

அழுகை என்பதை வெறுத்தவன் நான்... என்னை ஒரு நாள் பிரிந்திருக்க முடியாமல் , கைப்பேசியில் நீ சிந்திய உன் கண்ணீர் ஓசை என் காதில் கேட்கும் வரை...

பின்குறிப்பு: கிறுக்கனின் கிறுக்கல்கள் எங்கிருந்தோ தழுவப்பட்டது போல் தோன்றினால் கிறுக்கன் ( நான் உண்மையை ஏற்றுக்கொள்பவன்..:) ) பொறுப்பல்ல. கிறுக்கனின் கிறுக்கல்களில் வரும் கிறுக்கல்கள் யாவும் என் சிறய மூளையில் உதித்ததே ஆகும்.  Inspirations could have been drawn from somewhere else, but its the execution that counts....Isn't it ?