distal-attribute
distal-attribute
distal-attribute
distal-attribute

Postgres SQL (please)

User, date Message
Written by Dartdog
4 years ago
Category: Feature discussion
6 posts since Wed, 20 Aug 08
I love Heidi but as the various issues of Mysql emerge I keep looking longingly at Postgres. It could really use some better high quality support products like Heidi!Wish I had the skill base to do, rather than ask.:-(
Written by igorky
2 years ago
2 posts since Tue, 28 Aug 12
Heidi excellent tool, but I moved to the PostgresSQLunhappy
I'll wait for the PostgresSQL support.
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
Oh I don't think that happens in my life... I am having a hopelessly incomplete MS SQL feature list in HeidiSQL already. Well, isn't there a free PostGres GUI out there?
Written by igorky
2 years ago
2 posts since Tue, 28 Aug 12
Yes, but the pgadmin is unusable after years with the Heidiunhappy
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
Hehe. So, why not stick to MySQL?
Written by cferrero
2 years ago
2 posts since Thu, 30 Aug 12
Please, support PostgreSQL database with Heidisql
Por favor, soporten PostgreSQL desde Heidisql
Written by Allineer
2 years ago
47 posts since Thu, 01 Jul 10
+1 vote for PostgreSQL support :)
Written by gsegues
2 years ago
1 posts since Wed, 31 Oct 12
I'm starting in GIS, and need change from mysql to Postgres/Postgis. And after trying several GUI interfaces, I longs for HeidiSQL.


Written by ajsouza
1 year ago
1 posts since Thu, 23 May 13
one vote more to HeidiSQL Postgre :D
Written by ansgar
1 year ago
4940 posts since Fri, 07 Apr 06
Likely I get flamed for that questionsmilebut hasn't PostgreSQL become less popular in the recent years? Is there some usage statistics out there, couldn't find something.
Written by jrgppp
1 year ago
1 posts since Thu, 23 May 13
We suggest connecting to PostgreSQL, it would be very interesting.
Written by nahuelon
1 year ago
4 posts since Sat, 20 Jul 13
It will be an excellent parternship: the best RDBMS with the best SQL client.

+1 for heidisql + postgresql
Written by fbachofner
12 months ago
25 posts since Sun, 11 Aug 13
I prefer HeidiSQL tremendously to alternatives which work with PostgreSQL such as pgadmin and SquirrelSQL.

Adding PostgreSQL support would be awesome!
Written by kalvaro
12 months ago
587 posts since Thu, 29 Nov 07
Supporting a new DBMS is a huge work. The experimental SQL Server support introduced in 2011 is hardly usable more than two years later. Adding yet another incomplete major feature would hardly be a benefit for anyone.
Written by ansgar
12 months ago
4940 posts since Fri, 07 Apr 06
I must admit that's quite true, shame on me. But I think that's more due to the fact that I'm personally not very bound to MSSQL in special, whereas I'm a MySQL user for about 13 years now. The fact that MSSQL support took 2 years is probably a sign that it will never improve much more than its current state. On the other side, quite recently, I fixed several things for MSSQL, and introduced schema support. So, it's probably usable now, only not in a degree as it's for MySQL. For further rdbms support as PostgreSQL, if I'm honest, things will look similar as for MSSQL. I have zero experience with PostgreSQL and probably more important, I have personally no use for it. Probably this is something where someone can fork HeidiSQL's sources and put PostgreSQL into it, or merge it with official sources.
Written by Barium
12 months ago
3 posts since Mon, 12 Aug 13
I have used MySQL a lot over the years, but it always falls short on a few key points. The poor implementations of the SQL standards, the lack of proper transactional features, and lack of proper foreign key constraints. While some may argue that this or that version of MySQL have these features, I have long ago found them in PostgreSQL. Besides PostgrelSQL is not part of Oracle which I like (though I do work with Oracle at work smile).

