-
-
Notifications
You must be signed in to change notification settings - Fork 445
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
foreign key dropdown doesn't handle citext on postgres #1931
Comments
I was just trying to create a test table with a CITEXT column, but to my surprise, on Postgesql 15.1, I get an SQL error saying the type "CITEXT" does not exist: CITEXT Documentation says it should work from PostgreSQL 12.0+ so it should work on my local server. But the documentation also says something about a module installation, and I did not install any module. I have no clue what that means. Can you shed some light on that? |
Yes, citext is included with the default distribution but it isn't enabled by default - you have to do something like:
to enable it for each database you want to use it in. |
Thanks for the tip. That did it.
Note the value in I'm crossing fingers the hardcoded oid HeidiSQL/source/dbstructures.postgresql.pas Line 239 in 4ac4152
|
Yes - unfortunately - I think the oid changes from server to server/database to database. I was recently doing some similar work and noticed on my machine that citext was 2606673 but on the production server it was 59586 - i think the built in types are always the same, but ones from extensions like citext will vary. Hopefully that won't throw a wrench in things - if you want, i can download tomorrows build and verify for sure that it doesn't work, but I'm reasonably confident it won't if it is counting on 24651 being associated with citext. |
Yes, that would be helpful if you give it a test and give some feedback. The new build should be available in roughly half an hour. |
Similar issue in a different project: launchbadge/sqlx#295 |
Yep - not sure if it helps, but the SQL to get it seems to be: SELECT 'citext'::regtype::oid; and I did confirm it is different on every server I tried. Also, this article discusses getting it via the C api: https://stackoverflow.com/questions/45887484/get-user-defined-type-oid-in-postgresql-extension-c |
… dynamically, which is different on each server/database.
I decided for the Minor drawback is that this query is fired once on each session, anyway if there is a citext type or not. This is due to a still hardcoded list of data types in HeidiSQL, with citext being one of them. Only dynamic thing is the oid of citext now. Looking at the |
When editing int identity columns, in addition to the identity key, Heidi also usually displays the first varchar or text column in hopes of making things more human readable. I noticed that if the FK table is using "citext" instead of "text" columns it doesn't work (the identity numbers still show up in the dropdown but not the human readable part). If I change the column from "citext" to "text" and refresh, it works as expected.
Unfortunately, using case insensitive coalations even in this day and age with postgres is often problematic for many reasons so we and many others use citext to work around these limitations. Would be nice if Heidi worked for citext as well as varchar and text on postgres.
The text was updated successfully, but these errors were encountered: