Issue with INSERT INTO from table

Hi,

I am currently trying to insert a modified version of a table into another table. However, it is returning a “Exception: Syntax error at: SELECT” error.

However, the syntax is definitely okay and the error seems to have something to do with doing a linebreak in between the SELECT and the FROM statement.

Below you will find a reproducible example:

CREATE TABLE table_to_copy_to (a INT, b FLOAT, c TEXT, d TIMESTAMP);
CREATE TABLE table_to_copy_from (a INT, b FLOAT, c TEXT, d TIMESTAMP);
INSERT INTO table_to_copy_from VALUES (NULL, 3.1415, 'xyz', '2015-05-11 211720');

## Works 
INSERT INTO table_to_copy_to
SELECT * FROM table_to_copy_from

#does not work

INSERT INTO table_to_copy_to
SELECT *
FROM table_to_copy_from

As you are always so helpful @candido.dessanti , I would appreciate any suggestions with this one.

Thank you so much

Best
Frederic

Hi Frederik,
It looks really a tricky problem.

We used IAS without issues like that for a long time and the one you are facing looks caused by the parser than the IAS feature itself.

Anyway, I reproduced the issue, and as I said before, it’s really tricky because if I run the IAS command with omnisql within a single line or a multi-line, it’s not working, but running with another tool (I used dbeaver) in multiline mode I got the error.

To make the IAS works, as a workaround you should run the SELECT within a single line so

INSERT INTO table_to_copy_to 
SELECT * FROM table_to_copy_from;

INSERT INTO table_to_copy_to 
SELECT * FROM table_to_copy_from where c='xyz';

will work

INSERT INTO table_to_copy_to 
SELECT * 
FROM table_to_copy_from;

INSERT INTO table_to_copy_to SELECT * FROM table_to_copy_from
WHERE c='xyz'

won’t work (except you are running it on omnisql).

So, first of all, thanks for reporting back the problems and I suggest running the IAS commands within a single line until the fix.

Bests
Candido

Hi @ldnker,

I talked with the engineering team and this issue has been fixed in the upcoming 5.7 release.

Bests,
Candido

Hi,

okay, I will try out the workaround later today and thank you so much as always for the quick response and fixes!

Best
Frederic