[bug] PostgreSQL Copy Table error

[expired user #9074]'s profile image [expired user #9074] posted 9 years ago in General Permalink
Hello all. My company uses PostgreSQL and I was thrilled when I noticed recently that HeidiSQL added Postgres support!! It does appear to be quite buggy though at the moment. I imagine this is the appropriate place to report bugs? If not I'll gladly direct them elsewhere.

I was attempting to duplicate the structure of a Postgres table using [right-click] Create New -> Table copy. I selected just the Columns and Indexes, gave it a new name, and when I clicked OK received an error. The output is below.


date/time : 2015-07-07, 11:26:52, 631ms
computer name : XXXXXXXXXXX
user name : XXXXXXXX
registered owner : xp_usr / Windows User
operating system : Windows 7 x64 Service Pack 1 build 7601
system language : English
system up time : 1 day 2 hours
program up time : 1 minute 4 seconds
processors : 4x Intel(R) Core(TM) i5-3320M CPU @ 2.60GHz
physical memory : 458/3979 MB (free/total)
free disk space : (C:) 10.31 GB
display mode : 1920x1080, 32 bit
process id : $2528
allocated memory : 78.23 MB
largest free block : 8181.44 GB
executable : heidisql.exe
exec. date/time : 2015-05-02 13:40
version : 9.2.0.4947
compiled with : Delphi XE5
madExcept version : 4.0.8.1
callstack crc : $96fa3b98, $71857e3b, $71857e3b
exception number : 1
exception class : EDatabaseError
exception message : ERROR: relation "information_schema.TABLES" does not exist LINE 1: SELECT "TABLE_NAME" FROM

"information_schema"."TABLES" WHERE... ^.

main thread ($26ec):
00a7363b heidisql.exe dbconnection 2469 +61 TPgConnection.Query
00a8c3ac heidisql.exe dbconnection 5332 +2 TPGQuery.Execute
00a7b30a heidisql.exe dbconnection 3236 +5 TDBConnection.GetResults
00a7c8c4 heidisql.exe dbconnection 3507 +1 TDBConnection.GetVar
00b63789 heidisql.exe copytable 379 +6 TCopyTableForm.btnOKClick
00694034 heidisql.exe Vcl.Controls TControl.Click
006cb956 heidisql.exe Vcl.StdCtrls TCustomButton.Click
006cc972 heidisql.exe Vcl.StdCtrls TCustomButton.CNCommand
0040cb1e heidisql.exe System TObject.Dispatch
00693773 heidisql.exe Vcl.Controls TControl.WndProc
0069ae3e heidisql.exe Vcl.Controls TWinControl.WndProc
006cb3a0 heidisql.exe Vcl.StdCtrls TButtonControl.WndProc
00693250 heidisql.exe Vcl.Controls TControl.Perform
0069b039 heidisql.exe Vcl.Controls DoControlMsg
0069c3b3 heidisql.exe Vcl.Controls TWinControl.WMCommand
00809718 heidisql.exe Vcl.Forms TCustomForm.WMCommand
0040cb1e heidisql.exe System TObject.Dispatch
00693773 heidisql.exe Vcl.Controls TControl.WndProc
0069ae3e heidisql.exe Vcl.Controls TWinControl.WndProc
00804b0b heidisql.exe Vcl.Forms TCustomForm.WndProc
0069a07a heidisql.exe Vcl.Controls TWinControl.MainWndProc
005da9b3 heidisql.exe System.Classes StdWndProc
76e66ba8 USER32.dll SendMessageW
76e63b73 USER32.dll CallWindowProcW
0069afd9 heidisql.exe Vcl.Controls TWinControl.DefaultHandler
006946dc heidisql.exe Vcl.Controls TControl.WMLButtonUp
0040cb1e heidisql.exe System TObject.Dispatch
00693773 heidisql.exe Vcl.Controls TControl.WndProc
0069ae3e heidisql.exe Vcl.Controls TWinControl.WndProc
006cb3a0 heidisql.exe Vcl.StdCtrls TButtonControl.WndProc
0069a07a heidisql.exe Vcl.Controls TWinControl.MainWndProc
005da9b3 heidisql.exe System.Classes StdWndProc
76e698d5 USER32.dll DispatchMessageW
0081328f heidisql.exe Vcl.Forms TApplication.ProcessMessage
00813303 heidisql.exe Vcl.Forms TApplication.HandleMessage
0080bc48 heidisql.exe Vcl.Forms TCustomForm.ShowModal
00c0750e heidisql.exe Main 2271 +3 TMainForm.actCopyTableExecute
005d9400 heidisql.exe System.Classes TBasicAction.Execute
00664783 heidisql.exe Vcl.ActnList TCustomAction.Execute
005d9134 heidisql.exe System.Classes TBasicActionLink.Execute
007ea27c heidisql.exe Vcl.Menus TMenuItem.Click
007ecbaf heidisql.exe Vcl.Menus TMenu.DispatchCommand
007ef104 heidisql.exe Vcl.Menus TPopupList.WndProc
007ef005 heidisql.exe Vcl.Menus TPopupList.MainWndProc
005da9b3 heidisql.exe System.Classes StdWndProc
76e698d5 USER32.dll DispatchMessageW
0081328f heidisql.exe Vcl.Forms TApplication.ProcessMessage
00813303 heidisql.exe Vcl.Forms TApplication.HandleMessage
008137ef heidisql.exe Vcl.Forms TApplication.Run
00c5c3b7 heidisql.exe heidisql 77 +24 initialization
76f659cb kernel32.dll BaseThreadInitThunk
ansgar's profile image ansgar posted 9 years ago Permalink
Yes, PostgreSQL support is quite incomplete, and I am also not so a specialist in PG than I am in MySQL. But yes, this is the place to report bugs, and I can try to fix that somehow. Not sure how but I guess that IS.TABLES query mentioned above is meant for getting existing table names in the target database.
ansgar's profile image ansgar posted 9 years ago Permalink
The query in question is a check whether the target table exists, and if yes, Heidi asks whether it shall overwrite it with the new table copy. The query works here on my local PG v9.3.4 server, so I'm wondering which version of PG does not own the information_schema.tables view. Which is your PG version?
[expired user #9074]'s profile image [expired user #9074] posted 9 years ago Permalink
Hm, The db is on 9.2.12.

But I'm wondering if this is because of the double-quotes? When I run the query:
select * from information_schema.tables
I see the table details; if I run:
select * from "information_schema.tables"
I get the error message:

ERROR: relation "information_schema.tables" does not exist
SQL state: 42P01
Character: 15
[expired user #9074]'s profile image [expired user #9074] posted 9 years ago Permalink
Ah,
select "table_name" from "information_schema"."tables"
works, but
select "table_name" from "information_schema.tables"
doesn't
Code modification/commit from ansgarbecker, 9 years ago, revision 9.2.0.4975
Fix SQL error in "Copy table" dialog, in PostgreSQL mode. Use lowercase table and column names in IS.TABLES, so PG can find them. See http://www.heidisql.com/forum.php?t=18847
ansgar's profile image ansgar posted 9 years ago Permalink
Fixed in r4975 - please update to HeidiSQL's latest build.

The problem seems to be that PG wants the exact character case of table and column names when they're in quotes. Heidi selected from the table "TABLES", which is now "tables". Same goes for the column name "TABLE_NAME".

This just fixes one bug amongst others in that dialog. Creating indexes fails here in some cases. And foreign keys are never detected yet.
[expired user #9074]'s profile image [expired user #9074] posted 9 years ago Permalink
Thanks ansgar! This worked for me, my indexes were created successfully as well.

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