Postgres tips for the average and power user | Hacker News Reader

Postgres tips for the average and power user

citusdata.com

138 pointslfittl9 comments

I use pgcli for most of my day to day stuff in Pg, but recently been getting lazier and running pgadmin from docker and mapping volumes to store connections/SSH tunnels/server groups etc. Makes writing and tweaking longer queries a lot easier.

Relevant talk / slides I stumbled upon from 2013 by Heroku's Postgres team :

http://postgres-bits.herokuapp.com/#1

https://vimeo.com/61044807

Best tip I know: "use pgcli"

Wow, that is a great tip. Auto-completion and color coding.

I switched from psql to pgcli a few months ago and love it! But every so often it fails to remember some of the \ commands.

Do you have anything special in your config?

> Generating and inserting fake data

Sounds very promising just to be very quickly downgraded to

> something simple and quick

In this case that is only how to insert a bunch of rows that have only numbers and timestamps as these are types that `generate_series` supports.

Maybe someone will actually find it useful, so not saying that a tip is completely useless but personally I think it will be very rare for it to be actually fit for purpose. A huge majority of the time when I need to generate fake data there are other data types involved and usually, there are some constraints between them as well.

Other tips sound very nice and will keep them in my notes.

For text you can just concatenate a number on to whatever fixed text you want which is usually good enough for my testing. Also from text you can construct other types so a combination of these should cover most needs.

The value from generate_series is not used for anything, it is only used to generate the listed number of rows. The actual value in the examples is only provided by random(). You can create random strings, or other data types, to use with scalar selects.

I had never thought of using generate_series for inserting random data, but like you said for some tables it doesn't make sense to use purely random values.

The tag thing is pretty neat, and it works with anything readline-based.

Reply on news.ycombinator.com