7 ms·

Article is obviously for PostgreSQL developers BUT my gripe as a user (coming from MySQL).....the hardest part about transitioning from MySQL to PostgreSQL isn'

by mr_gaga 11y ago

Article is obviously for PostgreSQL developers BUT my gripe as a user (coming from MySQL).....the hardest part about transitioning from MySQL to PostgreSQL isn't PostgreSQL itself but transitioning from phpMyAdmin to phpPgAdmin. phpPgAdmin seems like it's trying to make my life difficult.

jperras 11y ago
Stop using web-based admin tools, and learn how to use the CLI interface. I mean this in the best, nicest possible way; we've all been there. But learning how to use the CLI will pay dividends in the short and long run.
- njs12345 11y ago
  Or, as a middle ground, JetBrains have a nice SQL IDE: https://www.jetbrains.com/dbe/ https://www.jetbrains.com/dbe/
  - javajosh 11y ago
    Yes, it is very good and works with anything not just PG. Of course pgAdmin is quite good too.
  - jimmar 11y ago
    But not released yet? I clicked the link saw that I could only get it through an early access release program.
    - _petronius 11y ago
      For some reason the email you get sent when you sign up for the early-access program also trips Thunderbird's scam warning feature (in addition to the form being submitted over HTTP, despite the site being served over HTTPS). I wonder if that's because the message contains the string "Hi Friend"?
    - z-e-r-o 11y ago
      Google "jetbrains dbe dmg" and it'll link you their knowledge base article with download links to the latest version. It's seriously a fantastic program!
- fein 11y ago
  CLI is obviously the best choice, but as a nice middle ground, PGAdmin is helpful in making the transition.
- craigkerstiens 11y ago
  If you're curious to get hands on with the CLI here's a couple of shameless plugs on psql the Postgres CLI (http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/ http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos... and http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/ http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/). If you're really stuck on a GUI editor give JackDB a try if you want something web based or Postico a try if you want a native GUI.
  - pjungwir 11y ago
    I've used psql for years but never knew about \e. Lately to work on big queries I've been doing a lot of \i foo.sql (while editing foo.sql in a separate window), but maybe I'll see if I like \e better. Thanks for sharing!
- philh 11y ago
  I'm skeptical that the CLI can do everything I'd want, but here goes. Is it as easy to use as phpMyAdmin when I want to: - Change the sort order? (PMA: Click a column. CLI: Edit your query to add an ORDER BY clause?) - Edit a value? (PMA: Double-click and edit. CLI: Check the primary key and write an UPDATE query? Hope not to make a typo when writing the key.) - Look up the row corresponding to a foreign key? (PMA: click it. CLI: write a SELECT query?) - View the extreme values of a result set? I.e. I run a query which returns 200 results, and I want to look at the first few and the last few. (PMA: home and end. CLI: scroll?)
  - moonchrome 11y ago
    >- View the extreme values of a result set? I.e. I run a query which returns 200 results, and I want to look at the first few and the last few. (PMA: home and end. CLI: scroll?) SELECT TOP ORDER BY ?
    - philh 11y ago
      You mean, run the (possibly expensive) query twice with different sort orders, and not see the middle ones at all? If that is what you mean, I think the advantage remains with PMA. Edit to clarify: I'm thinking here in terms of data exploration. I want to look at the whole result set, but the most interesting ones are likely to be at the top and bottom. With PMA, I can get the whole result set, and nothing else, on a single page. So I just scroll to the top or bottom of the page to see the extreme values. With a CLI, it can be difficult to find where the output of that specific query started. (This is a trade-off, because the CLI allows you to see results from your previous queries.) If there was a CLI feature like 'view the previous result set in less, without re-running it', I think that would satisfy me on this point. If you have to re-run it, that's less good, but it would beat scrolling.
      - moonchrome 11y ago
        I'm not a DB/pg guy but I think you want SELECT INTO TEMP which will create a temp table from your query (gets dropped at the end of the session).
