Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Unable to export Postgres database to another server (syntax error)

Mogzol's profile image Mogzol posted 6 months ago in General Permalink

I'm trying to export some tables to another Postgres server. When I used to use MySQL, I could do this by right clicking the desired table/database in the sidebar, choosing "Export Database as SQL", and choosing my target server as the output.

With two Postgres servers though, when I try to run the export, I get a syntax error:

ERROR:  syntax error at or near "USE"
LINE 1: USE "public"
        ^

Exporting to a SQL file first and then running that file in the new server works, but is less convenient, would be nice if this issue could be fixed.

1 attachment(s):
  • export-error
Mogzol's profile image Mogzol posted 5 months ago Permalink

Still getting this on the latest nightly (11.0.0.5995), any help would be greatly appreciated. My postgres version is 11 by the way, didn't mention that in my initial post.

ansgar's profile image ansgar posted 5 months ago Permalink

I suppose the CREATE statements generated in the exports are anyway incompatible to PG? But this particular USE ... is probably hardcoded, I should make that server specific, so PG gets a SET search_path TO ... instead.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
rpass's profile image rpass posted 3 weeks ago Permalink

I have the same problem, can't export table to another database directly : error syntax with USE ...

Icarules's profile image Icarules posted 2 weeks ago Permalink

Any update on this?

Code modification/commit f8e4b35 from Ansgar Becker <anse@heidisql.com>, 2 weeks ago, revision 6104
Make "USE xyz" query dependent on the target server system, when exporting data to another server. Fixes incompatible SQL for PostgreSQL, and most probably for SQLite. See https://www.heidisql.com/forum.php?t=36231
ansgar's profile image ansgar posted 2 weeks ago Permalink

I just made that hardcoded USE xyz query specific to the target server, so on PG HeidiSQL now fires SET search_path TO xyz instead. Next build will have that fix.

I suppose you will run right into the next issue, especially when having checked one of the "Create" or "Drop" options. But give it a try first.

Isn't there an issue report on Github for PG exports?

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.