Postgresql - Export Grid Rows - SQL Inserts - table schema and name quoted which results in an error

stompro2023's profile image stompro2023 posted 7 months ago in General Permalink

Hello, when I use the Export Grid Rows and choose SQL Inserts. The resulting SQL code has the table schema and name quoted. Postgresql doesn't allow quoted tables and schema names.

INSERT INTO "config.ui_staff_portal_page_entry" ("id", "page_col", "col_pos", "entry_type", "label", "image_url", "target_url", "entry_text", "owner") VALUES (1, 1, 0, 'header', 'Circulation and Patrons', NULL, NULL, NULL, 1);

Trying to execute this results in the error.

5: ERROR: relation "config.ui_staff_portal_page_entry" does not exist LINE 1: INSERT INTO "config.ui_staff_portal_page_entry" ("id", "page...

select * from "config.ui_staff_portal_page_entry" ;

ERROR: relation "config.ui_staff_portal_page_entry" does not exist LINE 1: select * from "config.ui_staff_portal_page_entry" ;

This works fine

select * from config.ui_staff_portal_page_entry limit 1;

stompro2023's profile image stompro2023 posted 7 months ago Permalink

Forgot the version: 12.5.0.6677 on windows 10 64bit.

ansgar's profile image ansgar posted 7 months ago Permalink

Problem is not the quotes themselves, but the schema seems to be included in the quoted string.

This should work as well:

SELECT * FROM config."ui_staff_portal_page_entry" LIMIT 1;

However, I am not able to reproduce that here with the latest build.

  • Does your connection have some special setting?
  • Did you export from the "Data" tab or from a "Query" tab?
stompro2023's profile image stompro2023 posted 7 months ago Permalink

I'm exporting from a query, so query tab.

I'm not seeing anything that I would consider unusual in the connection manager settings. Anything in particular I should check?

Thanks

ansgar's profile image ansgar posted 7 months ago Permalink

I admit apart from the used libpq* version there is no setting which may in any way affect that.

However, we should have a look at your SELECT-query to retrieve the grid rows. There could be something which makes HeidiSQL think the "config." is part of the table name. Can you post that query here?

stompro2023's profile image stompro2023 posted 7 months ago Permalink

I'm using libpq-10.dll, I can try a different version and see if that changes anything.... I changed to libpq-15.dll, but I'm seeing the same issue.

My query is super simple, SELECT * FROM config.ui_staff_portal_page_entry ORDER BY id;

Thanks Josh

ansgar's profile image ansgar posted 7 months ago Permalink

Thank you. I was just trying the same thing:

SELECT * FROM public.testserial;
-- creating a new row in the grid. Then, after posting I get this:
INSERT INTO "public"."testserial" ("id") VALUES (66);
-- success!

So here the quotes are around public, and the dot stands between schema and table name.

I have the gutt feeling your "config" is a database, not a schema?

Perhaps I can fix that once I understand what's going on here.

stompro2023's profile image stompro2023 posted 7 months ago Permalink

It is a schema, you can view the DDL here. https://git.evergreen-ils.org/?p=Evergreen.git;a=blob;f=Open-ILS/src/sql/Pg/002.schema.config.sql;h=41420d4f0c10ddf2b5a2f6dd3e885a6d74101386;hb=refs/heads/tags/rel_3_3_4

I'm testing on PG 13.11.

I can deal with this for now since it seems to just be a "me" issue. I'll look into it again in the future when I updated PG again.

Thank you for testing it out on your end. Josh

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