July 4th, 2009 by depesz | Tags: , , , | 5 comments »
Did it help? If yes - maybe you can help me? Donate BTC to 19zPa5diT2LZqGtTi8f8bfApLn8rw9zBHx

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

How does it look? Quite simple thing to test:

# create table x (id int4);
CREATE TABLE
# create table x2 () INHERITS (x);
CREATE TABLE
# create table x3 () INHERITS (x);
CREATE TABLE

Now, \d of x looks like this:

# \d x
Table "public.x"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Number of child tables: 2 (Use \d+ to list them.)

Please notice that there is no list of child tables – in some cases you might have hundreds of child tables, so it doesn't make sense to always output them.

Instead, you can:

# \d+ x
Table "public.x"
Column | Type | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
id | integer | | plain |
Child tables: x2,
x3
Has OIDs: no

Pretty cool.

  1. 5 comments

  2. Jul 6, 2009

    I noticed something when making a deeper hierarchy, namely that it descends just one level. I think this may count as a POLA violation. What do you think?

    CREATE TABLE x (id INTEGER);
    CREATE TABLE x_01 () inherits (x);
    CREATE TABLE x_02 () inherits (x);
    CREATE TABLE x_01_01 () inherits (x_01);
    CREATE TABLE x_01_02 () inherits (x_01);

    \d+ x
    Table “public.x”
    Column | Type | Modifiers | Storage | Description
    ——–+———+———–+———+————-
    id | integer | | plain |
    Child tables: x_01,
    x_02
    Has OIDs: no

  3. Jul 6, 2009

    @David Fetter:
    not sure. I have seen 2 uses for inherited tables so far, and none of them is really useful with multi-level inheritance, but maybe I’m missing some cases.

  4. Jul 6, 2009

    @depesz
    I’ve seen it in multi-level partitioning schemes. Whether *those* are a good idea is a whole different question.

  5. Jul 8, 2009

    I think the unfortunate thing about this is that it makes \d+ pretty much unusable on parent tables of large partition schemes.

  6. # Scott C.
    May 10, 2010

    @Robert

    Yup, I’ll have to remember not to use \d+ on my parent tables with ~7500 children.

Leave a comment