However MySQL have one thing, that PostgreSQL (and even Oracle) do not... HeidiSQL, this is by far the best SQL interface I have tried, beating the ever so slow SQL Developer by miles, and anything PostgreSQL has to offer. So naturally being a PostgreSQL user, I would love to see HeidiSQL made usable for PostgreSQL.

On the note of it being less popular than MySQL, then that is absolutely true, MySQL is more of a mainstream product, and is thus much more popular. But perhaps not necessarily by the users operating the most demanding database systems.

However proper statistics are difficult to find, since it can at best be difficult to find out the number of installations of a given database, so I have taken the number of searches for the different databases. This link shows the google search statistics for the four (MySQL, PostgreSQL, Oracle and MSSQL)

http://www.google.com/trends/explore?q=postgresql+mysql#q=mysql%2C%20postgresql%2C%20mssql%2C%20oracle&cmpt=q

Not surprisingly MySQL and Oracle are the most popular, being the two most widespread systems, MySQL provided by nearly all hosting companies, used to operate lots of personal PHP websites by happy amateurs (really this is no offense).smile

But it is interesting to note that PostgreSQL appears to be more popular than MSSQL which is supported by HeidiSQL (another good argument to try it)smileAnd if you add another of the old giants DB2 it is close to the popularity of PostgreSQL. While the curve is decreasing for PostgreSQL, so is it for both Oracle and MySQL, and by a larger percentage.

Anyway, I would welcome HeidiSQL as a PostgreSQL frontend, it would make the best free (my opinion) database system, even better, as it has greatly improved MySQL. :)
Written by fbachofner
12 months ago
25 posts since Sun, 11 Aug 13
Does HeidiSQL not have an abstraction layer for database connectivity?

In no way do I want to minimize the complexity of adding additional database support (I just spent the better part of a day finding I had used a "-" where I normally use "_" !), but some vendors make it seem almost "easy" to add support for databases which are standardized to some extent.

SQuirreLSQL, for example supports pretty much every SQL DB in existence as long as they have a JDBC driver.
Written by fbachofner
12 months ago
25 posts since Sun, 11 Aug 13
Hi Barium:


I took a look at your trend and replaced Oracle with Firebird (http://www.firebirdsql.org/) with interesting results (FB trends slightly higher than PostgreSQL!):

http://www.google.com/trends/explore?q=postgresql+mysql#q=mysql%2C%20postgresql%2C%20mssql%2C%20firebird&cmpt=q


While we are on the topic of requesting additional DBs to be supported by HeidiSQL, Firebird would be an absolute dream.

One really nice thing about Firebird (in addition to better ANSI SQL compliance than MySQL) is that a database IN ITS ENTIRETY is contained in ONE file. This makes Firebird databases very easy to copy, distribute, etc. and certainly makes a databases folder a lot cleaner (and less intimidating) looking than MySQL and *especially* PostgreSQL.

I'm not holding my breath on widespread support for Firebird though. I think Firebird will take a little longer to gain traction than PostgreSQL ... at least until the benefits are better marketed and understood.
Written by ansgar
12 months ago
4940 posts since Fri, 07 Apr 06

fbachofner wrote: Does HeidiSQL not have an abstraction layer for database connectivity?



Yes, I had introduced a home brown object oriented layer with a superclass and one subclass per rdbms in Oct 2009. Quite a few methods are not even derived in the subclasses, so finally it's not tons of lines to implement for another rdbms, just the connectivity, some SQL specialties, query result handling, column types and so on.

But there are major parts which I have overseen when I first implemented MSSQL support. For example the table editor was unusable due to a different SQL syntax in nearly each internal query. Also, schemata support is required when handling with MSSQL, but that was totally missing as HeidiSQL was a pure MySQL client before. Some dialogs are even dedicated to some special MySQL functionality, as for example the "maintenance" dialog, where you can OPTIMIZE and REPAIR tables. And, does PostgreSQL support extended INSERTs, like those created in the "SQL export" dialog?

Well, it's getting more and more usable, now that HeidiSQL supports table editing and schemata. Perhaps it's not such a big deal adding support for a third rdbms. Only don't expect code to be stable so soon :)
Written by fbachofner
12 months ago
25 posts since Sun, 11 Aug 13
Hi ansgar:

How did you cite my comment? I don't have a quote button or anything in settings which indicates this ability. Are you manually entering "<p class="cite"><strong>" etc?

You say: "Perhaps it's not such a big deal adding support for a third rdbms."

I like your attitude, but I do think it will be quite difficult. On the other hand, with your incredible coding talent and the fact that you have an abstraction layer (even a home grown one) you will save a lot of time with each additional DB you may someday support.

It would be really nice if such support were as easy to add as a language file for a multi-language app!
Written by ansgar
12 months ago
4940 posts since Fri, 07 Apr 06
Tada... see the new "Quote" link on the left hand of each post. :)
Written by fbachofner
12 months ago
25 posts since Sun, 11 Aug 13