Thaxll 11y ago
If you're serious you never use a tool like phpMyAdmin, SQLYog and the like are way better...
- cwyers 11y ago
  I agree that SQLYog is a far sight better than phpMyAdmin, but it doesn't work with Postgres. What's the SQLYog for Postgres people?
gdulli 11y ago
That's like passing up a great car because you don't like the color of the steering wheel.
- jordanlev 11y ago
  mm... I'd say it's more like passing up a great car with manul transmision because you only know how to drive an automatic.
untog 11y ago
Despite the negative replies you've gotten, I would actually agree that PostgreSQL admin tools are, by and large, not as good as MySQL ones. But that is changing - and I was happy to drop $10 on Navicat Essentials for PostgreSQL a while back.
giaour 11y ago
The community tooling around MySQL is impressive compared to what's available for Postgres. I've heard the same from people who have switched from SQL Server and miss the SQL Server Workbench suite. My hunch is that this gap exists because MySQL is often the default DB choice for new developers, whereas Postgres tends to be used by people already familiar with database CLI tools. Once you learn how to use the command line effectively, you'll be able to work a lot faster and won't want to use a web interface again, which is to say that you will become yet another reason for phpPgAdmin to be neglected.
metaphorm 11y ago
http://www.pgadmin.org/ http://www.pgadmin.org/ this is a really useful GUI tool for interacting with a postgres database.
dorfsmay 11y ago
Have you tried pgAdmin: http://www.pgadmin.org/ http://www.pgadmin.org/
- dorfsmay 11y ago
  For more generic use, I really like DBeaver, as it works with most RDBMS, including Oracle, SQLite,PostgreSQL and MySQL: http://dbeaver.jkiss.org/ http://dbeaver.jkiss.org/
the_rosentotter 11y ago
For a web interface, try TeamPostgreSQL[1]. Beats phpMyAdmin, imho. [1]: http://www.teampostgresql.com http://www.teampostgresql.com
davidgerard 11y ago
Your real problem is that MySQL won the network effect early, even though it's just terrible. The only thing we let people use MySQL for now is third-party frameworks that pretty much require it (Magento, MediaWiki, WordPress, Drupal). In fact, we're moving our Oracle to Postgres, with great success. (Oracle is fantastic at storing your data and giving it back, but everything else about it is the opposite of "agile".)
lazyant 11y ago
Navicat, DB Visualizer, pgAdmin

by mr_gaga 11y ago

jperras 11y ago

Stop using web-based admin tools, and learn how to use the CLI interface. I mean this in the best, nicest possible way; we've all been there. But learning how to use the CLI will pay dividends in the short and long run.

njs12345 11y ago
Or, as a middle ground, JetBrains have a nice SQL IDE: https://www.jetbrains.com/dbe/ https://www.jetbrains.com/dbe/
- javajosh 11y ago
  Yes, it is very good and works with anything not just PG. Of course pgAdmin is quite good too.
- jimmar 11y ago
  But not released yet? I clicked the link saw that I could only get it through an early access release program.
  - _petronius 11y ago
    For some reason the email you get sent when you sign up for the early-access program also trips Thunderbird's scam warning feature (in addition to the form being submitted over HTTP, despite the site being served over HTTPS). I wonder if that's because the message contains the string "Hi Friend"?
  - z-e-r-o 11y ago
    Google "jetbrains dbe dmg" and it'll link you their knowledge base article with download links to the latest version. It's seriously a fantastic program!
