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

Experimental PostgreSQL support

ansgar posted 8 months ago in News
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).
egodoyc posted 8 months ago
Excellent News !!!!happy
rarspace01 posted 7 months ago
Nice, thank you.
ansgar posted 6 months ago
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.
eliaszica posted 6 months ago
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.

ansgar posted 6 months ago
HeidiSQL displays schemata in PostgreSQL, not databases. That's a difference.
Jumanoid posted 6 months ago
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
ringerc_2ndquadrant posted 6 months ago
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.
ringerc_2ndquadrant posted 6 months ago
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
ansgar posted 3 months ago
How do Greenplum, ParAccel, EnterpriseDB AS, etc report themselves in SELECT VERSION() ?

jnp posted 2 months ago
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
ansgar posted 2 months 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
maptagsde posted 2 months ago
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
ansgar posted 2 months ago
Don't you need these PostGIS extensions for that to work?
Jumanoid posted 2 months ago
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;"

maptagsde posted 2 months ago
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
cduprez posted 1 month ago
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
andhika posted 2 weeks ago
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
ansgar posted 2 weeks ago
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.

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