ansgar wrote: Tada... see the new "Quote" link on the left hand of each post. :)



I DO!

Very cool.
Written by fbachofner
12 months ago
25 posts since Sun, 11 Aug 13
Next forum feature you "need" is the ability to edit posts.

. . . see I could have put this additional comment with the last one and saved some space!

Now, back to the discussion of PostgreSQL and . . . Firebird!
Written by yahyasonmez
6 months ago
2 posts since Sat, 08 Feb 14
MySql together PostgreSql support required.
Written by panofish
4 months ago
7 posts since Wed, 23 Feb 11
PostgreSQL support would be awesome. I do understand your reluctance and the added effort you would be taking on.

Perhaps it would be an interesting exercise to create a branch for your code and begin an attempt to make a version of HeidiSQL that is PostgreSQL only? You may discover the effort is less (or more) than you think. You could stop that branch whenever you like. :)
Written by pizza111
4 months ago
19 posts since Fri, 01 Nov 13
Firebird please, because firebird comes from Delphi!
Written by jfalchMoney, Euro
4 months ago
382 posts since Sat, 17 Oct 09
while you are at it, please also add Mork support ! should be easy !
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
Hehe... perhaps some more: Oracle, SQLite. No problemsmileGive me two... hmm.. three days! :)
Written by Allineer
4 months ago
47 posts since Thu, 01 Jul 10
PostgreSQL, SQLite & Redis.
and... seven days smile
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
Yesterday I downloaded PostgreSQL, copied libpq.dll and libintl-8 to my HeidiSQL directory and started to create a new descendent of my TDBConnection class for PostgreSQL. The original documentation helps a bit, while I have problems in finding more material, source examples. I can probably look into the ZEOS DBO code, as it also has PostgreSQL support.
Written by panofish
4 months ago
7 posts since Wed, 23 Feb 11
A good approach might be to just get a few basics working and take advantage of the community to help direct you on each next steps. That's another advantage of keeping heidisql(postgres) as a branch. You don't have to worry about affecting that awesome piece of code. THANKS ANSGAR... YOUR AWESOME!!!
Written by Allineer
4 months ago
47 posts since Thu, 01 Jul 10
THANKS ANSGAR... YOUR AWESOME!!!
Written by Barium
4 months ago
3 posts since Mon, 12 Aug 13
Sounds great Ansgarsmileif the bind variables will get in place as well, you will have made the worlds best DB IDE :)
Written by nahuelon
4 months ago
4 posts since Sat, 20 Jul 13
I am very excited to use the world best DB client (HeidiSQL) with the world best RDBMS (PostgreSQL). Thank you very much for your effort.
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
Please don't expect too much in a short time. I'm not certain at all I get it running with all required stuff as result handling, process lists and so on.

But look here:
Written by panofish
4 months ago
7 posts since Wed, 23 Feb 11
I think I can speak for many... we are super excited and super appreciative that you are working on this and we will take what we can get!
Written by Barium
4 months ago
3 posts since Mon, 12 Aug 13
This sounds (and looks) really great, please let us know if there is anything we can do to help?

