How to identify the column used to partition a table from the Postgres system catalogs












1















Given a table created as so...



CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);


How do I identify which column it's been partitioned on? - in this case 'logdate' solely by querying the postgres catalog.



I've looked in the obvious places in the catalog (pg_class, pg_index) but nothing springs out.



(Using version 10.5)










share|improve this question

























  • @McNets - "select * from pg_class where relkind='p'" gives me those tables that are partitioned, it doesn't tell which column they've been partitioned on (not that I can see anyhow!)

    – Hemel
    Nov 24 '18 at 16:14
















1















Given a table created as so...



CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);


How do I identify which column it's been partitioned on? - in this case 'logdate' solely by querying the postgres catalog.



I've looked in the obvious places in the catalog (pg_class, pg_index) but nothing springs out.



(Using version 10.5)










share|improve this question

























  • @McNets - "select * from pg_class where relkind='p'" gives me those tables that are partitioned, it doesn't tell which column they've been partitioned on (not that I can see anyhow!)

    – Hemel
    Nov 24 '18 at 16:14














1












1








1








Given a table created as so...



CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);


How do I identify which column it's been partitioned on? - in this case 'logdate' solely by querying the postgres catalog.



I've looked in the obvious places in the catalog (pg_class, pg_index) but nothing springs out.



(Using version 10.5)










share|improve this question
















Given a table created as so...



CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);


How do I identify which column it's been partitioned on? - in this case 'logdate' solely by querying the postgres catalog.



I've looked in the obvious places in the catalog (pg_class, pg_index) but nothing springs out.



(Using version 10.5)







postgresql partitioning postgresql-10






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 26 '18 at 7:16









a_horse_with_no_name

39k775112




39k775112










asked Nov 24 '18 at 15:34









HemelHemel

21829




21829













  • @McNets - "select * from pg_class where relkind='p'" gives me those tables that are partitioned, it doesn't tell which column they've been partitioned on (not that I can see anyhow!)

    – Hemel
    Nov 24 '18 at 16:14



















  • @McNets - "select * from pg_class where relkind='p'" gives me those tables that are partitioned, it doesn't tell which column they've been partitioned on (not that I can see anyhow!)

    – Hemel
    Nov 24 '18 at 16:14

















@McNets - "select * from pg_class where relkind='p'" gives me those tables that are partitioned, it doesn't tell which column they've been partitioned on (not that I can see anyhow!)

– Hemel
Nov 24 '18 at 16:14





@McNets - "select * from pg_class where relkind='p'" gives me those tables that are partitioned, it doesn't tell which column they've been partitioned on (not that I can see anyhow!)

– Hemel
Nov 24 '18 at 16:14










2 Answers
2






active

oldest

votes


















3














I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)



First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):



CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (city_id,logdate);


This is my solution:






select 
par.relnamespace::regnamespace::text as schema,
par.relname as table_name,
partnatts as num_columns,
column_index,
col.column_name
from
(select
partrelid,
partnatts,
case partstrat
when 'l' then 'list'
when 'r' then 'range' end as partition_strategy,
unnest(partattrs) column_index
from
pg_partitioned_table) pt
join
pg_class par
on
par.oid = pt.partrelid
join
information_schema.columns col
on
col.table_schema = par.relnamespace::regnamespace::text
and col.table_name = par.relname
and ordinal_position = pt.column_index;



schema | table_name | num_columns | column_index | column_name
:-------------------------- | :---------- | ----------: | -----------: | :----------
fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 1 | city_id
fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 2 | logdate



db<>fiddle here



pg_partitioned_table




The catalog pg_partitioned_table stores information about how tables
are partitioned.




Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.



+-----------+------------+---------------------+-------------------------------------------------------------|
| partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate |
| | | | which table columns are part of the partition key. |
| | | | For example, a value of 1 3 would mean that the first |
| | | | and the third table columns make up the partition key. |
| | | | A zero in this array indicates that the corresponding |
| | | | partition key column is an expression, rather than a simple |
| | | | column reference. |
+-----------+------------+---------------------+-------------------------------------------------------------|





share|improve this answer


























  • quote "I'm not a PostgreSQL pro" consider yourself promoted!

    – Hemel
    Nov 24 '18 at 17:21













  • I'm glad to help.

    – McNets
    Nov 24 '18 at 17:22



















0














The answer by McNets already helps, but here is a query that produces slightly prettier output:



select c.relnamespace::regnamespace::text as schema,
c.relname as table_name,
pg_get_partkeydef(c.oid) as partition_key
from pg_class c
where c.relkind = 'p';


Below is output that the above query produces:



 schema │ table_name  │      partition_key       
────────┼─────────────┼──────────────────────────
public │ measurement │ RANGE (city_id, logdate)
(1 row)





