Monday, June 27, 2016

postgresql: Getting started with PostgreSQL database

I have been learning and using PostgreSQL recently for a Django app, and I have to say it's really GOOD! I have used quite a bit of MySQL and Oracle DB in my workplace, so I didn't find good reason to learn PostgreSQL in the past. Now I finally get a chance to start another new project with Django, so I decided to give it a try, and I wasn't disappointed. I will write up a brief getting started guide here for those impatient to get things moving.

Installing. If you are running on a Mac, the easiest way to try it out is just get the, you just start the App, and your DB is running! No config needed!

After you have the database installed and running, you can either connect to the default "postgres" public database by (psql command), or create your own database. To create your own database, you run this command:

bash> PATH=/Applications/$PATH
bash> createdb mydb

Now you can connect to the new database

bash> psql -d mydb

Once you connected to database, you may run SQL to create/insert/select a table:
mydb-# create table test(id serial, name varchar(200));
mydb-# insert into test(name) values('Hello');
mydb-# select * from test;

To inspect your  table columns:
mydb-# \d test;

To see all tables available:
mydb-# \dt;

To see all database available:
mydb-# \l;

To switch to another database:
mydb-# \c mydb2;

To see more help (check out \d options! it's super flexible and useful.):
mydb-# \?;

To quit the psql shell:
mydb-# \q;

To create a database user and set up password, you can do it on bash shell as well:
bash> echo "CREATE ROLE mydbuser1 WITH CREATEDB LOGIN PASSWORD 'Welcome1'" | psql
bash> echo "GRANT ALL PRIVILEGES ON DATABASE mydb TO mydbuser1" | psql

Now you may relogin again with the user. It should prompt you for password.
bash> psql -U mydbuser1 -W
Once you connected to your database again, you may check your user privileges:
mydb-# \dp;

These examples should be enough to get you setup a database for app development. The rest are just normal SQL access to database. The PostgresSQL documentation is excellent reference for all that you need:


  1. This comment has been removed by the author.

  2. Your information related to Astrologer is really very useful for me ....Thanks for sharing this informative
    For better sofa renovation contact thesofastore gives best
    Best Sofa Repair Services in Dasarahalli,Bangalore

  3. Nice blog with good content,thanks for sharing.
    Click here to get a best astrological services contact. Vashikaran Astrologer in Mahalakshmipuram

  4. Its very interesting while reading it,thank you for posting such a good article.
    For web design & development service contact Multisoft digital technologies, They have
    Web Development in Dharwad

  5. Your blog is very informative for us.
    Click here to get a best astrological services contact. Vashikaran Astrologer in Prakash Nagar

  6. Thank you for your post. This is excellent information

    abhiram astrology center. Best Astrologer In btmlayout

  7. Its a Really good post,thansk for sharing
    The best astrological services contact. Best Astrologer in Ang Mo Kio

  8. Nice blog with good content,thanks for sharing.
    For Astrological service contact Shri Durga astro center,They gives
    Vashikaran Astrologer in Koppal

  9. Thank you for sharing very, useful & informative article.
    For best astrology services contact us Vashikaran Astrologer in Basavanagudi