:)
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
For those not starring issue #3190 - please let us continue discussion there. And I have gotten quite a step forward which I have just posted there.
Written by pizza111
4 months ago
19 posts since Fri, 01 Nov 13
ansgar has recovered from illness?
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
Not really. I just realized I can't keep my fingers from Heidi...
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
I have read the API documentation on http://www.postgresql.org/docs/9.2/interactive/libpq.html , and got several things to work in my local HeidiSQL build:
* connectivity
* error handling
* running queries
* database tree


Of course, this one week was not enough to cover all the things in PostgreSQL. For example:
* Alternative to SHOW CREATE [TABLE/VIEW/TRIGGER/...] ? Is information_schema.tables the only way here?
* Why does PQping always return a "3" on a good connection? The docs say it should return a PQPING_OK, which is a "0" in my understanding: http://www.postgresql.org/docs/9.2/interactive/libpq-connect.html
* Schemata: I'm unsure whether to show schematas or databases in the database tree. I tend to use schemata here, as these look most equivalent to what MySQL calls "databases". I can also display databases as folders and schemata as a table prefixes, as in MSSQL. But that would mean we would not see "pg_catalog" and "information_schema" in the tree.

Please help with (short) replies. Thanks!
Written by ansgar
4 months ago
4940 posts since Fri, 07 Apr 06
r4753 adds experimental PostgreSQL support now.

Libraries have to be downloaded to get it running:
64bit:
* http://heidisql.googlecode.com/svn-history/r4753/trunk/out/libintl-864.dll
=> rename to libintl-8.dll
* http://heidisql.googlecode.com/svn-history/r4753/trunk/out/libpq64.dll
=> rename to libpq.dll
32bit:
* http://heidisql.googlecode.com/svn-history/r4753/trunk/out/libintl32.dll
=> rename to libintl.dll
* http://heidisql.googlecode.com/svn-history/r4753/trunk/out/libpq32.dll
=> rename to libpq.dll

Or simply use the installer from http://www.heidisql.com/download.php .

32bit executable crashes in PQfinish() when closing a Postgres connection, I have no clue why yet.
Written by elcio.paludo
4 months ago
14 posts since Thu, 19 Sep 13
What about Oracle database suport?
would be nice.
Written by pizza111
4 months ago
19 posts since Fri, 01 Nov 13
ansgar is so tired....
Written by nahuelon
4 months ago
4 posts since Sat, 20 Jul 13
I have test the r4755 version and i could not open the database tree to reach the "public" schema.



I also get an bug crash (bugreport.txt) when I close the application.

Thank you for your effort!
Written by inzanity
4 months ago
10 posts since Wed, 16 Apr 14
Hi ansgar, I've just installed the 64 bit version of 4755 and tried to create a new connection but it throws an error:



I also tried the standalone 64 bit exe file using the libraries in your link to no avail. Thanks.
Written by egodoycMoney, Euro
4 months ago
5 posts since Mon, 07 Oct 13
This are GREAT NEWS !!!!! Thank You Ansgar.... I have made my first connection to a local Postgres Sever just like this !!!
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
Hi ansgar, the 64 bit version does not work on my end, the same error still appears per my post above but the 32 bit version works. Thanks.
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
I'm using win 7 64 bit by the way
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
How do we edit our post? :)

The error when closing the app by the way also includes the SSL_shutdown function
Written by ansgar
3 months ago
4940 posts since Fri, 07 Apr 06
You cannot edit your posts here.

Are you sure you have the 64 bit libpq.dll and 64 bit libintl-864.dll ?
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
Me again :)

This query:

SELECT *, pg_table_size(CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME)) AS data_length, pg_relation_size(CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_NAME)) AS index_length, c.reltuples, obj_description(c.oid) AS comment FROM "information_schema"."tables" AS t
LEFT JOIN "pg_class" c ON c.relname=t.table_name
LEFT JOIN "pg_namespace" n ON (n.oid = c.relnamespace) WHERE t."table_schema"= <NAME OF SCHEMA>;



I've just run and connected HeidiSQL (4764) to my postgresql database but it's not displaying any table, schema, etc. it just says 0B besides my database name and looking at the query generated above I noticed that you're setting the schema name to be the same as the database name which should not be as users tend to give the schema names that may differ from the database name.

