1

I'm looking for some SIMPLE SQL-based command to show me the partitioned table structure in postgres. Something like \d <table>. All I find so far is by doing pgdump.

db=> \d partitioned_table
ERROR:  column c.relhasoids does not exist
LINE 1: ..., c.relhasindex, c.relhasrules, c.relhastriggers, c.relhasoi...
         
1
  • 1
    Unfortunately there is no "SQL interface" to get the DDL of a table in Postgres
    – user1822
    Apr 3, 2022 at 8:24

2 Answers 2

0

I'm not aware of any "simple sql-based command", however that doesn't mean that you can't create one that gets you what you want.

In psql you can \set ECHO_HIDDEN on. This will cause psql to echo the queries that it uses to answer \d ... commands.

Having set ECHO_HIDDEN, run \d+ my_schema.my_partitioned_table. What this will get you is not only the details about the partitioned table (to include the partitions) but also the queries used to retrieve those details. Using those queries as a starting point it could be possible to build what you want.

2
  • the whole issue is that \d+ or \d gives me ERROR: column c.relhasoids does not exist
    – Nir
    Apr 6, 2022 at 14:03
  • @Nir That sounds like the version of psql that you are using is out of step with the server. Using a version of psql that matches (or at least is closer) to the server version should fix that error.
    – gsiems
    Apr 7, 2022 at 17:39
-1
SELECT table_name, 
       column_name, 
       data_type,
       character_maximum_length
FROM information_schema.columns
WHERE table_name = 'partitioned_table';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.