Thank you, I’ll review the pg_dump algorithms, there should be some difference.

Hello Douglas,

Are the tables in the same schema or different?

Hello Sergey,

The tables are in the same schema.

Could you please add SQL definitions of both tables?
You can use a context menu for it: "Generate SQL"->"Create"

Table "estado" (Create):

BEGIN;

-- CREATE TABLE "estado" ---------------------------------------
CREATE TABLE "base_corporativa"."estado" (
"id" Integer NOT NULL,
"nome" Character Varying( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"sigla" Character Varying( 2 ) COLLATE "pg_catalog"."default" NOT NULL,
"area" Numeric( 13, 4 ) NOT NULL,
"the_geom" "public"."geometry",
PRIMARY KEY ( "id" ),
CONSTRAINT "enforce_dims_the_geom" CHECK(public.st_ndims(the_geom) = 2),
CONSTRAINT "enforce_geotype_the_geom" CHECK((public.geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (the_geom IS NULL)),
CONSTRAINT "enforce_srid_the_geom" CHECK(public.st_srid(the_geom) = 4674) );
;
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id" -----------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."id" IS 'Identificador único da entidade estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome" ---------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."nome" IS 'Armazena o nome do estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "sigla" --------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."sigla" IS 'Armazena a sigla do estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "area" ---------------------------
COMMENT ON COLUMN "base_corporativa"."estado"."area" IS 'Area do estado (ha).';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "the_geom" -----------------------
COMMENT ON COLUMN "base_corporativa"."estado"."the_geom" IS 'Coluna georreferenciada.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "TABLE "estado" -------------------------
COMMENT ON TABLE "base_corporativa"."estado" IS 'Entidade responsável por armazenar os estados.';
-- -------------------------------------------------------------

-- CREATE INDEX "sidx_estado_the_geom" -------------------------
CREATE INDEX "sidx_estado_the_geom" ON "base_corporativa"."estado" USING gist( "the_geom" );
-- -------------------------------------------------------------

COMMIT;

//

Table "municipio" (Create):

BEGIN;

-- CREATE TABLE "municipio" ------------------------------------
CREATE TABLE "base_corporativa"."municipio" (
"id" Integer NOT NULL,
"nome" Character Varying( 200 ) COLLATE "pg_catalog"."default" NOT NULL,
"id_estado" Integer NOT NULL,
"area" Numeric( 13, 4 ) NOT NULL,
"the_geom" "public"."geometry",
"nome_sem_formatacao" Character Varying( 200 ) COLLATE "pg_catalog"."default",
PRIMARY KEY ( "id" ),
CONSTRAINT "enforce_dims_the_geom" CHECK(public.st_ndims(the_geom) = 2),
CONSTRAINT "enforce_geotype_the_geom" CHECK(((public.geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (public.geometrytype(the_geom) = 'POLYGON'::text)) OR (the_geom IS NULL)),
CONSTRAINT "enforce_srid_the_geom" CHECK(public.st_srid(the_geom) = 4674) );
;
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id" -----------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."id" IS 'Identificado único da entidade municipio.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome" ---------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."nome" IS 'Armazena o nome do município.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "id_estado" ----------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."id_estado" IS 'Identificador da entidade estado que realiza o relacionamento entre as entidades municipio e estado.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "area" ---------------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."area" IS 'Área do município (ha).';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "the_geom" -----------------------
COMMENT ON COLUMN "base_corporativa"."municipio"."the_geom" IS 'Coluna georreferenciada.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "FIELD "nome_sem_formatacao" ------------
COMMENT ON COLUMN "base_corporativa"."municipio"."nome_sem_formatacao" IS 'Nome sem formatação.';
-- -------------------------------------------------------------

-- CHANGE "COMMENT" OF "TABLE "municipio" ----------------------
COMMENT ON TABLE "base_corporativa"."municipio" IS 'Entidade responsável por armazenar os municipios.';
-- -------------------------------------------------------------

-- CREATE INDEX "sidx_municipio_the_geom" ----------------------
CREATE INDEX "sidx_municipio_the_geom" ON "base_corporativa"."municipio" USING gist( "the_geom" );
-- -------------------------------------------------------------

COMMIT;

BEGIN;

-- CREATE LINK "fk_m_estado" -----------------------------------
ALTER TABLE "base_corporativa"."municipio"
ADD CONSTRAINT "fk_m_estado" FOREIGN KEY ( "id_estado" )
REFERENCES "base_corporativa"."estado" ( ) MATCH SIMPLE
ON DELETE No Action
ON UPDATE No Action;
-- -------------------------------------------------------------

COMMIT;

Thank you, for some reason, the PK field is not defined for the link fk_m_estado.
What version of PostgreSQL is used?

PostgresSQL 9.2.15 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit

Can't reproduce yet.

What if you execute the following query:
SELECT column_name FROM information_schema.constraint_column_usage WHERE constraint_schema = 'base_corporativa' AND constraint_name = 'fk_m_estado'

Do you get the primary key column?

Does not get.

See the attachment.

Thank you, that's very strange.
What if you execute
SELECT * FROM information_schema.constraint_column_usage

I attached my output - id column is here.

Really, very strange.

In attachment, the output for the query SELECT * FROM information_schema.constraint_column_usage

Could you please create a SQL dump of the "municipio" table? If it contains the correct definition:
ALTER TABLE ONLY base_corporativa.municipio
ADD CONSTRAINT fk_m_estado FOREIGN KEY (id_estado) REFERENCES base_corporativa.estado(id);

then I can check the pg_dump sources to see how it gets information about the foreign keys.

Also, if it is possible to create a new test database, uncompress and load attached dump into it - is the issue reproducible (no id column for foreign key in information_schema.constraint_column_usage) ?

In the dump of table "municipio" constains the definition in attachment.

About creating the test database, unfortunately I can not do it at the moment. Sorry.

Thanks, Sergey.

Sergey, I created the test database and gave the restore in its dump.

I ran the query SELECT * FROM information_schema.constraint_column_usage and the output is attached.

I discovered the reason for the problem. It is the permission of a specific user that I was using to connect to the database.

Thanks for your help, Sergey.