Início / dba / Perguntas / 58468
Accepted
Amelio Vazquez-Reina
Asked: 2014-02-08 10:55:35 +0800 CST

Entendendo a saída de \d <table> em psql

  • 772

Digamos que eu execute o seguinte comando:

\d mtb.big_table

Eu recebo a saída mostrada abaixo. Minhas perguntas são:

  1. O que são modificadores de coluna? (o que default now()significa?)
  2. Em Indexes, Por que alguns índices se referem a várias colunas? (Eu pensei que as colunas só podem se referir a uma coluna)
  3. Qual é a relação entre Foreign-key constraintsquando se fala REFERENCESe o próximo grupo abaixo que diz "Referenced by"? (por que existem apenas duas Foreign-key constraintse tantas referências a TABLE?).

Por exemplo, a seguinte entrada:

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

lê:

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

Qual é a relação entre essas tabelas e colunas?

De forma mais geral, onde posso encontrar mais informações sobre a formatação da saída do \dcomando?

                                                   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)
postgresql index

1 respostas

  1. Best Answer

    re 1: esse é o valor padrão que você define ao criar a tabela:

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

    Os detalhes estão no manual:

    re 2: um índice pode ser definido em várias colunas:

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

    Mais uma vez, os detalhes estão no manual:

    • 4

relate perguntas