Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2013
    Posts
    6

    Unanswered: Postgres easy GUI data editing??

    Hi all
    I'm helping a voluntary Org create a proper database for their members.
    The data is messy and can only be cleaned by knowledgeable members.
    I'm going to move the data from MS Excel into Postgres. This will make it easier to scan and compare sets of data for manual integrity checking. However, they don't have any sql skills and can't be expected to issue hundreds of individual update statements.

    So is there any easy to use tool or front end which will allow a user to edit data in a number of fields and then just click a button to apply the changes. I believe this is the kind of thing MS Access can do.

    Thanks in advance

    Charlie101
    Last edited by charlie101; 11-07-13 at 22:00.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    See this list of GUI tools in the Postgres Wiki:

    http://wiki.postgresql.org/wiki/Comm...eSQL_GUI_Tools
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access can connect to Postgres db's through ODBC

    if you have got bad data (and using spreadsheets to capture data I can see why). then If I were you I'd get the data into a db, doesn't need to be POSTGRES, heck plain vanilla Access will do the job assuming you don't have many concurrent users (say 15..20, above that then a server backend may well be a good idea).

    The real advantage of Access Forms is that unlike PHPPGAdmin you can build logic into the form. also you can constrain what your users do.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2013
    Posts
    6
    Quote Originally Posted by shammat View Post
    See this list of GUI tools in the Postgres Wiki:

    Community Guide to PostgreSQL GUI Tools - PostgreSQL wiki
    Thanks for the link but the point of the question was so that I didn't have to trawl through lists of tools and double check their home pages. Besides most of them seem to be 'admin' tools. Although I did see Access there which is what I think I might have to use.

  5. #5
    Join Date
    Nov 2013
    Posts
    6
    Quote Originally Posted by healdem View Post
    Access can connect to Postgres db's through ODBC

    if you have got bad data (and using spreadsheets to capture data I can see why). then If I were you I'd get the data into a db, doesn't need to be POSTGRES, heck plain vanilla Access will do the job assuming you don't have many concurrent users (say 15..20, above that then a server backend may well be a good idea).

    The real advantage of Access Forms is that unlike PHPPGAdmin you can build logic into the form. also you can constrain what your users do.
    Thanks for your reply.

    That's what I am trying to do.
    I had thought of 'Access' as an intermediate step or even a temporary 'frontend' but was hoping I could go straight to Postgres and allow them to finish cleaning the data there.

    There are a lot of things to do and only myself with Core IT skills. So I'm trying to avoid any unnecessary steps.

    The spread sheet allows them to easily edit the data but not to cross check and validate the data. A database would make the cross checking and validation much easier but not the editing (as they have no SQL skills yet).

    So am I going to have to proceed via Access?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access is strictly speaking the front end, the forms, reports and so on. unless you sepcify otherwise it will as its default storage mechanism MS JET. you can connect to POSTGRES from Access using ODBC without using JET at all

    in the same way that open office can use other data sources.

    is there a specific reason why you want to use POSTGRES?
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by charlie101 View Post
    Thanks for the link but the point of the question was so that I didn't have to trawl through lists of tools and double check their home pages. Besides most of them seem to be 'admin' tools. Although I did see Access there which is what I think I might have to use.
    Most of the tools have some kind of an "Database explorer" which lets you display and edit table data in a grid without the need to run any SQL.

    This one http://vfront.org/index.php looks quite nice (but I have never used id).

    If you don't mind some programming, OpenXava might be an option as well http://www.openxava.org/ (definitely more programming work than MS Access, but probably more scalable as well).

    And finall OpenOffice can connect to Postgres as well. Maybe you can build up some data entry forms with that.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  8. #8
    Join Date
    Nov 2013
    Posts
    6

    Thumbs up

    Quote Originally Posted by healdem View Post
    Access is strictly speaking the front end, the forms, reports and so on. unless you sepcify otherwise it will as its default storage mechanism MS JET. you can connect to POSTGRES from Access using ODBC without using JET at all

    in the same way that open office can use other data sources.

    is there a specific reason why you want to use POSTGRES?
    Thanks for your reply.

    Although we are a small organization we have quite large IT plans to leverage a number of Open Source tools: Drupal, open CRM and Email management tools. On the site we hope to have blogs and forums and mailing lists etc. So the end database requirements could be quite substantial. I come from a long Oracle background and Postgres seems to be a heavy weight Open Sources Database. We need an Open Source Database for both cost and platform reasons. Most of the software will be on Linux and Postgres seems the best option from both a scalability and functional point of view. Although I would always listen to suggestions.

    I don't suppose Open office would offer any of the editing capabilities I could find in Access?

    Thanks again for your response.

  9. #9
    Join Date
    Nov 2013
    Posts
    6

    Thumbs up

    Quote Originally Posted by shammat View Post
    Most of the tools have some kind of an "Database explorer" which lets you display and edit table data in a grid without the need to run any SQL.

    This one VFront - Front-end MySQL & PostgreSQL looks quite nice (but I have never used id).

    If you don't mind some programming, OpenXava might be an option as well AJAX Java Web Framework for Rapid Application Development of Enterprise Applications - OpenXava (definitely more programming work than MS Access, but probably more scalable as well).

    And finall OpenOffice can connect to Postgres as well. Maybe you can build up some data entry forms with that.
    Hi Shammat

    The grid editing is just what I am looking for.
    I have no problem with the SQL and I can teach the users some basic SQL but lots of individual updates by inexperienced users would be a nightmare all round.
    I don't mind some programming but I'd rather use my time working on the end products rather than cleaning the historical issues. We are only a voluntary group with very limited resources.

    Thanks very much for your input. I will take a look at vfront.org and some of the others. It not that I'm too lazy to look it's just that I'm really trying to focus my time and minimize what can sometimes be very long and even fruitless searches if you don't have any pointers.

    Thanks again.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by charlie101 View Post
    The grid editing is just what I am looking for.
    I have no problem with the SQL and I can teach the users some basic SQL but lots of individual updates by inexperienced users would be a nightmare all round.
    Personally I use this one (from the wiki list): SQL Workbench/J*-* Home

    Any result of a single table query can be edited directly in the grid (SQL Workbench/J*-* Editing data in result set ) provided the primary key is part of the result.

    You can alse define "Macros" which are simply queries that are executed and then the result will be displayed. So you could have some ready-made queries which can be executed through a menu.

    The "database explorer" (SQL Workbench/J*-* Table definition) also display the table's data and lets the user edit it directly in the grid.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  11. #11
    Join Date
    Nov 2013
    Posts
    6

    Thumbs up

    Quote Originally Posted by shammat View Post
    Personally I use this one (from the wiki list): SQL Workbench/J*-* Home

    Any result of a single table query can be edited directly in the grid (SQL Workbench/J*-* Editing data in result set ) provided the primary key is part of the result.

    You can alse define "Macros" which are simply queries that are executed and then the result will be displayed. So you could have some ready-made queries which can be executed through a menu.

    The "database explorer" (SQL Workbench/J*-* Table definition) also display the table's data and lets the user edit it directly in the grid.
    Thanks for the pointer Shamat.

    It looks good.
    I'm going to setup a lxc container and download a few and give them a testing.

    Thanks again for your advice.

    P.S I'm a complete newbie on this forum; is there are way of rating or thanking or liking replies?
    Last edited by charlie101; 11-08-13 at 14:44.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •