Experimental PostgreSQL support

I'm very proud to announce PostgreSQL support as a big new feature!

Read and take part in the discussions:
* http://www.heidisql.com/forum.php?t=7025
* issue #3190

Or download the installer (which includes required DLL's).

Excellent News !!!!

Nice, thank you.

Missing/wrong/32/64bit DLLs all fixed now in the latest installer from the download page. What's yet missing for a reasonable beta stage is the ability to pass a database name to the PostgreSQL server. Will do that asap.

Can't see my pg databases in HeidiSQL after a sucessful connection.
Using Win 8.1 and the last HeidiSQL 64 bit version.

Thanks in advance.

HeidiSQL displays schemata in PostgreSQL, not databases. That's a difference.

Error at the very beginning with the PostreSQL 9.1.0

I'm just starting to learn the PostgreSQL
(In the HeidiSQL with a MySQL to work much easier than in a phpMyAdmin).

Сhoose my training to work with the PostreSQL to make your program HeidiSQL 8.3.0.4792 (64 bit), but this did not happen :(.


Describe my actions:
1)received from his hosting provider access to a customized PostgreSQL 9.1.0
2)using the SSH /usr/local/pgsql/9.1/bin/psql I'm created "mydemo" DATABASE
3)I'm connected through the HeidiSQL. In the list on the left there is no "mydemo" DATABASE. On the left were only system tables.
---LOG--------
SELECT VERSION();
/* Connected. Thread-ID: xxx */
SET statement_timeout TO 0;
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER;
SELECT "schema_name" FROM "information_schema"."schemata" ORDER BY "schema_name";
/* Entering session "PostgreSQL 9.1.0" */
-----------

4)when indicated in the HeidiSQL in the connection to the DATABASE mydemo - received on the screen and in the log error:

---LOG-----
...
SELECT VERSION();
/* Connected. Thread-ID: xxx */
SET statement_timeout TO 0;
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER;
/* Entering session "PostgreSQL 9.1.0" */
SET SCHEMA 'mydemo';
/* ERROR: invalid value for parameter "search_path": "mydemo"
DETAIL: schema "mydemo" does not exist
*/

--------
5)further installed the phpPgAdmin 5.1 on shared hosting and its connection to the PostgreSQL 9.1.0
6)phpPgAdmin showed me my "mydemo" DATABASE. Later in the phpPgAdmin, I can add and modify tables.

Tried to fully describe their actions. Good luck
I'm sorry that I do bad speak English

A few thoughts regarding different versions:

- pg_stat_activity.procpid was renamed to pg_stat_activity.pid in 9.2 . If you're using this view, you'll want to take account of that version-specific difference.

- Beware of the different default for 'standard_conforming_strings' on very old PostgreSQL releases.

- Consider detecting PostgreSQL 8.1 and older and refusing to connect to these versions. There's no good reason to be running anything that old, and you're likely to have all sorts of problems. The only reason to accept 8.2 is that's what Greenplum and ParAccel ("Amazon Redshift") report their versions as.

- If you're going to show the version, make sure to detect forked versions like Greenplum, ParAccel, EnterpriseDB AS, etc, and show them as different products. Please be careful not to show "PostgreSQL version: 8.2" when they're running Greenplum, it confuses everybody. Better: "Version: Greenplum 4.1 (like PostgreSQL 8.2)".


When it comes to DLLs, remember to bundle the MSVC runtimes required for libpq, ssleay32, etc if they're different to that for HeidiSQL. Alternately, it's pretty easy to just recompile libpq against the same runtime you use for HeidiSQL - feel free to drop me a note to ask about that if you want to do it.

A few ideas on PostgreSQL-specific things that it'd be useful to expose in HeidiSQL at some point, once the basics are sorted out:

- Managing postgresql.conf and pg_hba.conf

- ALTER USER ... SET, ALTER DATABASE ... SET

- Object ownership

- User defined operators, operator classes, etc

- Partial indexes

- Expression indexes

- Viewing active connections (pg_stat_activity), cancelling queries / terminating connections

- Viewing current locks, viewing queries blocked on locks (pg_locks)

- Reporting table bloat statistics

- Reporting and changing per-table autovacuum settings

