PostgreSQL - Regular Expression - SQL state: 42601 syntax error

Question

I am trying to create tables which include the word "Messdaten" in it. Without ~ "Messdaten" part, code works well, but whenever I add ~ "Messdaten" it throws SQL State: 42601 sytax error. Do you have any idea what is wrong with the code? Its postgresql 9.2.

DECLARE
tbl_arr varchar[] := array['Messdaten2','dumptable'];
i varchar;
BEGIN
FOREACH i IN Array tbl_arr
LOOP
EXECUTE format('CREATE TABLE %I AS SELECT * FROM %I ~ "Messdaten"','backup_'||i,i);
END LOOP;
END;

Stephan · Accepted Answer · 2013-12-02 15:59:36Z

1

The code is trying to do this:

CREATE TABLE Messdaten2 AS SELECT * FROM Messdaten2 ~ "Messdaten"

which is incorrect since Messadaten2 is the table name not the member of an expression.

Instead, put the regular expression check outside of the first parameter string of the format function:

IF i ~ 'Messdaten' THEN -- Do the regular expresssion test here
   EXECUTE format('CREATE TABLE %I AS SELECT * FROM %I','backup_'||i,i);
END IF;

edited Dec 2, 2013 at 15:59

answered Dec 2, 2013 at 12:04

Stephan

43.2k69 gold badges245 silver badges342 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mctuna Over a year ago

It works but what do you mean by outside of the code? outside of the sql query? I have tried to put the reqular expression to the foreach part which is also outside of the query but didn't work, I am confused now = ) BEGIN FOREACH i IN Array tbl_arr ~ 'Messdaten' LOOP

Collectives™ on Stack Overflow

PostgreSQL - Regular Expression - SQL state: 42601 syntax error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related