PostgreSQL Support for "Interval" Data Type

[expired user #7335]'s profile image [expired user #7335] posted 9 years ago in General Permalink
I recently came across an issue with the more recent HeidiSQL builds. It seems that the SUBSTR function is being used when getting PostgreSQL table data, which is generally working fine. But this brought to light another issue - HeidiSQL does not seem to fully support the "interval" data type in PostgreSQL. HeidiSQL treats the "interval" data type as a VARCHAR, when it actually a Date/Time data type. Because of this, when the table browser attempts to do a SUBSTR on an "interval" data type column, the database raises an error and the table data is not displayed.

Here is some info on the "interval" data type in PostgreSQL:

http://www.postgresql.org/docs/9.2/static/datatype-datetime.html

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.

-jnp
ansgar's profile image ansgar posted 9 years ago Permalink
Could you please provide a CREATE TABLE statement here with such an INTERVAL column? Thanks.
[expired user #7335]'s profile image [expired user #7335] posted 9 years ago Permalink
ansgar,
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
);


Thanks.

-jnp
[expired user #7335]'s profile image [expired user #7335] posted 9 years ago Permalink
This error is still present in the 9.0 release. I get the following error message when trying to browse the data grid on a table similar to the table shown in the CREATE code above:

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:
http://www.tutorialspoint.com/postgresql/postgresql_data_types.htm

-jnp
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, I'm busy with that bugfix currently.

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.
Code modification/commit from ansgar.becker, 9 years ago, revision 9.1.0.4869
Fix detection of PostgreSQL data type INTERVAL as VARCHAR. See http://www.heidisql.com/forum.php?t=16760
ansgar's profile image ansgar posted 9 years ago Permalink
Fixed in r4869. Should display INTERVAL now, not VARCHAR or UNKNOWN.

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.
ansgar's profile image ansgar posted 9 years ago Permalink
See also r4873 and r4870, which should also fix some other data type detections.
[expired user #7335]'s profile image [expired user #7335] posted 9 years ago Permalink
Thanks again. I really appreciate it. :-)

-jnp

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