PostgreSQL – List All Column Names for Multi-Joined Table

join;postgresqlquery

I have a slightly more complicated join:

SELECT person.given_name, person.family_name, person.age, person.sex, person.state,
       person.zip, person.street, person.residential_number,
       c1.name as citizen_of_country,
       c2.name as hospitalization_country,
       c3.name as infection_country

FROM patients

    JOIN person on patients.person_id = person.id
    JOIN country c1 on person."citizenOf_country_id" = c1.id
    JOIN country c2 on patients.hospitalized_in_country_id = c2.id
    JOIN country c3 on patients.infected_in_country_id = c3.id;

.. and I'd like to somehow get the name of all columns in this new table. Based on some answers I found, I tried

SELECT DISTINCT column_name FROM information_schema.columns WHERE table_schema = 'public' AND [code];

Where [code] is the block above.. But it didn't work, giving the error "Subquery must return only one column". I'm new to databases so I'm not sure how to handle this correctly.

Best Answer

You can get the information from yourcursor.description. Example:

conn = psycopg2.connect(...)
curs = conn.cursor()
curs.execute("""SELECT person.given_name, ...""")

curs.description

You can find more info under metadata on zetcode.com/python/psycopg2/

Related Solutions

Mysql – Group By on column from joined table

Probably because the table "dict_verbs_german" is the last table joined.

When I last researched this, "using temporary; using filesort" meant MySQL was writing the the join result into a temporary table, then sorting. (See note below.) In order to avoid "using temporary; using filesort", you'd have to meet all these conditions.

dict_verbs_german is the first non-constant table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

To avoid "using temporary", you'd have to meet all these conditions.

dict_verbs_german is the first table in the JOIN order, and
dict_verbs_german has an index that marches the ORDER BY column list.

Note: I'm talking about ORDER BY, because that's specifically what my research dealt with, and I'm pulling these notes from an email I wrote in 2010. I think the same optimizations applied to GROUP BY, but I could be wrong. Improvements to the optimizer over the last couple of years might have changed things, too.

MySQL documentation

In some cases, MySQL cannot use indexes to resolve the ORDER BY . . .. These cases include . . . joining many tables, and the columns in the ORDER BY are not all from the first nonconstant table that is used to retrieve rows. (This is the first table in the EXPLAIN output that does not have a const join type.)

MySQL documentation for optimizing limit queries

(I don't think this last reference will help you with this problem, but it's good to know where to find it.)

Later . . .

Cutting the WHERE clause seems to eliminate the filesort. (That might not be true for real-world data.) Maybe you can create a view without a WHERE clause and a LIMIT clause, then query the view and still avoid a filesort. I'm sorry that, for personal reasons, I'm not able to test that for you right now.

PostgreSQL Partitioning – Get All Partition Names for a Table

Use the first query from the answer you linked and add a simple WHERE clause to get the partitions of a single table:

SELECT
    nmsp_parent.nspname AS parent_schema,
    parent.relname      AS parent,
    nmsp_child.nspname  AS child_schema,
    child.relname       AS child
FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
    JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
    JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
    JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='parent_table_name';

Best Answer

Related Solutions

Mysql – Group By on column from joined table

PostgreSQL Partitioning – Get All Partition Names for a Table

Related Question