Want to learn PostgreSQL, used to Microsoft Access

Question

I'm trying to grapple with PostgreSQL but am finding it a strange beast compared to Microsoft Access. I'm sure it's just a matter of understanding the new model and would like to learn it hands on. I have an idea for a database and would like to implement it, but I'm having trouble figuring out the best tools to use. I can set up postgres on my computer, but I want to be able to develop and play around with the data model. Because PostgreSQL doesn't come with a visual environment (like Access), I'm a bit lost.

My guess is that I just need to sit down and read the manual front to back, and then tackle learning how to develop a database with PostgreSQL. With Access, that wasn't necessary, I was able to open it and go, studying along the way. Maybe my thinking is too entrenched in Microsoft to be able to pick up a new way without a fair bit of study.

I'm wondering if I could see how everything interacts (tables, schemas, views, users, etc...), and be able to edit them in a mostly point and click environment, if the transition would be easier. This is all on the side, so if it's going to be as difficult as it looks, than I'll have to put this off indefinitely and stick with Access, which I know.

For clarification, I chose PostgreSQL because of cost (free) and strong support in GIS Software through the PostGIS add-on (QGIS, GRASS, even ArcGIS), which is my day job.

Could someone make any suggestions? Thanks

Answer 1

After following up on a couple of the previous answers I found Pony ORM which is perfect for what I want to do. I didn't think there would be something this suitable, and that creates Python code as well. Fantastic!

With this I can fool around with the data structure until it's right with their ER Diagram Editor (Thanks @CraigRinger for pointing me in that direction), and it's in a way I can visualize easily. Then use the generated Python code to create it in PostgreSQL so I can begin adding data. Of any of the languages involved, I know Python best, so this fits where I'm coming from really well.

Down the road I'll be using spatial data with PostGIS, and most the programs I use (ArcGIS, QGIS) have Python support.

Thank you for all the comments to help me come to this!

Answer 2

As said above, PostgreSQL is just the engine to manage / store the data. I highly recommend PostgreSQL 9 Administration Cookbook (Amazon.com) and then Ben Forta's Teach Yourself SQL in 10 Minutes (Amazon.com). Both books are very very easy to follow and will get you working w/ basic PostgreSQL configuration / administration tasks as well as understanding CRUD operations using proper, clean, efficient ANSI SQL statements.

PostgreSQL is by far my favorite RDBMS (database server software) compared to MySQL or anything else. I strongly urge you to download VirtualBox too and create a VM on Linux and run PostgreSQL. You can run PostgreSQL on Windows or OS X however I feel you don't get any real world examples / experience w/ that since every production server I've worked on have ALL been Linux based.

Answer 3

Postgres has a nice cross platform GUI tool called pgadmin. It's not the exact same thing as Access, but gives a really nice GUI interface to the database and all the objects. http://www.pgadmin.org/