6 ms·

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

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

by jperras 11y ago

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