share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223327%2fhow-to-identify-the-column-used-to-partition-a-table-from-the-postgres-system-ca%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    3














    I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)



    First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):



    CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
    ) PARTITION BY RANGE (city_id,logdate);


    This is my solution:






    select 
    par.relnamespace::regnamespace::text as schema,
    par.relname as table_name,
    partnatts as num_columns,
    column_index,
    col.column_name
    from
    (select
    partrelid,
    partnatts,
    case partstrat
    when 'l' then 'list'
    when 'r' then 'range' end as partition_strategy,
    unnest(partattrs) column_index
    from
    pg_partitioned_table) pt
    join
    pg_class par
    on
    par.oid = pt.partrelid
    join
    information_schema.columns col
    on
    col.table_schema = par.relnamespace::regnamespace::text
    and col.table_name = par.relname
    and ordinal_position = pt.column_index;



    schema | table_name | num_columns | column_index | column_name
    :-------------------------- | :---------- | ----------: | -----------: | :----------
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 1 | city_id
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 2 | logdate



    db<>fiddle here



    pg_partitioned_table




    The catalog pg_partitioned_table stores information about how tables
    are partitioned.




    Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.



    +-----------+------------+---------------------+-------------------------------------------------------------|
    | partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate |
    | | | | which table columns are part of the partition key. |
    | | | | For example, a value of 1 3 would mean that the first |
    | | | | and the third table columns make up the partition key. |
    | | | | A zero in this array indicates that the corresponding |
    | | | | partition key column is an expression, rather than a simple |
    | | | | column reference. |
    +-----------+------------+---------------------+-------------------------------------------------------------|





    share|improve this answer


























    • quote "I'm not a PostgreSQL pro" consider yourself promoted!

      – Hemel
      Nov 24 '18 at 17:21













    • I'm glad to help.

      – McNets
      Nov 24 '18 at 17:22
















    3














    I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)



    First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):



    CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
    ) PARTITION BY RANGE (city_id,logdate);


    This is my solution:






    select 
    par.relnamespace::regnamespace::text as schema,
    par.relname as table_name,
    partnatts as num_columns,
    column_index,
    col.column_name
    from
    (select
    partrelid,
    partnatts,
    case partstrat
    when 'l' then 'list'
    when 'r' then 'range' end as partition_strategy,
    unnest(partattrs) column_index
    from
    pg_partitioned_table) pt
    join
    pg_class par
    on
    par.oid = pt.partrelid
    join
    information_schema.columns col
    on
    col.table_schema = par.relnamespace::regnamespace::text
    and col.table_name = par.relname
    and ordinal_position = pt.column_index;



    schema | table_name | num_columns | column_index | column_name
    :-------------------------- | :---------- | ----------: | -----------: | :----------
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 1 | city_id
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 2 | logdate



    db<>fiddle here



    pg_partitioned_table




    The catalog pg_partitioned_table stores information about how tables
    are partitioned.




    Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.



    +-----------+------------+---------------------+-------------------------------------------------------------|
    | partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate |
    | | | | which table columns are part of the partition key. |
    | | | | For example, a value of 1 3 would mean that the first |
    | | | | and the third table columns make up the partition key. |
    | | | | A zero in this array indicates that the corresponding |
    | | | | partition key column is an expression, rather than a simple |
    | | | | column reference. |
    +-----------+------------+---------------------+-------------------------------------------------------------|





    share|improve this answer


























    • quote "I'm not a PostgreSQL pro" consider yourself promoted!

      – Hemel
      Nov 24 '18 at 17:21













    • I'm glad to help.

      – McNets
      Nov 24 '18 at 17:22














    3












    3








    3







    I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)



    First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):



    CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
    ) PARTITION BY RANGE (city_id,logdate);


    This is my solution:






    select 
    par.relnamespace::regnamespace::text as schema,
    par.relname as table_name,
    partnatts as num_columns,
    column_index,
    col.column_name
    from
    (select
    partrelid,
    partnatts,
    case partstrat
    when 'l' then 'list'
    when 'r' then 'range' end as partition_strategy,
    unnest(partattrs) column_index
    from
    pg_partitioned_table) pt
    join
    pg_class par
    on
    par.oid = pt.partrelid
    join
    information_schema.columns col
    on
    col.table_schema = par.relnamespace::regnamespace::text
    and col.table_name = par.relname
    and ordinal_position = pt.column_index;



    schema | table_name | num_columns | column_index | column_name
    :-------------------------- | :---------- | ----------: | -----------: | :----------
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 1 | city_id
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 2 | logdate



    db<>fiddle here



    pg_partitioned_table




    The catalog pg_partitioned_table stores information about how tables
    are partitioned.




    Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.



    +-----------+------------+---------------------+-------------------------------------------------------------|
    | partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate |
    | | | | which table columns are part of the partition key. |
    | | | | For example, a value of 1 3 would mean that the first |
    | | | | and the third table columns make up the partition key. |
    | | | | A zero in this array indicates that the corresponding |
    | | | | partition key column is an expression, rather than a simple |
    | | | | column reference. |
    +-----------+------------+---------------------+-------------------------------------------------------------|





    share|improve this answer















    I'm not a PostgreSQL pro, but digging a bit I've founded a solution that perhaps can help you. (Works only for version 10 or above)



    First I've slightly modified you table by adding two columns to the partition definition (just to show you the final result):



    CREATE TABLE measurement (
    city_id int not null,
    logdate date not null,
    peaktemp int,
    unitsales int
    ) PARTITION BY RANGE (city_id,logdate);


    This is my solution:






    select 
    par.relnamespace::regnamespace::text as schema,
    par.relname as table_name,
    partnatts as num_columns,
    column_index,
    col.column_name
    from
    (select
    partrelid,
    partnatts,
    case partstrat
    when 'l' then 'list'
    when 'r' then 'range' end as partition_strategy,
    unnest(partattrs) column_index
    from
    pg_partitioned_table) pt
    join
    pg_class par
    on
    par.oid = pt.partrelid
    join
    information_schema.columns col
    on
    col.table_schema = par.relnamespace::regnamespace::text
    and col.table_name = par.relname
    and ordinal_position = pt.column_index;



    schema | table_name | num_columns | column_index | column_name
    :-------------------------- | :---------- | ----------: | -----------: | :----------
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 1 | city_id
    fiddle_ctcqwfrzpcyngmgnqkdy | measurement | 2 | 2 | logdate



    db<>fiddle here



    pg_partitioned_table




    The catalog pg_partitioned_table stores information about how tables
    are partitioned.




    Unnesting partattrs you can get column index of each row involved in the partition. Then you can join information_schema.columns just to retrieve the name of every column.



    +-----------+------------+---------------------+-------------------------------------------------------------|
    | partattrs | int2vector | pg_attribute.attnum | This is an array of partnatts values that indicate |
    | | | | which table columns are part of the partition key. |
    | | | | For example, a value of 1 3 would mean that the first |
    | | | | and the third table columns make up the partition key. |
    | | | | A zero in this array indicates that the corresponding |
    | | | | partition key column is an expression, rather than a simple |
    | | | | column reference. |
    +-----------+------------+---------------------+-------------------------------------------------------------|






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 24 '18 at 17:21

























    answered Nov 24 '18 at 17:12









    McNetsMcNets

    15.4k41858




    15.4k41858













    • quote "I'm not a PostgreSQL pro" consider yourself promoted!

      – Hemel
      Nov 24 '18 at 17:21













    • I'm glad to help.

      – McNets
      Nov 24 '18 at 17:22



















    • quote "I'm not a PostgreSQL pro" consider yourself promoted!

      – Hemel
      Nov 24 '18 at 17:21













    • I'm glad to help.

      – McNets
      Nov 24 '18 at 17:22

















    quote "I'm not a PostgreSQL pro" consider yourself promoted!

    – Hemel
    Nov 24 '18 at 17:21







    quote "I'm not a PostgreSQL pro" consider yourself promoted!

    – Hemel
    Nov 24 '18 at 17:21















    I'm glad to help.

    – McNets
    Nov 24 '18 at 17:22





    I'm glad to help.

    – McNets
    Nov 24 '18 at 17:22













    0














    The answer by McNets already helps, but here is a query that produces slightly prettier output:



    select c.relnamespace::regnamespace::text as schema,
    c.relname as table_name,
    pg_get_partkeydef(c.oid) as partition_key
    from pg_class c
    where c.relkind = 'p';


    Below is output that the above query produces:



     schema │ table_name  │      partition_key       
    ────────┼─────────────┼──────────────────────────
    public │ measurement │ RANGE (city_id, logdate)
    (1 row)





    share|improve this answer




























      0














      The answer by McNets already helps, but here is a query that produces slightly prettier output:



      select c.relnamespace::regnamespace::text as schema,
      c.relname as table_name,
      pg_get_partkeydef(c.oid) as partition_key
      from pg_class c
      where c.relkind = 'p';


      Below is output that the above query produces:



       schema │ table_name  │      partition_key       
      ────────┼─────────────┼──────────────────────────
      public │ measurement │ RANGE (city_id, logdate)
      (1 row)





      share|improve this answer


























        0












        0








        0







        The answer by McNets already helps, but here is a query that produces slightly prettier output:



        select c.relnamespace::regnamespace::text as schema,
        c.relname as table_name,
        pg_get_partkeydef(c.oid) as partition_key
        from pg_class c
        where c.relkind = 'p';


        Below is output that the above query produces:



         schema │ table_name  │      partition_key       
        ────────┼─────────────┼──────────────────────────
        public │ measurement │ RANGE (city_id, logdate)
        (1 row)





        share|improve this answer













        The answer by McNets already helps, but here is a query that produces slightly prettier output:



        select c.relnamespace::regnamespace::text as schema,
        c.relname as table_name,
        pg_get_partkeydef(c.oid) as partition_key
        from pg_class c
        where c.relkind = 'p';


        Below is output that the above query produces:



         schema │ table_name  │      partition_key       
        ────────┼─────────────┼──────────────────────────
        public │ measurement │ RANGE (city_id, logdate)
        (1 row)






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 26 '18 at 4:04









        Amit LAmit L

        24125




        24125






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f223327%2fhow-to-identify-the-column-used-to-partition-a-table-from-the-postgres-system-ca%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

            Calculate evaluation metrics using cross_val_predict sklearn

            Insert data from modal to MySQL (multiple modal on website)