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

How to connect to PostgreSQL

UlisesPedro posted 4 months ago in General
How to connect to PostgreSQL
rpe posted 4 months ago
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 posted 4 months ago
PostgreSQL does not allow to use and switch between multiple databases. But you should see schemata in the left tree.
rpe posted 4 months ago
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 posted 4 months ago
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.
roger_ackroyd posted 4 months ago

ansgar wrote: 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 posted 4 months ago
You probably have the wrong database in the "Databases" box?
roger_ackroyd posted 4 months ago
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?
offenSuse posted 4 months ago
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.
roger_ackroyd posted 4 months ago
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
roger_ackroyd posted 4 months ago
Have tried that and it is no different
ansgar posted 4 months ago
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.
offenSuse posted 4 months ago
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';

roger_ackroyd posted 4 months ago
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.
roger_ackroyd posted 4 months ago
I am getting essentially the same logs as [quote=offenSuse][/quote]

ansgar posted 4 months ago
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.
offenSuse posted 4 months ago
Now it works!.





Thanks.
ftn posted 4 months ago

WinXp x64 sp2
heidisql 8.3.0.4800
postgresql 9.0.8

why does not work?
ftn posted 4 months ago
ftn posted 4 months ago
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);
roger_ackroyd posted 4 months ago
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 posted 3 months ago
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:

ftn wrote: 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);

ansgar posted 3 months ago
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 posted 3 months ago
@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.
roger_ackroyd posted 3 months ago
@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 posted 3 months ago
you could use the separate pgshapeloader program.
ftn posted 3 months ago
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 posted 3 months ago
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 posted 3 months ago
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 posted 3 months ago
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.

ansgar posted 3 months ago
Done in r4804
ftn posted 3 months ago
ok, I will update my postgres to 9.3.4 smile
ftn posted 3 months ago
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 posted 3 months ago
Did you update HeidiSQL to build 4804 to get the SUBSTR() solution?
ansgar posted 3 months ago
llog is of type TEXT, so that SUBSTR() works here. Apart from LEFT() on PostgreSQL 9.3.4.
ftn posted 3 months ago
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 posted 3 months ago
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 posted 3 months ago
yes LEFT() it works on TEXT columns.
ansgar posted 3 months ago
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 posted 3 months ago
Thank you very much, great job.
cduprez posted 3 months ago
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 posted 3 months ago
Yes, will look into that.
ansgar posted 3 months ago
BIT and BIT VARYING support added in r4807.
cduprez posted 3 months ago
Again, thanks a lot.

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

Cedric
roger_ackroyd posted 3 months ago
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.

roger_ackroyd posted 3 months ago
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...
roger_ackroyd posted 3 months ago
Also
"HINT: Could not choose a best candidate function. You might need to add explicit type casts."
ansgar posted 3 months ago
r4809 adds support for geometry data types listed in the PostgreSQL documentation.
ret_ok posted 3 months ago
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 posted 3 months ago
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.
cduprez posted 3 months ago
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.
roger_ackroyd posted 3 months ago
r4811 still giving same error on geometry columns when clicking the Data tab
ansgar posted 3 months ago
Then, please post your data types here:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='YOUR_TABLE' AND TABLE_NAME='YOUR_DATABASE';

roger_ackroyd posted 3 months ago
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 posted 3 months ago
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?
roger_ackroyd posted 3 months ago
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)
ansgar posted 3 months ago
Support for PostgreSQL's "boolean" data type added in r4812.
ansgar posted 3 months ago
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 posted 3 months ago
r4813 now supports adding columns in the table editor, where I fired the MySQL-specific AFTER/FIRST clause all the time.
roger_ackroyd posted 3 months ago
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 posted 3 months ago
r4814 introduces a UNKNOWN data type for such purposes, so we don't run into that SUBSTR problem on non-text columns any more.
roger_ackroyd posted 3 months ago
That now works ansgar, thanks.
Mayar posted 2 months ago
r4826 SUBSTR problem with INET fields.

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