Recent release of new versions of PostgreSQL suggests that you do reindex of all indexes. But this will take a while, and since we don't actually have ‘REINDEX CONCURRENTLY' command – it's a bit tricky.
So, since I will be doing this on several databases, decided to write a script that will handle the work for me.
Continue reading Concurrent REINDEX of all indexes in database
I'd like to announce start of new project for PostgreSQL admins – Curo.
Continue reading New project – Curo
Yesterday I talked a bit with David Fetter about improvements in psql. We both would like to get proper stored procedures (with multiple result sets, and transaction control), but since this seems to be too complicated, I thought about what I'd like to have added to psql.
Continue reading Wish list for psql
On 6th of July, Robert Haas committed patch:
Add \ir command to psql.
\ir is short for "include relative"; when used from a script, the
supplied pathname will be interpreted relative to the input file,
rather than to the current working directory.
Gurjeet Singh, reviewed by Josh Kupershmidt, with substantial further
cleanup by me.
Continue reading Waiting for 9.2 – relative paths in psql
On 20th of July, Robert Haas committed (and later committed change to it) patch which adds another \* command to psql:
Add \conninfo command to psql, to show current connection info.
David Christensen. Reviewed by Steve Singer. Some further changes by me.
Continue reading Waiting for 9.1 – \conninfo in psql
On 22nd of November Tom Lane committed patch by Roger Leigh which fixes my pet peeve:
Improve psql's tabular display of wrapped-around data by inserting markers
in the formerly-always-blank columns just to left and right of the data.
Different marking is used for a line break caused by a newline in the data
than for a straight wraparound. A newline break is signaled by a "+" in the
right margin column in ASCII mode, or a carriage return arrow in UNICODE mode.
Wraparound is signaled by a dot in the right margin as well as the following
left margin in ASCII mode, or an ellipsis symbol in the same places in UNICODE
mode. "\pset linestyle old-ascii" is added to make the previous behavior
available if anyone really wants it.
In passing, this commit also cleans up a few regression test files that
had unintended spacing differences from the current actual output.
Roger Leigh, reviewed by Gabrielle Roth and other members of PDXPUG.
Continue reading Waiting for 8.5 – better wrapped output in psql
Yesterday Peter Eisentraut committed a patch, written by Damien Clochard, that modifies \d output in psql:
Have \d show child tables that inherit from the specified parent
As per discussion, \d shows only the number of child tables, because that
could be hundreds, when used for partitioning. \d+ shows the actual list.
Author: Damien Clochard <email@example.com>
Continue reading Waiting for 8.5 – Have \d show child tables that inherit from the specified parent
Let's say you imported some data, but it contains duplicates. You will have to handle them in some way, but to make sensible choice on how to handle it, you need more information.
So, let's start. We have table:
# \d users
Column | Type | Modifiers
id | integer | not null default nextval('users_id_seq'::regclass)
username | text |
registered | timestamp with time zone |
"users_pkey" PRIMARY KEY, btree (id)
Continue reading Tips N’ Tricks – Generating readable reports with plain SQL
On 6th of January Bruce Momjian committed patch by Greg Sabino Mulane, which fixes one of a very long standing annoyances in PostgreSQL – removes system objects from \dX commands in psql.
Continue reading Waiting for 8.4 – remove system objects from \dX
Today, Tom Lane committed patch, written by Abhijit Menon-Sen, which adds interesting feature to psql. Namely – it simplifies changing definition of functions.
Continue reading Waiting for 8.4 – \ef in psql