Postgres - Foreign Key Violation

[ainsleyclark]

Describe the Bug

Originally mentioned in: https://github.com/payloadcms/payload/issues/6845

I'm encountering a foreign key constraint violation errors when attempting to insert or update an array in a table. This issue seems to be related to foreign key references within the PostgreSQL adapter.

More specifically the path would be: array.group.select

[10:25:11] ERROR: error: insert or update on table "categories_capabilities_activities" violates foreign key constraint "categories_capabilities_activities_parent_fk"

I think the problem is linked to how activities are grouped within the categories field in our schema. Here is a simplified example of the field setup:

export const categories: Field[] = [
  {
    name: 'categories',
    label: 'Categories',
    type: 'array',
    fields: [
      {
        name: 'capabilities',
        label: 'Capabilities',
        type: 'group',
        fields: [
          {
            name: 'activities',
            label: 'Activities',
            type: 'select',
            hasMany: true,
            options: [
              { label: 'Web Development', value: 'web-development' },
              { label: 'Machine Learning', value: 'machine-learning' },
              // Other activity options...
            ],
          },
        ],
      },
    ],
  },
];

Link to the code that reproduces this issue

https://github.com/ainsleyclark/payload-postgres-bug

Reproduction Steps

Clone Repo for Details

• Create or update a record with activities under capabilities.
• Attempt to save or update the entry in Payload CMS.

Which area(s) are affected? (Select all that apply)

db-postgres

Environment Info

Binaries: Node: 21.7.3 npm: 10.8.1 Yarn: 1.22.22 pnpm: 9.7.0 Relevant Packages: payload: 3.0.0-beta.114 next: 15.0.0-canary.173 @payloadcms/db-postgres: 3.0.0-beta.114 @payloadcms/email-nodemailer: 3.0.0-beta.114 @payloadcms/graphql: 3.0.0-beta.114 @payloadcms/next/utilities: 3.0.0-beta.114 @payloadcms/plugin-cloud: 3.0.0-beta.114 @payloadcms/plugin-cloud-storage: 3.0.0-beta.114 @payloadcms/plugin-seo: 3.0.0-beta.114 @payloadcms/richtext-lexical: 3.0.0-beta.114 @payloadcms/translations: 3.0.0-beta.114 @payloadcms/ui/shared: 3.0.0-beta.114 react: 19.0.0-rc-3edc000d-20240926 react-dom: 19.0.0-rc-3edc000d-20240926 Operating System: Platform: darwin Arch: arm64 Version: Darwin Kernel Version 24.0.0: Tue Sep 24 23:37:36 PDT 2024; root:xnu-11215.1.12~1/RELEASE_ARM64_T6020 Available memory (MB): 65536 Available CPU cores: 12

[ainsleyclark]

Thank you @r1tsuu !

[r1tsuu]

Fixed in https://github.com/payloadcms/payload/pull/8737

[github-actions[bot]]

🚀 This is included in version v3.0.0-beta.116

[kotyperry]

@r1tsuu I believe there is another issue related to this somehow.

I have a select field inside of a tab group. When loading the component, I get an error saying

Error: column "value" cannot be cast automatically to type enum__work_v_version_options_featured_capabilities

Here is how my schema is structured.

{
      type: 'tabs',
      tabs: [
        {
          fields: [
            {
              name: 'layout',
              type: 'blocks',
              blocks: [CoverBlock, ImageBlock, ContentBlock, VideoBlock],
              required: true,
            },
          ],
          label: 'Content',
        },
        {
          name: 'meta',
          label: 'SEO',
          fields: [
            OverviewField({
              titlePath: 'meta.title',
              descriptionPath: 'meta.description',
              imagePath: 'meta.image',
            }),
            MetaTitleField({
              hasGenerateFn: true,
            }),
            MetaImageField({
              relationTo: 'media',
            }),
            MetaDescriptionField({}),
            PreviewField({
              // if the `generateUrl` function is configured
              hasGenerateFn: true,
              // field paths to match the target field for data
              titlePath: 'meta.title',
              descriptionPath: 'meta.description',
            }),
          ],
        },
        {
          name: 'options',
          label: 'Options',
          fields: [
            {
              name: 'featured_image',
              label: 'Featured Image',
              type: 'upload',
              relationTo: 'media',
            },
            {
              name: 'short_description',
              label: 'Short Description',
              type: 'textarea',
            },
            {
              name: 'featured_capabilities',
              label: 'Featured Capabilities',
              type: 'select',
              hasMany: true,
              options: [
                { label: 'Branding', value: 'branding' },
                { label: 'Copywriting', value: 'copywriting' },
                { label: 'Print', value: 'print' },
                { label: 'Video', value: 'video' },
                { label: 'Digital', value: 'digital' },
                { label: 'Non Profit', value: 'nonprofit' },
                { label: 'Insights', value: 'insights' },
                { label: 'Packaging', value: 'packaging' },
                { label: 'PR', value: 'pr' },
                { label: 'Social', value: 'social' },
                { label: 'Web', value: 'web' },
                { label: 'Paid Media', value: 'media' },
                { label: 'Photography', value: 'photography' },
                { label: 'Featured', value: 'featured' },
              ],
            },
          ],
        },