Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
1  
of course a "column can only refer to a single column". An index however can be defined on multiple columns. –  a_horse_with_no_name Feb 7 at 19:03

1 Answer 1

up vote 4 down vote accepted

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:

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:

share|improve this answer
    
Thanks! This is very helpful. I added one more subquestion - I hope it's easy to answer with the info that you already provided. –  user815423426 Feb 7 at 19:21
    
@user815423426: foreign keys are documented here: postgresql.org/docs/current/static/tutorial-fk.html Please do read the manual before posting questions –  a_horse_with_no_name Feb 7 at 19:28
    
@user815423426: (re 3:) Outgoing fks are listed under Foreign-key constraints:, incoming fks are listed under Referenced by:. –  Erwin Brandstetter Feb 7 at 20:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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