- Full-text search dictionaries and parsers

- Foreign tables and foreign servers

- Invoking psql to run sql scripts that use \commands

How do Greenplum, ParAccel, EnterpriseDB AS, etc report themselves in SELECT VERSION() ?

Hi - I have used HeidiSQL to connect to MySQL databases for years and have become addicted to the ease and speed of using this great tool :-)

Now I am trying to use it to connect to a PostgreSQL database (which is successful), but I am not able to get a listing of objects (tables, views, functions, etc.) in the PostgreSQL database. The tree view on the left is blank, and I assume that this is the user I am logging in as is read-only and owns no objects. I see that the query you are using to display PostgreSQL objects is:

SELECT "schema_name" FROM "information_schema"."schemata" ORDER BY "schema_name";

However, because I am not logged in as an admin, the query returns no records. Would it be possible to implement an alternative method for returning objects when the initial query returns no results?

I found a somewhat-comprehensive tutorial on how to accomplish this both by using the information_schema and (alternatively) using PostgreSQL-specific (pg_) tables:

http://www.alberton.info/postgresql_meta_info.html

Hopefully this will give you a running start if you decide to pursue this.

Thanks again for your hard work on HeidiSQL. Your efforts are greatly appreciated!

-JP

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

Hi,

first of all, big thanks for adding PG-Support to heidisql, you've done a great job so far and i really appreciate that.

Second, is there any way to get spatial functions to work, when im using a different schema then public? Because if i'm trying to execute something like this: select st_astext(a.geom) from test_schema.test_db a; It doesn't work.

regards
Simon

Don't you need these PostGIS extensions for that to work?

To use the example
SELECT ST_AsEWKT('0101000020E61000002EA7E8482EB74240F9156BB8C8E94B40')

me in the new database has to make the team

$ psql -d mydatabase -c "CREATE EXTENSION postgis;"

Hi ansgar,

thanks for your reply. Yes, the PostGIS extension is already installed on my server.
The spatial functions are stored in the public schema by default and i can execute them inside the database from any custom schemata (with other DB-Clients).

What i found out that, when i modify my previous statement to: Select public.st_astext(a.geom) from test_schema.test_db a;

It works in HeidiSQL, but i've to edit all my SQL-Syntaxes, if i finally decide to switch to HeidiSQL. Is there any way to avoid this?

best regards
Simon

Hi,

The problem comes from the fact that heidisql only includes the schema on which you are connected in its object browser. If you are not pointing to the public schema, tables, functions, views, etc. from the public schema are not visible inside the query editor.
One solution consists of adding the command "SET search_path TO public, my_schema;" at the beginning of every script (my_schema is the PostgreSQL schema where your spatial tables are located and that you are pointing in your object browser).

A Heidisql solution is to add systematically the "public" schema in the "search_path" when selecting a new schema in the objects browser.

Regards,
Cedric

Hi Team,

May I know, is there a SSH Tunnel feature in this Experimental PostgreSQL? Or SSH Tunnel just for MySQL? Need a guidance, since I could not find it.

Warm Regards,
andhika

SSH tunnel is built into HeidiSQL for MySQL only. For using a tunnel to a PostgreSQL server, you will need to start a plink.exe command line seperately, and then let HeidiSQL connect to 127.0.0.1 on the port you specified in the command line.

Hello,

in postgresql when datatype of field is TEXT and if i want to edit this field with embedded HeidiSQL editor - i can't - because editor shows only 255 characters

br
Andris

I second the request for Foreign table support.

Thanks for a great product.
John

It is great to find a more sophisticated postgres client that pgadmin. Thanks for putting this out there.

When I connect to my database I see my schemas listed and the public schema which I would expect but also there is pg_temp_1, pg_temp_6 and pg_toast_1 among others. These are not schemas so was wondering why they are listed ?

Thanks
Steve

[T]here is pg_temp_1, pg_temp_6 and pg_toast_1 among others. These are not schemas so was wondering why they are listed ?

Actually, they are schemas, it's just that clients like psql and PgAdmin know to hide them by default. You'll see them if you SELECT * FROM pg_namespace; .

The client should probably hide any schema beginning with pg_ and also hide the information_schema .