fein 11y ago
CLI is obviously the best choice, but as a nice middle ground, PGAdmin is helpful in making the transition.
craigkerstiens 11y ago
If you're curious to get hands on with the CLI here's a couple of shameless plugs on psql the Postgres CLI (http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/ http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Pos... and http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/ http://www.craigkerstiens.com/2013/02/21/more-out-of-psql/). If you're really stuck on a GUI editor give JackDB a try if you want something web based or Postico a try if you want a native GUI.
- pjungwir 11y ago
  I've used psql for years but never knew about \e. Lately to work on big queries I've been doing a lot of \i foo.sql (while editing foo.sql in a separate window), but maybe I'll see if I like \e better. Thanks for sharing!
philh 11y ago
I'm skeptical that the CLI can do everything I'd want, but here goes. Is it as easy to use as phpMyAdmin when I want to: - Change the sort order? (PMA: Click a column. CLI: Edit your query to add an ORDER BY clause?) - Edit a value? (PMA: Double-click and edit. CLI: Check the primary key and write an UPDATE query? Hope not to make a typo when writing the key.) - Look up the row corresponding to a foreign key? (PMA: click it. CLI: write a SELECT query?) - View the extreme values of a result set? I.e. I run a query which returns 200 results, and I want to look at the first few and the last few. (PMA: home and end. CLI: scroll?)
- moonchrome 11y ago
  >- View the extreme values of a result set? I.e. I run a query which returns 200 results, and I want to look at the first few and the last few. (PMA: home and end. CLI: scroll?) SELECT TOP ORDER BY ?
  - philh 11y ago
    You mean, run the (possibly expensive) query twice with different sort orders, and not see the middle ones at all? If that is what you mean, I think the advantage remains with PMA. Edit to clarify: I'm thinking here in terms of data exploration. I want to look at the whole result set, but the most interesting ones are likely to be at the top and bottom. With PMA, I can get the whole result set, and nothing else, on a single page. So I just scroll to the top or bottom of the page to see the extreme values. With a CLI, it can be difficult to find where the output of that specific query started. (This is a trade-off, because the CLI allows you to see results from your previous queries.) If there was a CLI feature like 'view the previous result set in less, without re-running it', I think that would satisfy me on this point. If you have to re-run it, that's less good, but it would beat scrolling.
    - moonchrome 11y ago
      I'm not a DB/pg guy but I think you want SELECT INTO TEMP which will create a temp table from your query (gets dropped at the end of the session).

Thaxll 11y ago

If you're serious you never use a tool like phpMyAdmin, SQLYog and the like are way better...

cwyers 11y ago
I agree that SQLYog is a far sight better than phpMyAdmin, but it doesn't work with Postgres. What's the SQLYog for Postgres people?

gdulli 11y ago

That's like passing up a great car because you don't like the color of the steering wheel.

jordanlev 11y ago
mm... I'd say it's more like passing up a great car with manul transmision because you only know how to drive an automatic.

untog 11y ago

Despite the negative replies you've gotten, I would actually agree that PostgreSQL admin tools are, by and large, not as good as MySQL ones. But that is changing - and I was happy to drop $10 on Navicat Essentials for PostgreSQL a while back.

giaour 11y ago

The community tooling around MySQL is impressive compared to what's available for Postgres. I've heard the same from people who have switched from SQL Server and miss the SQL Server Workbench suite. My hunch is that this gap exists because MySQL is often the default DB choice for new developers, whereas Postgres tends to be used by people already familiar with database CLI tools. Once you learn how to use the command line effectively, you'll be able to work a lot faster and won't want to use a web interface again, which is to say that you will become yet another reason for phpPgAdmin to be neglected.

metaphorm 11y ago

http://www.pgadmin.org/ http://www.pgadmin.org/ this is a really useful GUI tool for interacting with a postgres database.

dorfsmay 11y ago

Have you tried pgAdmin: http://www.pgadmin.org/ http://www.pgadmin.org/

dorfsmay 11y ago
For more generic use, I really like DBeaver, as it works with most RDBMS, including Oracle, SQLite,PostgreSQL and MySQL: http://dbeaver.jkiss.org/ http://dbeaver.jkiss.org/

the_rosentotter 11y ago

For a web interface, try TeamPostgreSQL[1]. Beats phpMyAdmin, imho. [1]: http://www.teampostgresql.com http://www.teampostgresql.com

davidgerard 11y ago

Your real problem is that MySQL won the network effect early, even though it's just terrible. The only thing we let people use MySQL for now is third-party frameworks that pretty much require it (Magento, MediaWiki, WordPress, Drupal). In fact, we're moving our Oracle to Postgres, with great success. (Oracle is fantastic at storing your data and giving it back, but everything else about it is the opposite of "agile".)

lazyant 11y ago

Navicat, DB Visualizer, pgAdmin