[jplush76]

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!

[Slyatslys]

3) I know that there is a:

phpPgAdmin

http://phppgadmin.sourceforge.net/

never tried it though to be honest

[rycamor]

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:

\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 .

[jplush76]

rycamor thank you for the well detailed reply! I appreciate it

[christo]

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

[rycamor]

...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.)

[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.

[rycamor]

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.

[jplush76]

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

[rycamor]

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.

[jplush76]

damn! lol I literally just place the order as I got your email notification.

[rycamor]

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.

[jplush76]

damn! I just ordered the book you recommended too. lol

good thing work is payin for them

[metaBarf]

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!!!

[christo]

we're still using redhat 6.2

eeek!