PostgreSQL

mcemberci's profile image mcemberci posted 9 years ago in HeidiSQL portable Permalink
Hi,
I am using Windows 8.1 Turkish locale,

When I add a table to postgres and than add a column type to integer, logs shows that Unknown datatype "İNTEGER" for "id". Fall back to UNKNOWN.
I wonder the cause of this issue is İNTEGER INTEGER difference?
ansgar's profile image ansgar posted 9 years ago Permalink
That should not happen. Probably a bug in a recent change. Which version of HeidiSQL is it?
mcemberci's profile image mcemberci posted 9 years ago Permalink
9.2.0.4953 64bit
mcemberci's profile image mcemberci posted 9 years ago Permalink
Sample Log output is as follows.

* Unknown datatype "İNTEGER" for "a_id". Fall back to UNKNOWN. */
/* Unknown datatype "NUMERİC(4,0)" for "model". Fall back to UNKNOWN. */
/* Unknown datatype "NUMERİC(1,0)" for "renk_id". Fall back to UNKNOWN. */
/* Unknown datatype "İNTEGER" for "k_id". Fall back to UNKNOWN. */
ansgar's profile image ansgar posted 9 years ago Permalink
HeidiSQL does not support an İNTEGER type - but INTEGER. How do you manage to get a special character into the column data type??
mcemberci's profile image mcemberci posted 9 years ago Permalink
I am sending a screenshot of an already created table's create code.
Please consider this issue.
1 attachment(s):
  • heidi_postgre
mcemberci's profile image mcemberci posted 9 years ago Permalink
the same table with phppgadmin
1 attachment(s):
  • pgadmin
ansgar's profile image ansgar posted 9 years ago Permalink
Please post the results of the following query here:
SELECT DISTINCT a.attname AS column_name, a.attnum, a.atttypid, FORMAT_TYPE(a.atttypid, a.atttypmod) AS data_type, CASE a.attnotnull WHEN FALSE THEN 'YES' ELSE 'NO' END AS IS_NULLABLE, com.description AS column_comment, def.adsrc AS column_default, NULL AS character_maximum_length
FROM pg_attribute AS a
JOIN pg_class AS pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_description AS com ON (pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef AS def ON (a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid AND pg_table_is_visible(pgc.oid) AND NOT a.attisdropped AND pgc.relname = 'YOUR_TABLE_NAME'
ORDER BY a.attnum;

Please replace "YOUR_TABLE_NAME" with the name of your table.
mcemberci's profile image mcemberci posted 9 years ago Permalink
Result is as follows
1 attachment(s):
  • 2015-05-26
ansgar's profile image ansgar posted 9 years ago Permalink
Please do the following:
* Activate Tools > Preferences > Logging > "Debug messages" (and click "OK")
* Click the table so you see the table editor
* Watch out for debug messages in the SQL logging area, and post these here.

Secondly:
* Again, execute the above query to get the column details
* This time, export the result as text, not as a screenshot: You can export the grid data by right click > "Export grid rows" > "Excel CSV" and attach that .csv file here.
mcemberci's profile image mcemberci posted 9 years ago Permalink
Step1
Unknown datatype "İNTEGER" for "a_id". Fall back to UNKNOWN. */
/* Unknown datatype "NUMERİC(4,0)" for "model". Fall back to UNKNOWN. */
/* Unknown datatype "NUMERİC(1,0)" for "renk_id". Fall back to UNKNOWN. */
/* Unknown datatype "İNTEGER" for "k_id". Fall back to UNKNOWN. */
1 attachment(s):
  • 2015-05-26-1205
mcemberci's profile image mcemberci posted 9 years ago Permalink
Secondly
1 attachment(s):
mcemberci's profile image mcemberci posted 9 years ago Permalink
Also, heidisql is grouping three characters of strings like numeric fields.
1 attachment(s):
  • heidi_threedigitsgrouping_nonnumeric
ansgar's profile image ansgar posted 9 years ago Permalink
This is so strange, I guess there must be something very special on your system. Noone else posted such an issue before.

Do you perhaps have some startup script running in HeidiSQL, which alters the character set of your connection, or something else?
mcemberci's profile image mcemberci posted 9 years ago Permalink
No there is no special startup scripts on my computer, I am also sending my regional settings in control panel.
1 attachment(s):
  • regionalsettings
ansgar's profile image ansgar posted 9 years ago Permalink
I have just changed my Windows locale settings to Turkish, and I got the issue reproduced now! That's at least one step forward. I will now try to watch out for the problem behind that.
Code modification/commit from ansgarbecker, 9 years ago, revision 4958
Fix column type converted to locale string format by String.ToUpper in TDBConnection.GetCreateCode - prefer String.ToUpperInvariant instead, to avoid funny characters in data types. See http://www.heidisql.com/forum.php?t=18466
ansgar's profile image ansgar posted 9 years ago Permalink
Got it! Fixed in r4958...
mcemberci's profile image mcemberci posted 9 years ago Permalink
Thank you.

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