PgSqlDataTable doesn't create Constraints and PrimaryKeys

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

PgSqlDataTable doesn't create Constraints and PrimaryKeys

Post by chris901 » Sat 15 Oct 2016 19:32

Hello,

in my database I have a table articles where I store various information about articles.

A simplified version of the table is:

Code: Select all

create table articles
(
  article_id      serial,    -- 1
  name            text,      -- Dell Laptop
  number          text,      -- A0001-DL

  primary key (article_id)
);
I set a query up like this...

Code: Select all

select article_id,
       name,
       number
from articles
order by article_id;
... and use it in a PgSqlDataTable.

The strange thing is that no constraints or primary key (the properties of PgSqlDataTable) get created.

Screenshot: http://i.imgur.com/oar4NTP.png

I figured out that this happens because of the article number column that is named number.

For testing purposes I renamed the column number to numbera and the constraints and primary key got added correctly.

Constraints and Primary Key will also not get created when there is a subselect inside the SelectCommand.

So for example:

Code: Select all

select article_id,
       (select pg_backend_pid())
from articles
order by article_id;
I would appreciate a fix for those issues.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDataTable doesn't create Constraints and PrimaryKeys

Post by Pinturiccio » Wed 19 Oct 2016 13:17

We could not reproduce the issue. We have the same behaviour when a column has the name 'number' or 'numbera'. Constraints and PrimaryKeys are created when CreateColumns is called. Make sure that you have invoked CreateColumns in the case with the 'number' column name.
chris901 wrote:Constraints and Primary Key will also not get created when there is a subselect inside the SelectCommand.
We cannot obtain the schema of such a query, and Constraints and PrimaryKeys are not created for such queries.

chris901
Posts: 64
Joined: Wed 20 Jul 2016 04:21

Re: PgSqlDataTable doesn't create Constraints and PrimaryKeys

Post by chris901 » Wed 19 Oct 2016 19:26

That weird. It definitely isn't working for me if the column is named number.

Once Visual Studio 2016 drops in November I'll setup a clean install and report back then.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDataTable doesn't create Constraints and PrimaryKeys

Post by Pinturiccio » Fri 13 Jan 2017 14:40

We have fixed the bug with not creating Constraints and Primary Keys collections for PgSqlDataTable with a query that contains a subquery. We will post here when the corresponding build of dotConnect for PostgreSQL is available for download.

Pinturiccio
Devart Team
Posts: 2192
Joined: Wed 02 Nov 2011 09:44

Re: PgSqlDataTable doesn't create Constraints and PrimaryKeys

Post by Pinturiccio » Mon 16 Jan 2017 16:03

New build of dotConnect for PostgreSQL 7.7.819 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/postgr ... nload.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to viewtopic.php?t=34832

Post Reply