How to connect to PostgreSQL

[expired user #8229]'s profile image [expired user #8229] posted 10 years ago in General Permalink
How to connect to PostgreSQL
[expired user #8232]'s profile image [expired user #8232] posted 10 years ago Permalink
I have managed to connect to PostgreSQL using
host: IP address
port: normally 5432
user: (normally 'postgres') and password
However --- I do not see a full list of databases and/or schemas (in my case, I have 3 databases, each with multiple schemas).
Any brilliant ideas?

Robert
ansgar's profile image ansgar posted 10 years ago Permalink
PostgreSQL does not allow to use and switch between multiple databases. But you should see schemata in the left tree.
[expired user #8232]'s profile image [expired user #8232] posted 10 years ago Permalink
Thankyou for your reply, ansgar.
I tried to select a database on the initial selection screen, but received an access violation (0366958F, LIBEAY32.dll, address 03010000) - win32.
On Win7, a dropdown list is shown, but does not include any of the databases defined (as, e.g., in pgAdmin).
PostgreSQL version is 9.3.4
I would love to drop pgAdmin in favour of Heidi! (I use both MariaDB and POstgreSQL).

Regards,
Robert
ansgar's profile image ansgar posted 10 years ago Permalink
You need to install HeidiSQL with dlls you don't have yet. Please use the latest installer from the nightly builds section.

Then, in the session screen, ensure you have one database you want to work with in the "Database(s)" box.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink

You need to install HeidiSQL with dlls you don't have yet. Please use the latest installer from the nightly builds section.

Then, in the session screen, ensure you have one database you want to work with in the "Database(s)" box.


Have done this ansgar (re-installed build 8.3.0.4799) and I get the connection requested but no objects.
Any idea whats wrong please?
ansgar's profile image ansgar posted 10 years ago Permalink
You probably have the wrong database in the "Databases" box?
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
There are two databases shown using pgadmin, postgres and postgis_21_sample. These are the only ones that will connect. Heide shows nothing under either?
[expired user #8255]'s profile image [expired user #8255] posted 10 years ago Permalink
Hi,

I can't get it work. My english is poor and i will try to explain my connection problem.

I have an PHP app that uses PostgreSQL. This is my PHP connection configuration:



This is the schema of the database with another database manager application:



I can connect correctly with HeidiSQL but the public "schema" is empty, if i no choose database name in heidi connection configuration:



And this is the result:



If i choose my database name in Heidi SQL i get the same result (empty):



Result:



What am I doing wrong?

More Information:
- HeidiSQL 8.3.0.4799.
- Wine 1.7.2
- SO openSUSE 13.1
- PostgreSQL 9.2.7


Greetings.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
I am thinking that it may be because Heidi cannot find the postgres driver on the classpath? The JDBC drivers are available here
http://jdbc.postgresql.org/download.html
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Have tried that and it is no different
ansgar's profile image ansgar posted 10 years ago Permalink
No, HeidiSQL does not need JDBC drivers, as HeidiSQL is not a Java application.

If you don't get error messages when connecting to a PostgreSQL server, you don't have a driver problem.

The fact that your table list is empty probably means that HeidiSQL fires a wrong query to get them. Please post the last 10 lines of your SQL log when you click your empty database.
[expired user #8255]'s profile image [expired user #8255] posted 10 years ago Permalink
If you need it:

Without writing database name on session manager and selecting it on left panel:
/* Delimitador cambiado a ; */
/* Conectando a localhost por PostgreSQL (experimental), usuario postgres, usando contraseña: Yes ... */
SELECT VERSION();
/* Conectado. ID de Hilo: 2728 */
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";
/* Entrando a la sesión "localhost PGSQL" */
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"='public';
SET SCHEMA 'public';


Writing database name on session manager and selecting it on left panel:
/* Delimitador cambiado a ; */
/* Conectando a localhost por PostgreSQL (experimental), usuario postgres, usando contraseña: Yes ... */
SELECT VERSION();
/* Conectado. ID de Hilo: 2794 */
SET statement_timeout TO 0;
SELECT EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - pg_postmaster_start_time())::INTEGER;
SET SCHEMA 'sapcine';
/* Entrando a la sesión "localhost PGSQL" */
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"='sapcine';
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Created a postres database containing a table using Heidi. It allowed me to connect to the new database but no objects just 0B.
Then connected using pgAdmin III. The database and table created using Heidi were there as expected.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
I am getting essentially the same logs as [quote=offenSuse][/quote]
Code modification/commit from ansgar.becker, 10 years ago, revision 4800
* Do not use the "Databases" setting for the db tree, just use it to set the single db name in PostgreSQL mode.
* Use "postgres" as default db (was "template1")
* Turn checkable dropdown menu items into radio items in PostgreSQL mode
See issue #3190 and http://www.heidisql.com/forum.php?t=16072
ansgar's profile image ansgar posted 10 years ago Permalink
Now, with r4800, in PostgreSQL, the "Databases" box in the session manager is now only used to set the single wanted database name. This was previously also used to set the database nodes in the left database tree. The tree now always displays all schemata in PostgreSQL.
[expired user #8255]'s profile image [expired user #8255] posted 10 years ago Permalink
Now it works!.





Thanks.
ftn's profile image ftn posted 10 years ago Permalink

WinXp x64 sp2
heidisql 8.3.0.4800
postgresql 9.0.8

why does not work?
ftn's profile image ftn posted 10 years ago Permalink
ftn's profile image ftn posted 10 years ago Permalink
This fulfills a request like:
SELECT *, pg_table_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS data_length
, pg_relation_size(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);
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Build 4800 now appears to be working as expected.
Main problem I was having was because some of the database object names were in uppercase. PGAdminIII does not seem to mind this but Heidi (correctly) throws errors.
Thanks to ansgar for a great piece of work much more friendly than PGAdmin. Will still have to use PGAdmin as I need to use the shapefile loader plugin. Unless its possible to use the plugin in Heidi of course?
ftn's profile image ftn posted 10 years ago Permalink
When you view the data base get error
Console log:
/* 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. */

This fix error:

This fulfills a request like:
SELECT *, pg_table_size(t.TABLE_SCHEMA || '.' || t.TABLE_NAME) AS data_length
, pg_relation_size(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);

Code modification/commit from ansgar.becker, 10 years ago, revision 4802
Support older PostgreSQL servers in TPGConnection.FetchDbObjects. See http://www.heidisql.com/forum.php?t=16072#p16138
ansgar's profile image ansgar posted 10 years ago Permalink
r4802 now uses the two pipes syntax instead of the CONCAT function call. Also works on PostgreSQL 9.3.4, and I guess CONCAT() does not work in most older versions.
ansgar's profile image ansgar posted 10 years ago Permalink
@roger_ackroyd: I have no clue what that shapeloader plugin does or how it works. HeidiSQL supports some MySQL plugins using the "plugins" folder, but this is of course something completely different than what you mean.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
@ansgar: I am working on Geo. Information System (GIS). Postgres has a PostGIS extension which I am using to store spatial mapping data. This is the reason why I needed postgres (not supported in MySQL) and a good SQL tool (i.e. Heidi) in order to work with associated non-spatial data. Thanks
jfalch's profile image jfalch posted 10 years ago Permalink
you could use the separate pgshapeloader program.
ftn's profile image ftn posted 10 years ago Permalink
postgresql92-server-9.2.9
See table data:
/* ERROR: function left(double precision, integer) does not exist
LINE 1: SELECT "uin", LEFT("pass", 256), "ulock", LEFT("llog", 2...
----
so use SUBSTR({COLUMN},0,LENGHT) ?
ansgar's profile image ansgar posted 10 years ago Permalink
Could you please provide me that table definition here, so I can test that? I cannot for some reason reproduce that LEFT() function call here on a table with a text column in it.
ftn's profile image ftn posted 10 years ago Permalink
structure of the table when viewing that there is an error:
CREATE TABLE "users_info_ext" (
"uin" TEXT NOT NULL,
"pass" TEXT NULL,
"ulock" SMALLINT NULL,
"llog" TEXT NULL,
"iadr" TEXT NULL,
"bcst" SMALLINT NULL,
"cdate" TEXT NULL,
"cpass" SMALLINT NULL,
"nick" TEXT NULL,
"frst" TEXT NULL,
"last" TEXT NULL,
"email1" TEXT NULL,
"email2" TEXT NULL,
"email3" TEXT NULL,
"e1publ" SMALLINT NULL,
"gmtoffs" SMALLINT NULL,
"auth" SMALLINT NULL,
"sex" SMALLINT NULL,
"age" SMALLINT NULL,
"bday" SMALLINT NULL,
"bmon" SMALLINT NULL,
"byear" SMALLINT NULL,
"waddr" TEXT NULL,
"wcity" TEXT NULL,
"wstate" TEXT NULL,
"wcountry" INTEGER NULL,
"wcompany" TEXT NULL,
"wtitle" TEXT NULL,
"wocup" SMALLINT NULL,
"wdepart" TEXT NULL,
"wphon" TEXT NULL,
"wfax" TEXT NULL,
"wpager" TEXT NULL,
"wzip" TEXT NULL,
"wweb" TEXT NULL,
"notes" TEXT NULL,
"haddr" TEXT NULL,
"hcity" TEXT NULL,
"hstate" TEXT NULL,
"hcountry" INTEGER NULL,
"hphon" TEXT NULL,
"hfax" TEXT NULL,
"hcell" TEXT NULL,
"hzip" TEXT NULL,
"hweb" TEXT NULL,
"nnotes" TEXT NULL,
"lang1" SMALLINT NULL,
"lang2" SMALLINT NULL,
"lang3" SMALLINT NULL,
"hpage_cf" SMALLINT NULL,
"hpage_cat" INTEGER NULL,
"hpage_txt" TEXT NULL,
"wdepart2" TEXT NULL,
"past_num" SMALLINT NULL,
"past_ind1" INTEGER NULL,
"past_key1" TEXT NULL,
"past_ind2" INTEGER NULL,
"past_key2" TEXT NULL,
"past_ind3" INTEGER NULL,
"past_key3" TEXT NULL,
"int_num" SMALLINT NULL,
"int_ind1" INTEGER NULL,
"int_key1" TEXT NULL,
"int_ind2" INTEGER NULL,
"int_key2" TEXT NULL,
"int_ind3" INTEGER NULL,
"int_key3" TEXT NULL,
"int_ind4" INTEGER NULL,
"int_key4" TEXT NULL,
"aff_num" SMALLINT NULL,
"aff_ind1" INTEGER NULL,
"aff_key1" TEXT NULL,
"aff_ind2" INTEGER NULL,
"aff_key2" TEXT NULL,
"aff_ind3" INTEGER NULL,
"aff_key3" TEXT NULL,
"iphide" SMALLINT NULL,
"webaware" SMALLINT NULL,
PRIMARY KEY ("uin")
);
ansgar's profile image ansgar posted 10 years ago Permalink
Ok thanks. And LEFT() does not work for you? On my 9.3.4 server it works. I guess they introduced this and some more functions like CONCAT recently. And I guess SUBSTR() always works.
Code modification/commit from ansgar.becker, 10 years ago, revision 4804
Use SUBSTR() alternative to LEFT() on PostgreSQL table data selection. See http://www.heidisql.com/forum.php?t=16072
ansgar's profile image ansgar posted 10 years ago Permalink
Done in r4804
ftn's profile image ftn posted 10 years ago Permalink
ok, I will update my postgres to 9.3.4 smile
ftn's profile image ftn posted 10 years ago Permalink
Done, upgraded to 9.3.4
SELECT "uin" , LEFT("pass",256) pass, "ulock"
-- , LEFT("llog", 256) yyy
FROM "public"."users_info_ext";

it worked
---
SELECT "uin" , LEFT("pass",256) pass, "ulock"
, LEFT("llog", 256) yyy
FROM "public"."users_info_ext";

not worked
---
/* ERROR:  function left(double precision, integer) does not exist
LINE 2:   , LEFT("llog", 256) yyy
^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts. */

---
hmm column "pass" and "llog" type text, what could be wrong?
P.S. and function substr same problem
ansgar's profile image ansgar posted 10 years ago Permalink
Did you update HeidiSQL to build 4804 to get the SUBSTR() solution?
ansgar's profile image ansgar posted 10 years ago Permalink
llog is of type TEXT, so that SUBSTR() works here. Apart from LEFT() on PostgreSQL 9.3.4.
ftn's profile image ftn posted 10 years ago Permalink
understanding, the structure of the table I took from heidisql and in fact it looks like this:
CREATE TABLE users_info_ext (
uin double precision NOT NULL,
pass text,
ulock smallint,
llog double precision,
iadr double precision,
bcst smallint,
cdate double precision,
cpass smallint,
nick text,
frst text,
last text,
email1 text,
email2 text,
email3 text,
e1publ smallint,
gmtoffs smallint,
auth smallint,
sex smallint,
age smallint,
bday smallint,
bmon smallint,
byear smallint,
waddr text,
wcity text,
wstate text,
wcountry integer,
wcompany text,
wtitle text,
wocup smallint,
wdepart text,
wphon text,
wfax text,
wpager text,
wzip text,
wweb text,
notes text,
haddr text,
hcity text,
hstate text,
hcountry integer,
hphon text,
hfax text,
hcell text,
hzip text,
hweb text,
nnotes double precision,
lang1 smallint DEFAULT 0,
lang2 smallint DEFAULT 0,
lang3 smallint DEFAULT 0,
hpage_cf smallint DEFAULT 0,
hpage_cat integer DEFAULT 0,
hpage_txt text DEFAULT ''::text,
wdepart2 text DEFAULT ''::text,
past_num smallint DEFAULT 0,
past_ind1 integer DEFAULT 0,
past_key1 text DEFAULT ''::text,
past_ind2 integer DEFAULT 0,
past_key2 text DEFAULT ''::text,
past_ind3 integer DEFAULT 0,
past_key3 text DEFAULT ''::text,
int_num smallint DEFAULT 0,
int_ind1 integer DEFAULT 0,
int_key1 text DEFAULT ''::text,
int_ind2 integer DEFAULT 0,
int_key2 text DEFAULT ''::text,
int_ind3 integer DEFAULT 0,
int_key3 text DEFAULT ''::text,
int_ind4 integer DEFAULT 0,
int_key4 text DEFAULT ''::text,
aff_num smallint DEFAULT 0,
aff_ind1 integer DEFAULT 0,
aff_key1 text DEFAULT ''::text,
aff_ind2 integer DEFAULT 0,
aff_key2 text DEFAULT ''::text,
aff_ind3 integer DEFAULT 0,
aff_key3 text DEFAULT ''::text,
iphide smallint DEFAULT 0,
webaware smallint DEFAULT 1
);
ansgar's profile image ansgar posted 10 years ago Permalink
Ah, understand... so LEFT() works in general for you, on TEXT columns?

I will add that "double precision" data type in HeidiSQL's column type detection, as that seems to be missing yet.
ftn's profile image ftn posted 10 years ago Permalink
yes LEFT() it works on TEXT columns.
Code modification/commit from ansgar.becker, 10 years ago, revision 4805
Support data types with spaces in them, for example "DOUBLE PRECISION" on PostgreSQL. See http://www.heidisql.com/forum.php?t=16072#p16169
ansgar's profile image ansgar posted 10 years ago Permalink
r4805 adds support for data types with spaces, like "double precision". So, your double precision columns should not get misdetected as text any longer, and the data grid should no longer fire some SUBSTR() on double precision columns.
ftn's profile image ftn posted 10 years ago Permalink
Thank you very much, great job.
[expired user #8285]'s profile image [expired user #8285] posted 10 years ago Permalink
Hi,

I have installed the last r4805 release, using PostgreSQL 9.3.5 on a Windows 7 64 bit computer.
I still have the same error when I go on the Data tab :
"ERROR: function substr(bit, integer, integer) does not exist..."
This is because a column that has the BIT data type is probably detected as a text column.
Could you add this data type to the list of non-text types ?

Thanks a lot in advance,

Cedric
ansgar's profile image ansgar posted 10 years ago Permalink
Yes, will look into that.
Code modification/commit from ansgar.becker, 10 years ago, revision 4807
Support BIT and BIT VARYING data types in PostgreSQL without SQL errors in Data tab. See http://www.heidisql.com/forum.php?t=16072#p16180
ansgar's profile image ansgar posted 10 years ago Permalink
BIT and BIT VARYING support added in r4807.
[expired user #8285]'s profile image [expired user #8285] posted 10 years ago Permalink
Again, thanks a lot.

Last problem : the same error message appears with GEOMETRY columns.
"ERROR: function substr(geometry, integer, integer) does not exist..."

Cedric
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Heidi appears to interpret geometry columns as arbitrary TEXT type when in fact these are complex spatial types line,point, polygon etc. substr() function fails because geometry fields are not strings. If you strip the substr() off it works and shows a meaningless string of integers of indeterminate length.
We are in danger of trying to run before we can walk. i.e. priority must be to get Postgres working correctly before we tackle the PostGIS extension issues.
Postgres support remains, after all 'experimental'.
@ansgar is, I know trying to deal with a number of issues regarding the subtle differences between MySQL and Postgres but given time and patience, I am sure the reward will be worth the effort. Then we can put pgadminIII away forever.
We must continue to support his laudable work and significant progress has been made so far.
There are also issues with creating indexes but that is another thread.
Thanks ansgar.

[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
With v4808 I now get the following when selecting any table containing a geometry column:-

SELECT "gid", SUBSTR("naer13cd", 0, 256), SUBSTR("naer13cdo", 0, 256), SUBSTR("naer13nm", 0, 256), SUBSTR("geom", 0, 256) FROM "public"."naer_dec_2013_wa_bfe" LIMIT 1000;
/* ERROR: function substr(geometry, integer, integer) is not unique
LINE 1: ...aer13cdo", 0, 256), SUBSTR("naer13nm", 0, 256), SUBSTR("ge...
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Also
"HINT: Could not choose a best candidate function. You might need to add explicit type casts."
Code modification/commit from ansgar.becker, 10 years ago, revision 4809
Add support for geometry data types in PostgreSQL. See http://www.heidisql.com/forum.php?t=16072#p16198
ansgar's profile image ansgar posted 10 years ago Permalink
r4809 adds support for geometry data types listed in the PostgreSQL documentation.
[expired user #8295]'s profile image [expired user #8295] posted 10 years ago Permalink
If field type boolean in Heidi it called 'char' and SUBSTR("is_active", 0, 256) not worked

/* ERROR:  function substr(boolean, integer, integer) does not exist LINE 1: SELECT "id", SUBSTR("is_active", 0, 256)
ansgar's profile image ansgar posted 10 years ago Permalink
Yes, can confirm that. I would like to add support for that boolean type, and provide a dropdown menu in the grid so the user can select a "true"/"false" value by click.
[expired user #8285]'s profile image [expired user #8285] posted 10 years ago Permalink
I upgraded to r4809, but still get the error message mentionned by roger_ackroyd :
"function substr(public.geometry, integer, integer) is not unique.
HINT : Could not choose a best candidate function. You might need to add explicit type casts."

Thanks for all you efforts.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
r4811 still giving same error on geometry columns when clicking the Data tab
ansgar's profile image ansgar posted 10 years ago Permalink
Then, please post your data types here:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='YOUR_TABLE' AND TABLE_NAME='YOUR_DATABASE';
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
That query returns 44 columnsI so I have restricted the columns to the ones I think you may need?

column_name;column_default;is_nullable;data_type;character_maximum_length;udt_name
gid;nextval('oa_2011_ew_pwc_gid_seq'::regclass);NO;integer;\N;int4
oa11cd;\N;YES;character varying;9;varchar
geom;\N;YES;USER-DEFINED;\N;geometry

ansgar's profile image ansgar posted 10 years ago Permalink
Ok, the first is integer, second one is character varying. So far no problem in HeidiSQL I think. But the third column has data type "USER-DEFINED"... How should HeidiSQL treat such data types, if not as text?
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
geometry columns are created as
geom geometry(Point)
geom geometry(Line)
geom geometry(Polygon)
geom geometry(MultiPolygon)

As follows
Does this help?

CREATE TABLE public.oa_2011_ew_pwc
(
gid integer NOT NULL DEFAULT nextval('oa_2011_ew_pwc_gid_seq'::regclass),
oa11cd character varying(9),
geom geometry(Point),
CONSTRAINT oa_2011_ew_pwc_pkey PRIMARY KEY (gid)
Code modification/commit from ansgar.becker, 10 years ago, revision 4812
Add support for BOOLEAN column data type in PostgreSQL. See http://www.heidisql.com/forum.php?t=16072#p16204
ansgar's profile image ansgar posted 10 years ago Permalink
Support for PostgreSQL's "boolean" data type added in r4812.
ansgar's profile image ansgar posted 10 years ago Permalink
For the geometry thingy: I cannot add/test geometry columns here, as I'm getting an error when adding such a column:
alter table geom add column geotest geometry(Point);
/* FEHLER:  Typ „geometry“ existiert nicht
LINE 1: alter table geom add column geotest geometry(Point) */

Which means as much as "Type "geometry" does not exist".
ansgar's profile image ansgar posted 10 years ago Permalink
r4813 now supports adding columns in the table editor, where I fired the MySQL-specific AFTER/FIRST clause all the time.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
Geometry issues are because you perhaps need to execute something like this to install the PostGis extension
-- Enable PostGIS (includes raster)

CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;

I created a simple table and then ran a query similar to yours and it works?
http://postgis.net/
ansgar's profile image ansgar posted 10 years ago Permalink
r4814 introduces a UNKNOWN data type for such purposes, so we don't run into that SUBSTR problem on non-text columns any more.
[expired user #8249]'s profile image [expired user #8249] posted 10 years ago Permalink
That now works ansgar, thanks.
[expired user #7792]'s profile image [expired user #7792] posted 9 years ago Permalink
r4826 SUBSTR problem with INET fields.
[expired user #11932]'s profile image [expired user #11932] posted 5 years ago Permalink

Selam

HeidiSql V : 9.5.0.5196

PostgreSQL V : 11

Database List empty.

I would like your help

1 attachment(s):
  • pg
[expired user #11932]'s profile image [expired user #11932] posted 5 years ago Permalink

When you installed PostgreSQL database server, the PostgreSQL installer also installed some useful tools for working with the database server. You can connect to the PostgreSQL database server by using the psql or pgAdmin tool.

Connect to PostgreSQL database using psql psql is an interactive terminal program provided by PostgreSQL. You can do a lot with psql tool e.g., execute SQL statements, manage database objects, etc.

The following steps show you how to connect to the PostgreSQL database server by using the psql program:

First launch psql program.

Second, enter all the required information such as the server, database, port, username, and password. If you press enter, psql will use default values inside the square brackets.

Third, you can interact with PostgreSQL database server by using various SQL statements. You can try the following statement to test it out:

1 SELECT version(); psql tool

Please do not forget to put the semicolon (;) at the end of the statement. After pressing enter, psql will give you the current PostgreSQL version that you have in the system.

Connect to PostgreSQL database using pgAdmin GUI application The second way to connect to a database is using pgAdmin GUI application. By using pgAdmin GUI application, you can interact with PostgreSQL database server via an intuitive user interface.

The following illustrates how to connect to a database using pgAdmin GUI application:

First, launch the pgAdmin application.

Launch pgAdmin

Second, double-click the PostgreSQL 9.2 under the Servers item. pgAdmin will ask you for the password. You have to provide the password for the postgres user. Once finish, click OK button to log in to the PostgreSQL server.

pgAdmin Enter Password

Third, choose the postgres database and click Execute Arbitrary SQL queries tool from the pgAdmin’s toolbar.

Launch SQL editor Fourth, enter the following statement:

Execute SQL query

Fifth, click the execute query button in the toolbar, pgAdmin will display the result in the output panel.

pgadmin output

Connect to PostgreSQL database from other applications Any application that supports ODBC or JDBC can connect to PostgreSQL database server. In addition, if you develop an application that uses an appropriate driver, the application can connect to the PostgreSQL database server as well.

In this tutorial, you’ve learned how to connect to PostgreSQL database server by using different client tools including psql and pgAdmin GUI application. Let’s explore the PostgreSQL database objects and find out how can we use them in our applications.

Thank you very much for your response.

I am actively using "SQL Manager" for PostgreSQL.

In my previous projects I was using MySQL - HeidiSQL ...

HeidiSQL has a lot of success and Easy to Use interface ...

When I see the posts for older versions of PostgreSQL, I have written in the hope that a study can be done for the current version ...

I thank HeidiSQL developer friends and wish them continued success.

Respects

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