【PostgreSQL】テーブル定義とカラム一覧を確認する

testdb=# \d users
                           Table "public.users"
   Column   |            Type             | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+---------
 id         | integer                     |           | not null |
 name       | text                        |           | not null |
 email      | character varying(255)      |           |          |
 address    | text                        |           |          |
 created_at | timestamp without time zone |           |          | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "idx_users_email" btree (email)

testdb=# \d+ users
                                                        Table "public.users"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target |  Description
------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+----------------
 id         | integer                     |           | not null |         | plain    |             |              |
 name       | text                        |           | not null |         | extended |             |              |
 email      | character varying(255)      |           |          |         | extended |             |              | メールアドレス
 address    | text                        |           |          |         | extended |             |              |
 created_at | timestamp without time zone |           |          | now()   | plain    |             |              |
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "idx_users_email" btree (email)
Access method: heap

select column_name, data_type, is_nullable, column_default
  from information_schema.columns
  where table_name = 'users'
  order by ordinal_position;

 column_name |          data_type          | is_nullable | column_default
-------------+-----------------------------+-------------+----------------
 id          | integer                     | NO          |
 name        | text                        | NO          |
 email       | character varying           | YES         |
 address     | text                        | YES         |
 created_at  | timestamp without time zone | YES         | now()
(5 rows)

select column_name, data_type, character_maximum_length, is_nullable
  from information_schema.columns
  where table_name = 'users'
  order by ordinal_position;

 column_name |          data_type          | character_maximum_length | is_nullable
-------------+-----------------------------+--------------------------+-------------
 id          | integer                     |                          | NO
 name        | text                        |                          | NO
 email       | character varying           |                      255 | YES
 address     | text                        |                          | YES
 created_at  | timestamp without time zone |                          | YES
(5 rows)

select a.attname as column_name,
       format_type(a.atttypid, a.atttypmod) as data_type,
       a.attnotnull as not_null,
       col_description(a.attrelid, a.attnum) as comment
  from pg_attribute a
  where a.attrelid = 'users'::regclass
    and a.attnum > 0
    and not a.attisdropped
  order by a.attnum;

 column_name |          data_type          | not_null |    comment
-------------+-----------------------------+----------+----------------
 id          | integer                     | t        |
 name        | text                        | t        |
 email       | character varying(255)      | f        | メールアドレス
 address     | text                        | f        |
 created_at  | timestamp without time zone | f        |
(5 rows)