Postgres Pro
Downloads
Home   >   mailing lists

Thread: Need to check each element of an array satisfies a foreign key constraint

Need to check each element of an array satisfies a foreign key constraint

From
David Gauthier
Date:
Hi:

I have a column in a table which is a csv of values and I need to make sure each element of the csv = the PK of that same table.

create table projects (
  project varchar primary key,
  children_csv varchar );

insert into projects (project,children_csv) values 
('prj1',null),
('prj2',null),
('prj3','prj1,prj2');

I need to make sure that the elements of 'prj1,prj2' are both valid projects.

I'm thinking the csv should be split into an array (regexp_split_to_array) but the constraint needs to somehow iterate over each element to check that they are all valid.  

I suppose I could write a stored procedure to do this and call it in a check constraint. But I was wondering if there is something more elegant.

Thanks in Advance ! 

Re: Need to check each element of an array satisfies a foreign key constraint

From
"David G. Johnston"
Date:
On Tuesday, July 13, 2021, David Gauthier <davegauthierpg@gmail.com> wrote:

I suppose I could write a stored procedure to do this and call it in a check constraint. But I was wondering if there is something more elegant.


You cannot use a check constraint here as the behavior is not immutable.  You can use a trigger function though.  Or normalize the table and use the built-it foreign key triggers.

David J.
 

Re: Need to check each element of an array satisfies a foreign key constraint

From
David Gauthier
Date:
Ok, thanks. 

I was looking for, but didn't find, something like... each_element_of(regexp_split_to_array(children_csv)) references projects(project);
Of course the "each_element_of" is my creation here :-)

On Tue, Jul 13, 2021 at 10:07 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tuesday, July 13, 2021, David Gauthier <davegauthierpg@gmail.com> wrote:

I suppose I could write a stored procedure to do this and call it in a check constraint. But I was wondering if there is something more elegant.


You cannot use a check constraint here as the behavior is not immutable.  You can use a trigger function though.  Or normalize the table and use the built-it foreign key triggers.

David J.
 

Re: Need to check each element of an array satisfies a foreign key constraint

From
Ken Tanzer
Date:
On Tue, Jul 13, 2021 at 7:47 AM David Gauthier <davegauthierpg@gmail.com> wrote:
Ok, thanks. 

I was looking for, but didn't find, something like... each_element_of(regexp_split_to_array(children_csv)) references projects(project);
Of course the "each_element_of" is my creation here :-)


Several years ago, I had hopes for this, and still do.  It would be great for my use cases.  From what I remember, it was "complicated."  There was a patch proposed at least as far back as 9.3 for this[1].    Googling for this now though, I see there was some activity on it this year[2].  Does anyone know the status of this, or if/when this is likely to show up in Postgresql?  Thanks!

Ken



 

--
AGENCY Software  
A Free Software data system
By and for non-profits
(253) 245-3801

learn more about AGENCY or
follow the discussion.

Re: Need to check each element of an array satisfies a foreign key constraint

From
Laurenz Albe
Date:
On Tue, 2021-07-13 at 10:00 -0400, David Gauthier wrote:
> I have a column in a table which is a csv of values and I need to make sure each element of the csv = the PK of that
sametable.
 

This won't work, and it is broken by design.

Change your data model to adhere to the first normal form, and the
exercise will be simple.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com