1

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

Improve this question
6
  • 2
    sql is sql. the main difference is the syntax/availability of the various helper functions, e.g. sum(), count() etc... If you understand the basic underpinnings of a relational database, then postgres will work "just fine" - you'll just have some basic syntax variations to account for. Commented Apr 3, 2014 at 19:41
  • 2
    For poking around make use of pgadmin it's gui and will make it quick for you to learn in. The command line tool which is really nice is psql, but if your use to gui you will probably find pgadmin to be helpful. Commented Apr 3, 2014 at 19:42
  • @Bob pgadmin looks like most of what I would like. Thank you Commented Apr 3, 2014 at 20:19
  • 1
    OpenOffice.org Base may also be worth looking into, as it has a forms tool, etc. Most people who use PostgreSQL do so via scripts and programs they've written, rather than canned tools - Ruby on Rails being one hugely popular example despite the limitations it imposes. PostgreSQL is an engine for data, it leaves the user interface to other things - more like MS SQL Server than MS Access. Commented Apr 3, 2014 at 23:31
  • @CraigRinger So is PostgreSQL a good place to create a data model? Or should I have the tables and relationships all ironed out before I attempt to put it into an RDBMS. I have used Access as a kind of Rapid Application Development, changing the tables and structure as I go and I suppose that's what I'm hoping to do with PostgreSQL too. I think this has turned into a different question... Commented Apr 4, 2014 at 13:12

3 Answers 3

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

This is great, I've begun to read the books you suggested and they are very helpful. I'd like to mark this one as correct also.
That's great. Just take things slow. I generally install the latest version of PostgreSQL and then download the test databases that follow the book along. They usually have a link somewhere in the book which lets you import a .sql file into a newly created database and you will feel like Superman!
2

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/

Improve this answer

1 Comment

Thanks, this is what I was hoping for. I've come across this before but had passed it over, I think confusing it with psql.
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!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.