Postgres SQL (please)
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)
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. :)
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.
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!):
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 :)
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!
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. :)
* 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!
Libraries have to be downloaded to get it running:
=> rename to libintl-8.dll
=> rename to libpq.dll
=> rename to libintl.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.
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
and my suggestion is to list the output below the database name. Thanks
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.
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 :)
/* 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. */
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.
1. To list Views
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>
Below is a sample function creation:
CREATE OR REPLACE FUNCTION f_test(a_num numeric(15, 4))
RETURNS numeric AS
DECLARE num numeric(15, 4);
num := a_num;
LANGUAGE plpgsql VOLATILE
ALTER FUNCTION f_test(a_num numeric(15, 4))
OWNER TO my_db;
/* ERROR: syntax error at or near "@"
LINE 1: SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KE...
Restrict disabling foreign keys to MySQL when dropping objects. See http://www.heidisql.com/forum.php?t=7025#p16142
CREATE TABLE "city" ( "id" BIGINT NOT NULL, "name" VARCHAR NOT NULL, PRIMARY KEY ("id") );
-- 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;
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?
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."
Please login to leave a reply, or register at first.