How to get PostgreSQL to give me MySQL SHOW COLUMNS-like results?

Question

After much mucking about, I'm close (For my sake, I don't care about the type differences). I do, however, want the exact same output format as MySQL. The reason is I'm trying to adapt a MySQL-only tool for use with PostgreSQL. Here's an example output from MySQL (albeit with fewer columns):

mysql> show columns from users;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | int(11)      | NO   | PRI | NULL    |       |
| name        | varchar(200) | YES  |     | NULL    |       |
| institution | varchar(200) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

Here's the table on which I'm testing this:

Table "public.users"
       Column       |          Type          | Collation | Nullable |              Default              
--------------------+------------------------+-----------+----------+-----------------------------------  
id                  | integer                |           | not null | nextval('users_id_seq'::regclass) 
name                | character varying(255) |           |          |   
role_id             | integer                |           |          |   
image_url           | character varying(510) |           |          |   
institution         | character varying(255) |           |          |  
qualifications      | text                   |           |          |   
cv_url              | character varying(510) |           |          |   
specializations     | text                   |           |          |   
text_collaboration  | text                   |           |          |  
Indexes:
    "users_pkey" PRIMARY KEY, btree (id) Check constraints:
    "users_name_not_null" CHECK (name IS NOT NULL) Foreign-key constraints:
    "fk_role_id" FOREIGN KEY (role_id) REFERENCES roles(id) Referenced by:
    TABLE "novel_reviews" CONSTRAINT "novels_reviewer_id_fkey" FOREIGN KEY (reviewer_id) REFERENCES users(id)
    TABLE "review_translations" CONSTRAINT "review_translations_recorder_id_fkey" FOREIGN KEY (recorder_id) REFERENCES users(id)

Here's the query I have... it's probably poorly done esp. with the GROUP BY part:

SELECT column_name AS "Field"
       , data_type AS "Type"
       , is_nullable AS "Null"
       , CASE WHEN is_primary=true THEN 'PRI' ELSE NULL END AS "Key"
       , column_default as "Default"
       , CASE WHEN column_default LIKE 'nextval(%' THEN 'auto_increment' ELSE '' END AS "Extra" 
FROM
(
    SELECT c.column_name
           , c.data_type
           , c.is_nullable
           , tc.constraint_type='PRIMARY KEY' AS is_primary
           , c.column_default 
    FROM information_schema.columns AS c 
    LEFT JOIN information_schema.constraint_column_usage AS ccu USING (column_name, table_name) 
    LEFT JOIN information_schema.table_constraints tc USING (constraint_name) 
    WHERE c.table_name = 'users'
    GROUP BY c.column_name
             , c.data_type
             , c.is_nullable
             , is_primary
             , c.column_default
) as sq;

Here's the results I'm getting currently. Sorry for the poor formatting.

>        Field        |       Type        | Null | Key |              Default              |     Extra      
> --------------------+-------------------+------+-----+-----------------------------------+----------------  
> |   cv_url          | character varying | YES  |     |                 
> |   id              | integer           | NO   |     | nextval('users_id_seq'::regclass) | auto_increment  
> |   id              | integer           | NO   | PRI | nextval('users_id_seq'::regclass) | auto_increment  
> |   image_url       | character varying | YES  |     | 
> |   institution     | character varying | YES  |     |             
> |   name            | character varying | YES  |     |             
> |   qualifications  | text              | YES  |     |             
> |   role_id         | integer           | YES  |     |             
> |   specializations | text              | YES  |     |             
> | 
> |  (10 rows)

I can't figure how to get the second occurrence of id to go away, the one emanating from the non-primarykey constraint. I can't wrap my head around how to drop that. I tried doing WHERE is_primary_key is NULL or is_primary_key=TRUE but that drops the Name field as well, which is joined to a constraint which is also not a primary key.

What I'd like is to get all columns from the table, (each only once) and the string "PRI" if the field is a primary key.

Help! I'm in a bit over my head. Thanks.

Start by running `psql` with the `-E` switch, that'll echo all the SQL behind the various backslash commands. Then you can `\d users` to see the SQL that `psql` uses to describe the `users` table. — mu is too short, Jun 05 '19 at 21:01
That's interesting. I managed to find the query being run to generate that. However, postgres doesn't have a column to indicate "Key" or primary key constraint or anything like that in its `\d` command, so it's not a direct help. Edit: I guess it does have indexes actually. Still, I need to figure out how to combine that info. — Brian Peterson, Jun 05 '19 at 22:47

score 1 · Answer 1 · answered Jun 05 '19 at 22:55

1

It is the query you need :

SELECT *
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name   = 'users'

answered Jun 05 '19 at 22:55

berkancetin

315
1
3
18

No, because I want to get whether or not the column is a primary key, necessitating the JOINs I am using above, resulting in the duplication I don't want. I just need a way to SELECT DISTINCT and ensure the primary key one comes out first. – Brian Peterson Jun 06 '19 at 16:11

Brian Peterson · Answer 2 · 2019-06-06T20:27:48.243

Figured it out after a lot of banging my head against the wall. First I made a view:

CREATE VIEW table_column_constraints as (SELECT c.table_schema, c.table_name, c.column_name
           , c.data_type
           , c.is_nullable
           , tc.constraint_type              
           , c.column_default 
    FROM information_schema.columns AS c 
    LEFT JOIN information_schema.constraint_column_usage AS ccu USING (column_name, table_name) 
    LEFT JOIN information_schema.table_constraints tc ON tc.constraint_name=ccu.constraint_name WHERE c.table_schema='public');

Then, I did a de-duplication technique of comparing the table to itself:

SELECT column_name as "Field" 
           , data_type AS "Type"
           , is_nullable AS "Null"
           , CASE WHEN constraint_type='PRIMARY KEY' THEN 'PRI' ELSE NULL END AS "Key"              
           , column_default AS "Default", CASE WHEN column_default LIKE 'nextval(%' THEN 'auto_increment' ELSE '' END AS "Extra" 
    FROM table_column_constraints as given WHERE given.table_name = 'users'  
    AND NOT EXISTS (SELECT * FROM table_column_constraints other WHERE other.column_name=given.column_name AND given.constraint_type!='PRIMARY KEY' AND other.constraint_type='PRIMARY KEY');

To get the following results:

       Field        |       Type        | Null | Key |              Default              |     Extra      
--------------------+-------------------+------+-----+-----------------------------------+----------------
 name               | character varying | YES  |     |                                   | 
 id                 | integer           | NO   | PRI | nextval('users_id_seq'::regclass) | auto_increment
 image_url          | character varying | YES  |     |                                   | 
 institution        | character varying | YES  |     |                                   | 
 qualifications     | text              | YES  |     |                                   | 
 cv_url             | character varying | YES  |     |                                   | 
 specializations    | text              | YES  |     |                                   | 
 text_collaboration | text              | YES  |     |                                   | 
 role_id            | integer           | YES  |     |                                   | 
(9 rows)

I was inspired by https://stackoverflow.com/a/45065229/1151229 on a question called "Selecting rows ordered by some column and distinct on another"

How to get PostgreSQL to give me MySQL SHOW COLUMNS-like results?

2 Answers2