Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Los Angeles, CA
    Posts
    85
    Rep Power
    12

    PostGRESQL Features I'm looking for


    Hey all,
    I'm looking to move our site from mysql to postgresql to take advantage of triggers, transactions, etc that mysql does not support.

    There are a couple things I have come to rely on with Mysql that I'm hoping postgres can handle as well but I haven't found any info on the web yet...

    1. Load data infile
    - in mysql I can take an excel file, save it as tab deliminated and upload it right into a table using load data infile. Does postgres have something like that?

    2. select * into outfile
    in mysql I can select the contents of a query right into a text file on my server.

    3. PHPMyAdmin
    does postgres have a graphical interface like mysql has phpmyadmin? It makes administrating so much faster.

    Thanks everyone!
    ~jim~
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    31
    Rep Power
    11
    3) I know that there is a:

    phpPgAdmin

    http://phppgadmin.sourceforge.net/

    never tried it though to be honest
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    Re: PostGRESQL Features I'm looking for


    Welcome to team PostgreSQL!

    1. Load data infile
    - in mysql I can take an excel file, save it as tab deliminated and upload it right into a table using load data infile. Does postgres have something like that?

    2. select * into outfile
    in mysql I can select the contents of a query right into a text file on my server.
    The COPY command is a very straightforward way to move data between files and the database. It accomplishes both of these. There are also plenty of extra command-line options, and special 'non-SQL' commands available from the 'psql' command prompt (type '\h' and '\?' inside psql for more details). Extremely cool example: you can use the standard "backtick" operator (`) to capture the output of shell commands to variables inside psql, as in:
    Code:
    \set foo '\\'' `ls -lah /home` '\\''
    INSERT INTO filelog(directory,dirlist) VALUES ('/home', :foo);
    This example would set the variable foo, concatenate a single quote, the output of "ls -lah /home", and another single quote, making it a valid text entry. Then anywhere you use :foo in your SQL statement, it gets substituted for your /home directory list. Makes for some very interesting shell script possibilities, no?


    3. PHPMyAdmin
    does postgres have a graphical interface like mysql has phpmyadmin? It makes administrating so much faster.
    As Slyatslys mentions, phpPgAdmin is available, as well as several other interfaces. phpPgAdmin was completely changed recently for versions 7.3+ of PostgreSQL (because of architectural changes in PostgreSQL). The decision was made to scrap the old codebase and start again. Unfortunately, this means that many of phpMyAdmin's easy features are not yet implemented, so it might be a little frustrating to use at the moment. There are several developers (I might be contributing also ) working hard at restoring the full feature set.

    Meanwhile, I strongly recommend you try the beta version of
    PGAdmin III. This is a much more "serious" tool for database management, and since version III, is now capable of running on many desktop platforms.

    Check out many contributed modules and programs for PostgreSQL at http://gborg.postgresql.org/. Also, look for an article on migrating from MySQL to PostgreSQL by yours truly soon .
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Los Angeles, CA
    Posts
    85
    Rep Power
    12
    rycamor thank you for the well detailed reply! I appreciate it
  8. #5
  9. Introspective
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    3,317
    Rep Power
    110
    rycamor - good reply!

    Just whilst I'm thinking of it.. and I admit I haven't googled particularly hard for this, does Postgres have a fulltext indexing features like the MyISAM tables in mysql? I have an application where speed of retrieval from a large database is essential. I wouldn't consider Postgres if I didn't think it could match a heavily indexed/optimised mysql equivalent on select.

    jplush76, from what I have read a move to postgres also unravels other great features such as Views, stored procedures, data cubes, sequences, row-level locking and more - quite an impressive array of toys for someone who really cares about their data!

    christo

    edit: rycamor: I can't wait to see that article, dude
    Last edited by christo; September 9th, 2003 at 02:34 PM.
    This is me: http://chris.uk.com
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    ...does Postgres have a fulltext indexing features like the MyISAM tables in mysql? I have an application where speed of retrieval from a large database is essential. I wouldn't consider Postgres if I didn't think it could match a heavily indexed/optimised mysql equivalent on select.
    For sure . There are some great extras in the PostgreSQL source download. Just unpack the tar.gz archive and go into the directory called 'contrib'. There, you will see several text matching modules, such as 'fulltextindex', 'fuzzystringmatch', and the popular 'tsearch', which I think is the most commonly used fulltext system for PostgreSQL.

    (By the way, just browse through the other directories in /contrib -- there's some phenomenal stuff.)
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Los Angeles, CA
    Posts
    85
    Rep Power
    12
    I'm making the move to get into all that good stuff, triggers, stored proc, transaction support.

    It would save alot of headaches I've had to endure using mysql for our ecom site.
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61

    Q


    Originally posted by jplush76
    I'm making the move to get into all that good stuff, triggers, stored proc, transaction support.

    It would save alot of headaches I've had to endure using mysql for our ecom site.
    Absolutely. Also, don't forget referential integrity, domains, check constraints, and schemas. There are some amazing benefits to be had.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Los Angeles, CA
    Posts
    85
    Rep Power
    12
    u guys recommend any good books? I'm going to get this one to start with:

    http://www.amazon.com/exec/obidos/tg...lance&st=books
  18. #10
  19. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Jplush--

    I would recommend against that book, and most others for one very good reason: PostgreSQL changed significantly this past year (with version 7.3+) and most of these books don't deal with those changes. I think the only recent book on PostgreSQL is PostgreSQL, by SAMs press. I can't comment about the book, not having read it, but it is at least published in 2003, so it should cover these features.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Los Angeles, CA
    Posts
    85
    Rep Power
    12
    damn! lol I literally just place the order as I got your email notification.
  22. #12
  23. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Originally posted by jplush76
    damn! lol I literally just place the order as I got your email notification.
    lol...

    Don't worry... This book will still get you a long way. It's not that features have changed much, but new features such as schema and domain have been added, which make PostgreSQL much more of an enterprise DB server. Actually, I browsed the TOC for the "PostgreSQL" book and was disappointed to see that they don't even bother to deal with domains yet, so even they aren't completely up-to-date.

    Anyway, for the REAL questions, just ask away in this forum, and we will be happy to tackle them.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2002
    Location
    Los Angeles, CA
    Posts
    85
    Rep Power
    12
    damn! I just ordered the book you recommended too. lol

    good thing work is payin for them
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    for pgadminIII it says you should use a recent linux distribution I guess I will have to wait and try it at home... we're still using redhat 6.2 at work!!!
  28. #15
  29. Introspective
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Nov 2001
    Location
    London, UK
    Posts
    3,317
    Rep Power
    110
    we're still using redhat 6.2
    eeek!
    This is me: http://chris.uk.com
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo