Understanding the output of \d <table> in psql

Question

Say I run the following command:

\d mtb.big_table

I get the output shown below. My questions are:

1. What are column modifiers? (what does default now() mean?)
2. Under Indexes, Why do some indices refer to multiple columns? (I thought columns can only refer to one column)
3. What is the relationship between Foreign-key constraints when it talks about REFERENCES and the next group below that says "Referenced by" ? (why are there only two Foreign-key constraints and so many TABLE references?).

For example, the following entry:

TABLE "dt.table_segment_hierarchy" CONSTRAINT "dx_segment_hierarchy_ancestor_segment_uid_fkey" FOREIGN KEY (ancestor_segment_uid) REFERENCES dt.table_segment(object_uid)

reads:

TABLE <table_1> CONSTRAINT <column_1> FOREIGN_KEY (<column_2>) REFERENCES <table_2>(<column_3>)

What is the relationship between these tables and columns?

---

More generally, where can I find more information about the formatting of the output of the \d command?

                                                   Table "dt.table_segment"
         Column         |            Type             |                              Modifiers                              
------------------------+-----------------------------+---------------------------------------------------------------------
 segment_id             | integer                     | not null default nextval('dt.table_segment2_segment_id_seq'::regclass)
 segment_type           | character varying(31)       | not null
 object_uid             | character varying(255)      | not null
 object_version         | timestamp without time zone | default now()
 name                   | character varying(255)      | not null
 is_active              | boolean                     | not null
 expiration             | integer                     | 
 expiration_unit        | character varying(255)      | 
 frequency_count        | integer                     | 
 matching_expression    | character varying(32672)    | 
 data_vendor_uid        | character varying(255)      | not null
 creation_date          | timestamp without time zone | not null default now()
 researcher_uid         | character varying           | 
 sharing_enabled        | boolean                     | not null default false
 compressed_id          | integer                     | 
Indexes:
    "dx_segment_pkey" PRIMARY KEY, btree (object_uid)
Foreign-key constraints:
    "dx_segment_researcher_fk" FOREIGN KEY (researcher_uid) REFERENCES dt.table_researcher(object_uid)
    "fkeef87e8854520518" FOREIGN KEY (data_vendor_uid) REFERENCES dt.table_data_vendor(object_uid)
Referenced by:
    TABLE "dt.table_segment_hierarchy" CONSTRAINT "dx_segment_hierarchy_ancestor_segment_uid_fkey" FOREIGN KEY (ancestor_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_hierarchy" CONSTRAINT "dx_segment_hierarchy_descendant_segment_uid_fkey" FOREIGN KEY (descendant_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_shared_organization_segments" CONSTRAINT "dx_shared_organization_segments_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_external_id" CONSTRAINT "fk3f8c6d84c87bfc0" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_group_rate" CONSTRAINT "fk710100c4a72a47f" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_group_rate" CONSTRAINT "fk710100c4b8416291" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_action" CONSTRAINT "fk7c000342a72a47f" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_action" CONSTRAINT "fk7c000342b7946789" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_edge" CONSTRAINT "fk9c237d147cc05c6a" FOREIGN KEY (parent_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_segment_edge" CONSTRAINT "fk9c237d14a102ddc" FOREIGN KEY (child_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "dt.table_flight_group" CONSTRAINT "fkbeae4f48b8416291" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_assets_load" CONSTRAINT "ls_assets_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_attributions_load" CONSTRAINT "ls_attributions_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_group_converters_load" CONSTRAINT "ls_group_converters_load_group_uid_fkey" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_group_unique_users_load" CONSTRAINT "ls_group_unique_users_load_group_uid_fkey" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_campaign_segment_load" CONSTRAINT "ls_campaign_segment_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_creative_exchange_hourly_load" CONSTRAINT "ls_creative_exchange_hourly_load_group_uid_fkey" FOREIGN KEY (group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_impressions_load" CONSTRAINT "ls_impressions_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_related_segment_load" CONSTRAINT "ls_related_segment_load_related_segment_uid_fkey" FOREIGN KEY (related_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_related_segment_load" CONSTRAINT "ls_related_segment_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_segment_load" CONSTRAINT "ls_segment_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_top_interest_load_cant_modify" CONSTRAINT "ls_top_interest_load_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_top_interest_load" CONSTRAINT "ls_top_interest_load_segment_uid_fkey1" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_hourly_load" CONSTRAINT "ls_winning_segment_hourly_load_winning_group_uid_fkey" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_hourly_load" CONSTRAINT "ls_winning_segment_hourly_load_winning_segment_uid_fkey" FOREIGN KEY (winning_segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_list_segments" CONSTRAINT "ls_winning_segment_list_segments_segment_uid_fkey" FOREIGN KEY (segment_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_aggregate_data" CONSTRAINT "winning_segment_aggregate_group_fk" FOREIGN KEY (winning_group_uid) REFERENCES dt.table_segment(object_uid)
    TABLE "fb.ls_winning_segment_aggregate_data" CONSTRAINT "winning_segment_aggregate_segment_fk" FOREIGN KEY (winning_segment_uid) REFERENCES dt.table_segment(object_uid)

Answer 1

re 1: that's the default value you define when you create the table:

create table foo 
(
  id integer default 42,
  last_modified timestamp default now()
)

Details are in the manual:

• http://www.postgresql.org/docs/current/static/ddl-default.html
• http://www.postgresql.org/docs/current/static/sql-createtable.html

re 2: an index can be defined on multiple columns:

create table foo
(
  id_1 integer not null, 
  id_2 integer not null
);
create unique index on foo (id_1, id_2);

Again details are in the manual:

• http://www.postgresql.org/docs/current/static/indexes.html
• http://www.postgresql.org/docs/current/static/indexes-multicolumn.html
• http://www.postgresql.org/docs/current/static/sql-createindex.html