Postgres SQL (please)

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.:-(

Heidi excellent tool, but I moved to the PostgresSQL
I'll wait for the PostgresSQL support.

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?

Yes, but the pgadmin is unusable after years with the Heidi

Hehe. So, why not stick to MySQL?

Please, support PostgreSQL database with Heidisql
Por favor, soporten PostgreSQL desde Heidisql

+1 vote for PostgreSQL support :)

I'm starting in GIS, and need change from mysql to Postgres/Postgis. And after trying several GUI interfaces, I longs for HeidiSQL.

one vote more to HeidiSQL Postgre :D

Likely I get flamed for that questionbut hasn't PostgreSQL become less popular in the recent years? Is there some usage statistics out there, couldn't find something.

We suggest connecting to PostgreSQL, it would be very interesting.

It will be an excellent parternship: the best RDBMS with the best SQL client.

+1 for heidisql + postgresql

I prefer HeidiSQL tremendously to alternatives which work with PostgreSQL such as pgadmin and SquirrelSQL.

Adding PostgreSQL support would be awesome!

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.

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.

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 ).

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).

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)And 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. :)

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.

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.

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 :)

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!

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

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

I DO!

Very cool.

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!

MySql together PostgreSql support required.

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. :)

Firebird please, because firebird comes from Delphi!

while you are at it, please also add Mork support ! should be easy !

Hehe... perhaps some more: Oracle, SQLite. No problemGive me two... hmm.. three days! :)

PostgreSQL, SQLite & Redis.
and... seven days

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.

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!!!

THANKS ANSGAR... YOUR AWESOME!!!

Sounds great Ansgarif the bind variables will get in place as well, you will have made the worlds best DB IDE :)

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.

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:

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!

This sounds (and looks) really great, please let us know if there is anything we can do to help?

:)

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.

ansgar has recovered from illness?

Not really. I just realized I can't keep my fingers from Heidi...

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!

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.

What about Oracle database suport?
would be nice.

ansgar is so tired....

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!

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.

This are GREAT NEWS !!!!! Thank You Ansgar.... I have made my first connection to a local Postgres Sever just like this !!!

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.

I'm using win 7 64 bit by the way

How do we edit our post? :)

The error when closing the app by the way also includes the SSL_shutdown function

You cannot edit your posts here.

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

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

Me again :)

Well, if I can't help it :)

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?

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.

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

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

here too, multiple schema in a database

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 :)

The HeidiSQL installer already has ssleay32.dll, but not yet libeay32.dll. I'll add that file from the PostgreSQL folder.

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...

Thanks :)

btw I've found another interesting thing, but i'ts possibly due to my lack of pgsql exp:
fe_sendauth: no password supplied

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

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/

My bad :), HeidiSQL already uses UPX

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. */

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.

Hi ansgar,

Could we have separate parent nodes for the tables, views, functions and trigger functions and under the parent View nodes are rules made for each view.

Query examples:
1. To list Views
SELECT *
from pg_rules

2. To list Functions:
SELECT routine_name FROM information_schema.routines
WHERE specific_schema = <schema_name>

3. To list Triggers:
SELECT DISTINCT trigger_name FROM information_schema.triggers
WHERE event_object_table = <table_name>
AND trigger_schema = <schema_name>

Links:

[/url]

[url=http://aloksah.org/2011/04/23/extracting-information-schema-using-postgres-sql/]

Oops this is the second link:
http://aloksah.org/2011/04/23/extracting-information-schema-using-postgres-sql/

There is also an error while trying to create a function.

[img][/img]

Below is a sample function creation:

CREATE OR REPLACE FUNCTION f_test(a_num numeric(15, 4))
RETURNS numeric AS
$BODY$

DECLARE num numeric(15, 4);

BEGIN
num := a_num;

RETURN num;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION f_test(a_num numeric(15, 4))
OWNER TO my_db;

I just found out about the tree style options so please disregard my post regarding the separate parent nodes. Also there's an error when dropping views and tables via Right-Click -> Drop menu:

/* ERROR: syntax error at or near "@"
LINE 1: SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KE...
^ */

r4801 restricts foreign key disablings to MySQL, so you can drop objects in PostgreSQL without errors now.

Incorrect encoding in the fields of tables and queries

Please post the CREATE TABLE code for that table with broken characters, and post a screenshot of what PGAdmin shows instead of these blocks.

in heidisql:

CREATE TABLE "city" (
"id" BIGINT NOT NULL,
"name" VARCHAR NOT NULL,
PRIMARY KEY ("id")
);

in pgadmin^

-- Table: net.city


-- DROP TABLE net.city;


CREATE TABLE net.city
(
id bigserial NOT NULL,
name character varying(50) NOT NULL,
CONSTRAINT city_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE net.city
OWNER TO billing;

Ok, so we have normal varchar columns with some international (russian?) characters in them. I guess the method to retrieve column values in HeidiSQL is wrong, does some superfluous conversion or something is missing. Or, your data is corrupt for some reason. At least I can display my own german umlauts and russion characters fine here.

I would like to test that here locally. Could you provide a small SQL dump so I can import a few lines here with critical characters?

yes. i use russian characters.
tommorow i import few lines here.
but may be better use all russian chars?

sql dump:
[/url]
or
[url=http://rghost.ru/58042279]

Thanks. But I cannot import that dump, neither in HeidiSQL nor in PGAdmin. HeidiSQL displays broken chars and exits with "Relation �city_id_seq� existiert nicht
LINE 5: SELECT pg_catalog.setval('city_id_seq', 20, true)
" after running that dump. PGADmin displays this error message:

"The file "C:\Users\Ansgar\Downloads\city.sql" could not be opened because it contains characters that could not be interpreted."

Maybe we should install a Russian keyboard layout in the operating system?
I used the latest version of pgadmin.

I have just inserted chinese and russion characters into a varchar column using HeidiSQL's data tab:

Look at picture:

it's original sql dump in editor.

http://rghost.ru/58084814 - original picture

may be trouble of codepage related with this:
not included in these variables encoding:
server_encoding WIN1251
lc_time ru_RU.CP1251
lc_numeric ru_RU.CP1251
lc_monetary ru_RU.CP1251
lc_messages ru_RU.CP1251
lc_ctype ru_RU.CP1251
lc_collate ru_RU.CP1251

r4846 introduces handling of PostgreSQL functions in HeidiSQL:
* Displays functions in database tree now
* Enables function editor
* Enables "Run routine" button, with function argument handling

r4849 now supports column comments in PostgreSQL:
* Shows comments in table editor
* Fires such a query for applying a comment:

COMMENT ON COLUMN "mytable"."mycolumn" IS 'This is a comment';

I updated to the latest version but it did not help.
Version 4851