psql, the database client for PostgreSQL has, since forever, support for variables.
These let you write certain queries in a way that is safe even when getting params from “outside".
Let's see what can be done with it…
Tag: tutorial
What is LATERAL, what is it for, and how can one use it?
Lately in couple of places I recommended people that they can solve their problem with queries using LATERAL. In some cases recipient of such suggestion indicated that they had no idea what LATERAL is. Which made me think that it might be good idea to write more about them (lateral queries)…
Also – I know that some of the examples I shown in here can be done differently, I just wanted to show how one can use LATERAL, and am terrible with coming up with better usecases.
Continue reading What is LATERAL, what is it for, and how can one use it?
Starting with Pg – where are logs?
Just so that it will be perfectly clear: the logs I have in mind are the ones for DBAs to read – with slow queries, errors, and other interesting information.
So, how does one find them?
Starting with Pg – where/how can I set configuration parameters?
Previously I wrote about locating config files.
The thing is – postgresql.conf is not the only place you can set your configuration in.
In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from.
Continue reading Starting with Pg – where/how can I set configuration parameters?
Starting with Pg – where is the config?
Over the years I saw some people find themselves in position where they have to start dealing with PostgreSQL with minimal, or none, prior exposure. This leads to problems with seemingly easy tasks – how to change config? How to find stuff in logs?
So I decided to write some blogposts to be able to point such people to pre-made tutorials.
And I start today, with information on how to find PostgreSQL config files.
Why is there database named “postgres”?
Every so often, on irc, someone asks if they can remove postgres database, and why is it even there.
Figured I can write one blogpost to answer it once and for all, and use it as ready answer whenever the question resurfaces.
How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 2
In previous post I showed how I'd install PostgreSQL for developer.
But that's not all. Now we need to add some configuration. What, how, where?
Continue reading How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 2
How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 1
Recently I spent some time thinking about what can be improved when it comes to helping new users start using PostgreSQL.
One thing that almost immediately jumped to my mind is – how to install PostgreSQL? The task is theoretically simple. But there are always some caveats – which packages to use, what to configure in the beginning, where to find config files and logs.
With that in mind I decided to write a howto based on my ideas on what is right. These do not necessarily mean that these are the best for everybody, but I think this is a good start for anyone wanting to start their adventure with PostgreSQL.
Final note of warning – this post is for installing and setting PostgreSQL on developer workstation. As in: server where user can do anything, and we don't really care about security. Please do not configure production servers using this howto.
Continue reading How to install and configure PostgreSQL Debian/Ubuntu – for developer use – part 1
How to make sure there is always at least one “sub” record?
This question appeared couple of times on irc, so I figured I can do a blogpost about it.
Continue reading How to make sure there is always at least one “sub" record?
What index to create?
Some time ago I wrote a blogpost about why index might not be used.
While this post seemed to be well received (top link from depesz.com on reddit), it doesn't answer another question – what index to create for given situation.
I'll try to cover this question now.