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

Postgres SQL (please)

Dartdog posted 4 years ago in Feature discussion
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.:-(
igorky posted 2 years ago
Heidi excellent tool, but I moved to the PostgresSQLunhappy
I'll wait for the PostgresSQL support.
ansgar posted 2 years ago
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?
igorky posted 2 years ago
Yes, but the pgadmin is unusable after years with the Heidiunhappy
ansgar posted 2 years ago
Hehe. So, why not stick to MySQL?
cferrero posted 2 years ago
Please, support PostgreSQL database with Heidisql
Por favor, soporten PostgreSQL desde Heidisql
Allineer posted 2 years ago
+1 vote for PostgreSQL support :)
gsegues posted 2 years ago
I'm starting in GIS, and need change from mysql to Postgres/Postgis. And after trying several GUI interfaces, I longs for HeidiSQL.


ajsouza posted 1 year ago
one vote more to HeidiSQL Postgre :D
ansgar posted 1 year ago
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.
jrgppp posted 1 year ago
We suggest connecting to PostgreSQL, it would be very interesting.
nahuelon posted 1 year ago
It will be an excellent parternship: the best RDBMS with the best SQL client.

+1 for heidisql + postgresql
fbachofner posted 1 year ago
I prefer HeidiSQL tremendously to alternatives which work with PostgreSQL such as pgadmin and SquirrelSQL.

Adding PostgreSQL support would be awesome!
kalvaro posted 1 year ago
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.
ansgar posted 1 year ago
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.
Barium posted 1 year ago
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. :)
fbachofner posted 1 year ago
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.
fbachofner posted 1 year ago
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.
ansgar posted 1 year ago

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 :)
fbachofner posted 1 year ago
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!
ansgar posted 1 year ago
Tada... see the new "Quote" link on the left hand of each post. :)

fbachofner posted 1 year ago

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



I DO!

Very cool.
fbachofner posted 1 year ago
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!
yahyasonmez posted 9 months ago
MySql together PostgreSql support required.
panofish posted 7 months ago
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. :)
pizza111 posted 7 months ago
Firebird please, because firebird comes from Delphi!
jfalch posted 7 months ago
while you are at it, please also add Mork support ! should be easy !
ansgar posted 7 months ago
Hehe... perhaps some more: Oracle, SQLite. No problemsmileGive me two... hmm.. three days! :)
Allineer posted 7 months ago
PostgreSQL, SQLite & Redis.
and... seven days smile
ansgar posted 7 months ago
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.
panofish posted 7 months ago
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!!!
Allineer posted 7 months ago
THANKS ANSGAR... YOUR AWESOME!!!
Barium posted 7 months ago
Sounds great Ansgarsmileif the bind variables will get in place as well, you will have made the worlds best DB IDE :)
nahuelon posted 7 months ago
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.
ansgar posted 7 months ago
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:
panofish posted 7 months ago
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!
Barium posted 7 months ago
This sounds (and looks) really great, please let us know if there is anything we can do to help?

:)
ansgar posted 7 months ago
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.
pizza111 posted 7 months ago
ansgar has recovered from illness?
ansgar posted 7 months ago
Not really. I just realized I can't keep my fingers from Heidi...
ansgar posted 7 months ago
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!
ansgar posted 7 months ago
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.

elcio.paludo posted 7 months ago
What about Oracle database suport?
would be nice.
pizza111 posted 7 months ago
ansgar is so tired....
nahuelon posted 7 months ago
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!
inzanity posted 7 months ago
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.
egodoyc posted 7 months ago
This are GREAT NEWS !!!!! Thank You Ansgar.... I have made my first connection to a local Postgres Sever just like this !!!
inzanity posted 6 months ago
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.
inzanity posted 6 months ago
I'm using win 7 64 bit by the way
inzanity posted 6 months ago
How do we edit our post? :)

The error when closing the app by the way also includes the SSL_shutdown function
ansgar posted 6 months ago
You cannot edit your posts here.

Are you sure you have the 64 bit libpq.dll and 64 bit libintl-864.dll ?
inzanity posted 6 months ago
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
ansgar posted 6 months ago

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?
inzanity posted 6 months ago

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.
inzanity posted 6 months ago
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

dor posted 6 months ago
my database and schema names differ that's why am seeing empty (0B) database

here too, multiple schema in a database
forestwalker posted 6 months ago
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 :)
ansgar posted 6 months ago
The HeidiSQL installer already has ssleay32.dll, but not yet libeay32.dll. I'll add that file from the PostgreSQL folder.
ansgar posted 6 months ago
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...
forestwalker posted 6 months ago
Thanks :)

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

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
inzanity posted 6 months ago
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/
inzanity posted 6 months ago
My bad :), HeidiSQL already uses UPX

ftn posted 6 months ago
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. */
ansgar posted 5 months ago
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.
inzanity posted 3 months ago
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/]
inzanity posted 3 months ago
Oops this is the second link:
http://aloksah.org/2011/04/23/extracting-information-schema-using-postgres-sql/
inzanity posted 3 months ago
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;
inzanity posted 3 months ago
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...
^ */
ansgar posted 2 months ago
r4801 restricts foreign key disablings to MySQL, so you can drop objects in PostgreSQL without errors now.
sford posted 2 months ago
Incorrect encoding in the fields of tables and queries
ansgar posted 2 months ago
Please post the CREATE TABLE code for that table with broken characters, and post a screenshot of what PGAdmin shows instead of these blocks.
sford posted 2 months ago
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;




ansgar posted 2 months ago
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?
sford posted 2 months ago
yes. i use russian characters.
tommorow i import few lines here.
but may be better use all russian chars?
sford posted 2 months ago
sql dump:
[/url]
or
[url=
http://rghost.ru/58042279]
ansgar posted 1 month ago
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."
sford posted 1 month ago
Maybe we should install a Russian keyboard layout in the operating system?
I used the latest version of pgadmin.
ansgar posted 1 month ago
I have just inserted chinese and russion characters into a varchar column using HeidiSQL's data tab:
sford posted 1 month ago
Look at picture:

it's original sql dump in editor.
sford posted 1 month ago
sford posted 1 month ago
http://rghost.ru/58084814 - original picture
sford posted 2 weeks ago
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
ansgar posted 6 days ago
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

ansgar posted 5 days ago
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';

sford posted 3 days ago
I updated to the latest version but it did not help.
Version 4851

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