You can get the schema list by using something like this:

select *
from information_schema.schemata


http://www.postgresql.org/docs/current/static/information-schema.html
and my suggestion is to list the output below the database name. Thanks smile
Written by ansgar
3 months ago
4940 posts since Fri, 07 Apr 06

inzanity wrote: Me again :)



Well, if I can't help it :)

inzanity wrote: I noticed that you're setting the schema name to be the same as the database name



No, I'm not using databases in PostgreSQL yet, only schemata. Where did you see a database name?
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14

ansgar wrote: You cannot edit your posts here.

Are you sure you have the 64 bit libpq.dll and 64 bit libintl-864.dll ?



Yes I've downloaded the installer and tried it but the error occurs then I tried using the standalone 64 bit exe file with the 64 bit libraries but it still throws the same error. 32 bit exe file with 32 bit libraries are working though. Thanks.
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
this part last part of your query:

WHERE t."table_schema"= 'database name is in here';



my database and schema names differ that's why am seeing empty (0B) database

Written by dor
3 months ago
1 posts since Wed, 23 Apr 14
my database and schema names differ that's why am seeing empty (0B) database

here too, multiple schema in a database
Written by forestwalker
3 months ago
11 posts since Wed, 10 Feb 10
I've found a strange issue - don't know if it's only applicable for wine:
I had the error message experenced by others too:

Cannot find a usable libpq.dll [...etc]



though I reinstalled the latest HeidiSQL and got every dll mentioned above.
After some trial-fail cycle i've found this in the console:


err:module:import_dll Library LIBEAY32.dll (which is needed by L"C:\\Program Files\\HeidiSQL\\SSLEAY32.dll") not found
err:module:import_dll Library SSLEAY32.dll (which is needed by L"C:\\Program Files\\HeidiSQL\\libpq.dll") not found
err:module:import_dll Library LIBEAY32.dll (which is needed by L"C:\\Program Files\\HeidiSQL\\libpq.dll") not found



So the error was caused by the missing LIBEAY32.dll. I've borrowed it from the Navicat's linux version install and copied into HeidiSQL's dir -> the error disappeared :)
Written by ansgar
3 months ago
4940 posts since Fri, 07 Apr 06
The HeidiSQL installer already has ssleay32.dll, but not yet libeay32.dll. I'll add that file from the PostgreSQL folder.
Written by ansgar
3 months ago
4940 posts since Fri, 07 Apr 06
Shit. libeay32.dll is over 1.5m in size. Including that file makes the installer quite larger. Download traffic will grow high with that file...
Written by forestwalker
3 months ago
11 posts since Wed, 10 Feb 10
Thanks :)

btw I've found another interesting thing, but i'ts possibly due to my lack of pgsql exp:
fe_sendauth: no password supplied
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14

ansgar wrote: Shit. libeay32.dll is over 1.5m in size. Including that file makes the installer quite larger. Download traffic will grow high with that file...


libeay32.dll is already included when installing postgresql, we could copy that file to where we installed HeidiSQL
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
Another way to go about this ansgar is to compress the dll and exe file by using UPX, I've been doing that for my applications for years and see no problem.

http://upx.sourceforge.net/
Written by inzanity
3 months ago
10 posts since Wed, 16 Apr 14
My bad :), HeidiSQL already uses UPX
Written by ftn
3 months ago
1 posts since Mon, 05 May 14
Heidi 4769
/* ERROR: function concat(information_schema.sql_identifier, unknown, information_schema.sql_identifier) does not exist
LINE 1: SELECT *, pg_table_size(CONCAT(t.TABLE_SCHEMA, '.', t.TABLE_...
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts. */
Written by ansgar
2 months ago
4940 posts since Fri, 07 Apr 06
All wrong/missing/32bit/64bit DLL problems are solved now in the recent built installer, r4784. To test that, use the newest installer from the download page once.
 

Please login to leave a reply, or register at first.