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
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
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]). @PGriffith any chance that happens in an upcoming major revision?
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.
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.
I don't use either of these. DataGrip for the win.
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.
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.
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!
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.
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.
...so I clicked.