1

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.

  • information_schema views contain metadata for database object, like tables and views. Your query is not a database object, so there isn't anything about it in the system catalog. You have written the query, so you should already know the columns you are retrieving. I suspect this is an example of an X-Y problem; can you describe the problem you're trying to solve? – mustaccio Mar 22 '20 at 0:47
  • This is inside a method in Python utilizing the psycopg2 library. I want to return the data and also the column names for the data so it's easy for the other coder to work with and bind it to the GUI. So I assume I just have to return that manually for the method – Jack Avante Mar 22 '20 at 1:43
1

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/

3

I'd like to somehow get the name of all columns in this new table.

You can create a temporary view, which registers the row type. Then query either the information schema like you tried or the system catalog pg_attribute:

CREATE TEMP VIEW tmp_v1 AS SELECT ...;  -- your query here

SELECT attname
FROM   pg_attribute
WHERE  attrelid = 'pg_temp.tmp_v1'::regclass;

Returns the desired list of column names.

Catalog tables or information schema? See:

The temporary view is only visible inside your current session and dropped at its end. (Or you can drop it manually.)


In psql you could use the meta-command \gdesc:

test=# SELECT 1 AS foo \gdesc
 Column |  Type
--------+---------
 foo    | integer

The manual:

\gdesc

Shows the description (that is, the column names and data types) of the result of the current query buffer. The query is not actually executed; however, if it contains some type of syntax error, that error will be reported in the normal way.

If the current query buffer is empty, the most recently sent query is described instead.

So you can even execute a bare \gdesc after running the query to get a "table description" for the executed query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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