search

martinjw / dbschemareader

Read database metadata (from SqlServer/Oracle/MySql/SQLite/PostgreSql/DB2 etc) into one simple model
Microsoft Public License
293 stars 128 forks source link

PostgreSQL: DatabaseIndex.IsUnique is always false when loaded from database #141

Closed Arnagos closed 2 years ago

Arnagos commented 2 years ago

When loading a schema from a PostgreSQL database the IsUnique property on indices is always false.

The index was created with this SQL:

CREATE UNIQUE INDEX "Punchclock__Company_CeoId_idx" ON "Punchclock__Company"("CeoId");

And prompting the database to generate a creation script generates this SQL:

CREATE UNIQUE INDEX IF NOT EXISTS "Punchclock__Company_CeoId_idx"
    ON public."Punchclock__Company" USING btree
    ("CeoId" ASC NULLS LAST)
    TABLESPACE pg_default;

Based on this I'm pretty sure it's a loader issue.

Arnagos commented 2 years ago

Seems like the Loader does not set this value for PostgreSQL.

The query set in the constructor of the same class doesn't even request the required column.

Adding ix.indisunique as index_is_unique, to the select should provide the necessary value for this. And if you want to ignore the primary key indices ix.indisprimary as index_is_primary, should help.

image

Arnagos commented 2 years ago

I just noticed that the index type is not set either. Maybe ix.indisclustered can help with this?