PostgreSQL Support for "Interval" Data Type
Here is some info on the "interval" data type in PostgreSQL:
Perhaps a workaround would be to ensure any columns with an "interval" data type are treated as a TIMESTAMP rather than a VARCHAR. Another possibility would be to treat "interval" columns as unknown and not use the SUBSTR function on them. Thanks.
Here is an example CREATE TABLE statement that uses an INTERVAL column (uptime):
CREATE TABLE router ( router character varying NOT NULL, type character varying(16) NOT NULL, update_time timestamp with time zone DEFAULT now(), vendor character varying, software character varying, uptime interval, mgmt_ip host_ip, dob timestamp with time zone DEFAULT now(), owner character varying, can_ssh boolean NOT NULL DEFAULT false, must_ssh boolean NOT NULL DEFAULT false, CONSTRAINT router_pkey PRIMARY KEY (router), CONSTRAINT vendor FOREIGN KEY (vendor) REFERENCES vendor (vendor) MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT, CONSTRAINT mgmt_ip_unique UNIQUE (mgmt_ip) ) WITH ( OIDS=FALSE );
SELECT "router", "type", SUBSTR("pop", 0, 256), "update_time", SUBSTR("vendor", 0, 256), SUBSTR("software", 0, 256), SUBSTR("uptime", 0, 256), "mgmt_ip", "dob", SUBSTR("owner", 0, 256), "can_ssh", "must_ssh" FROM "routing"."router" LIMIT 1000;
"/* ERROR: function substr(interval, integer, integer) does not exist
LINE 1: ...("vendor", 0, 256), SUBSTR("software", 0, 256), SUBSTR("up...
HINT: No function matches the given name and argument types. You might need to add explicit type casts. */"
The problem appears to be that HeidiSQL is trying to use the SUBSTR function on a DATE/TIME (interval) column (SUBSTR("uptime", 0, 256)).
I see that the custom data type of host_ip is treated as UNKNOWN by HeidiSQL and does not have the SUBSTR function applied. Could this be done for the "interval" data type, as well (rather than assuming it to be VARCHAR - the current behavior)?
FYI - here is a quick reference on PostgreSQL data types that I found to be helpful:
There are two places which detect column types.
The first one is used for the Data tab and parses the CREATE TABLE coming from pg_catalog tables.
The second one just examines the result of a SELECT query in a "Query" tab, and types are detected by oid's send by libpq.dll, and mapped by HeidiSQL to their real names.
The second logic should work with that UNKNOWN type, but the first one does not work here for some reason.
Fix detection of PostgreSQL data type INTERVAL as VARCHAR. See http://www.heidisql.com/forum.php?t=16760
PostgreSQL has a myriad of data types. That makes the parser for query results quite error-prone. That was the reason why I introduced that UNKNOWN type as a fallback. But even this fallback does not work on data types I had already mapped to a wrong PostgreSQL "oid" before, like that INTERVAL type.
Please login to leave a reply, or register at first.