Retool PostgreSQL - Create table with Foreign Key - PK not listed as target

💬 Queries and Resources
1

I'm using the built-in Retool PostgreSQL database. Today, I see that when I go to use the Retool UI (via https://healthcareitleaders.retool.com/resources/data/... and clicking the (+) at top-left) to create a new table, and in that table attempt to define a foreign key field, the drop-down for the FK target contains nearly all the attributes of that table ... except the PK that I really want to target. (It also excludes booleans, but that's not surprising.)

It's doing this across all the tables I've tried pointing my FK at. I've verified (via DBeaver) that the ID fields do indeed exist and the PK constraints are still present. I half-expected that the "Select a $column" option might somehow BE the ID (i.e. the display-name might be wrong, but maybe internally it's what I want?), but inspecting that option in developer-tools shows it has a value of "", not the absent field name.

I've used this screen many times to create tables and I've never seen this happen before. (But it's been a few weeks since I created a new table, so I can't tell you exactly when this problem started.)

The tables I'm trying to target with the FK were all created through the retool UI, using its particular idioms (int4, auto-created sequence, auto-created primary key constraint).

I'm including a screenshot of the UI failing to show fields, and of freshly-generated DDL for the table I was trying to target in said screenshot, to confirm they exist.

image
image591×417 12.9 KB

image
image516×357 13 KB

2

Not a surprise, but thought I'd confirm the same thing happens if trying to add a new FK field to an existing table. (It's a different screen, so it was worth verifying.)

image
image409×487 13.3 KB

3

Thanks for reaching out, @philip.williams.healthcareitleaders! I see the same thing on my end, so this is definitely a systemic bug. I'll document your findings internally and alert the owning team. :+1: As soon as there is news to share, I'll provide an update here!

1 Like
4

Any update on this? This is a critical and breaking issue for us.

5

No updates yet, @J_John - it's hard to give any concrete timeline at this point, but I don't think this needs to be a significant blocker, fortunately.

If you proceed with generating those columns with the generic Number preset, you can subsequently connect to the DB - either with psql or a DB management tool like pgAdmin or DBeaver - and add the necessary constraints with a query similar to the following:

ALTER TABLE table_name
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_name)
REFERENCES access_privileges(privilege_id)
ON DELETE CASCADE -- CASCADE, RESTRICT, SET NULL, or SET DEFAULT
ON UPDATE CASCADE; -- CASCADE, RESTRICT, SET NULL, or SET DEFAULT

I'm happy to help tweak the query in order to meet your specific needs!

1 Like
6

+1 from me, same issue.

7

The release of version 3.266 will include a fix for this particular issue!