# I hate postgres **URL:** \ **Category:** General Discussion\ **Created:** [September 9, 2024, 6:06pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748 "2024-09-09T18:06:05Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 9, 2024, 6:06pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/1 "2024-09-09T18:06:05Z")
Hah, I clickbaited you... But this is a rant that stems from a week of frustration. Why is everything so un-intuitive? I moved from a primary MySQL environment over 2 years ago to a fully MS SQL Server and it took me one day to switch environments. I currently work with databases daily in my role, about 50% Ignition development, 50% database. For those who will say that its only because I don't want to learn the specific ~~quirks~~ syntax of postgres (plpgsql), or don't understand its superiority as a highly scalable database, or that its because I'm too dumb - I agree with all of those things. Everything I try for the past few days has been like pulling teeth. I've tried both pgAdmin and DBeaver and I get similar results. For example, I have a `Type` table with only two columns, so I used the DBeaver GUI table editor and filled in three rows. I hit the `Save` button, closed the tab, then did a right-click -\> `View Table` to verify the data is in there. However, when I do a ```sql select * from public.mytypetable; ``` I don't get any data back... I'm sure there is something simple going on, but this demonstrates my point - I would guess that with no other database would I have problems doing something so trivial. ### Here are the other things that are weird that I noticed so far - The case sensitivity - our tables have mixed capitalization, so I need to use quotes around the table names and columns (i.e. dbo.Asset, Asset.AssetID, etc - if you think `public.assettypeextensionconfiguration` is easier to read than `dbo.AssetTypeExtensionConfiguration`, that is an opinion and I strongly disagree). - A postgres Procedure / Function is not the same thing as MySQL/MSSQL procedures and functions - you can force the postgres procedure to return a dataset, but then you need to work with cursors. I'm not saying this is necessarily a bad thing, functions/procedures just mean something different than what I'm used to. From what I've been reading in the docs, my MSSQL procedures should be postgres functions. - General user experience working in DBeaver/pgAdmin - getting unintuitive error messages, not being able to run even the simplest SQL statements, primary action buttons showing up small/on the bottom of the screen/etc. #### Benefits of postgres that I've noticed so far - Great JSON support - Open source (free) I know taking a stance online will guarantee me getting dunked on, but this has been my experience using `postgres` the last few days. And no, this post is not intended to bait @pturmel, though I do expect him to tell me how wrong I am 😉 ---
**Author:** ![sherman](https://avatars.discourse-cdn.com/v4/letter/s/e5b9ba/32.png) [@sherman](https://forum.inductiveautomation.com/u/sherman)\ **Post date:** [September 9, 2024, 6:17pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/2 "2024-09-09T18:17:00Z")
> [@YF129701](#): > > The case sensitivity - our tables have mixed capitalization, so I need to use quotes around the table names and columns (i.e. dbo.Asset, Asset.AssetID, etc - if you think `public.assettypeextensionconfiguration` is easier to read than `dbo.AssetTypeExtensionConfiguration`, that is an opinion and I strongly disagree). I do wish that IA would add a .lower() to any parsing of column names if the database type is postgres -- it is very annoying to have to go in and manually lowercase every column name in something like a Transaction Group (especially when there's a bug that doesn't reflect the change in the GUI [I know this is to be fixed soon]). @paul-griffith any chance that happens in an upcoming major revision? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [September 9, 2024, 6:26pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/3 "2024-09-09T18:26:19Z")
> [@YF129701](#): > > case sensitivity The SQL standard specifies case sensitivity for quoted identifiers. It specifies automatic normalization to upper case for unquoted identifiers. Only Oracle precisely follows this part of the standard. Postgres normalizes to lower case. MSSQL preserves case and uses case insensitive comparisons for unquoted identifiers. MySQL passes unquoted identifiers to the filesystem and stores/compares using that filesystem's methods. MSSQL and MySQL also do not use the standard's identifier quote character (double quote) unless explicitly configured to use ANSI standards mode. IMNSHO, MSSQL and MySQL are utterly broken in this regard. You simply cannot expect queries written for their defaults to be usable anywhere else. > [@YF129701](#): > > A postgres Procedure / Function is not the same thing as MySQL/MSSQL procedures and functions Yup, different. The SQL Standard doesn't really lock this down, as the various brands' procedural languages are all so different. PostgreSQL is closest to Oracle for this. > [@YF129701](#): > > working in DBeaver/pgAdmin I don't use either of these. DataGrip for the win. ---
**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\ **Post date:** [September 9, 2024, 6:31pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/4 "2024-09-09T18:31:59Z")
> [@YF129701](#): > > I hit the `Save` button, closed the tab, then did a right-click -\> `View Table` to verify the data is in there I'd have to look again, but I believe things in DBeaver are treated as transactions and you have to commit the transaction for it to take effect. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 9, 2024, 6:38pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/5 "2024-09-09T18:38:40Z")
The `Commit` button is greyed out - this was also my first thought. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [September 9, 2024, 6:44pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/6 "2024-09-09T18:44:24Z")
HeidiSQL is the best GUI I have tried so far (as a moderately experienced SQL user, not a power user) ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [September 9, 2024, 6:45pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/7 "2024-09-09T18:45:14Z")
I also settled on DataGrip. That said, I just loaded up DBeaver, and used it to mock up a table. Unfortunately, I'm not seeing the same thing. One error did see was about cross-database referencing, but that's because I don't have it enabled. > [@YF129701](#): > > The `Commit` button is greyed out Is Auto-commit turned on, then? EDIT: nvm, even with it off, it still completes, and is viewable, with the number of transactions counting above. I'm going to guess that it's turned on. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 9, 2024, 6:51pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/8 "2024-09-09T18:51:48Z")
I rebuilt that table from scratch, since it's only a few columns and rows. I paid special attention to the `Commit` and `Save` buttons - when I entered the row data, the `Save` button on the bottom of the screen turned green. At no point was the `Commit` button enabled. There may be other issues going on, so I'm exploring this further - thank you for your input! ---
**Author:** ![michael.flagler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/michael.flagler/32/26272_2.png) [@michael.flagler](https://forum.inductiveautomation.com/u/michael.flagler)\ **Post date:** [September 9, 2024, 7:03pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/9 "2024-09-09T19:03:54Z")
Out of curiosity, what has made you want to switch from MySQL or MSSQL to Postgres? I've wanted to try it out as well, but I'm most familiar with MySQL and MSSQL so have stuck with those 2 for a while. My most recent MySQL I switched from using InnoDB for the storage engine to RocksDB to try to save space, so we'll see how well it fares in production. ---
**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\ **Post date:** [September 9, 2024, 7:22pm UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/10 "2024-09-09T19:22:39Z")
It was out of necessity - some of our customers use Postgres. ---
**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\ **Post date:** [September 10, 2024, 4:16am UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/11 "2024-09-10T04:16:26Z")
> [@YF129701](#): > > Hah, I clickbaited you... Hah, you got me! I've had a lot on my schedule the last couple of months, and I'm way behind on my reading, but I simply couldn't imagine anybody hating Postgres, 🤣 ...so I clicked. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 23, 2024, 9:37am UTC](https://forum.inductiveautomation.com/t/i-hate-postgres/95748/12 "2024-11-23T09:37:34Z")
> [@YF129701](#): > > Why is everything so un-intuitive? Sounds like Linux 🤣