Redshift INSERT INTO TABLE from CTE

Question

According to the Redshift WITH Clause documentation, you can use a WITH clause with a INSERT INTO...SELECT statement. However when testing this, I am getting the below error. Is this not possible, or do I have the syntax wrong?

CREATE TABLE TestCTEInsert (SomeTimestamp TIMESTAMP);
WITH CTE AS
(SELECT GETDATE() as SomeTimestamp)
INSERT INTO TestCTEInsert
    (SomeTimestamp) SELECT SomeTimestamp from CTE;

ERROR: 42601: syntax error at or near "insert"

Interestingly, it does support inserting into a new table i.e.

WITH CTE AS
(SELECT GETDATE() as SomeTimestamp)
INSERT SomeTimestamp INTO NewTable 
SELECT SomeTimestamp from CTE;

The command completed successfully (1 rows affected)

EDIT: Just to confirm, I get the same error when using an INTEGER column rather than TIMESTAMP:

CREATE TABLE TestCTE (SomeInt INTEGER);
WITH CTE AS
(SELECT 1 as SomeInt)
INSERT INTO TestCTEInsert 
SELECT SomeInt from CTE;

ERROR: 42601: syntax error at or near "insert"

Thanks for your suggestion. This returns the same error: ERROR: 42601: syntax error at or near "insert" — fez
– fez, Commented Jan 18, 2016 at 11:04
As far as i know, you cannot insert an explicit value into a timestamp column. — Akshey Bhat
– Akshey Bhat, Commented Jan 18, 2016 at 11:09
Thanks @AksheyBhat but I don't think this is the problem here. I recreated this with an integer column rather than timestamp and got the same error message. — fez
– fez, Commented Jan 18, 2016 at 11:12
The ; goes at the end of a statement not somewhere in the middle. — user330315
– user330315, Commented Jan 18, 2016 at 11:44

Adeeb Armalite · Accepted Answer · 2016-01-18 11:19:39Z

47

Try putting the CTE in the insert (not sure if that beats the point)

INSERT INTO TestCTEInsert
WITH CTE AS
(SELECT CURRENT_TIMESTAMP as SomeTimestamp)
SELECT SomeTimestamp from CTE;

edited Jan 18, 2016 at 11:19

answered Jan 18, 2016 at 11:07

Adeeb Armalite

7046 silver badges7 bronze badges

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

Comments

user330315 · Accepted Answer · 2016-01-18 11:49:22Z

The ; terminates a statement, so it needs to go at the end of the statement, not somewhere in the middle:

You can do this in two ways, either use a create table as select

create table TestCTEInsert 
as
WITH CTE AS
(  
   SELECT current_timestamp as SomeTimestamp
)
SELECT SomeTimestamp 
from CTE; -- ; only at the end

Or in two steps:

CREATE TABLE TestCTEInsert (SomeTimestamp TIMESTAMP); -- end this with a ;

insert into TestCTEInsert
WITH CTE AS
(  
   SELECT current_timestamp as SomeTimestamp
)
SELECT SomeTimestamp 
from CTE; -- ; only at the end

The above runs on a vanilla Postgres installation, I don't have access to RDS

Thanks for your help @a_horse_with_no_name. The semicolon in my script was just to separate the CREATE TABLE and the CTE insert so I could do this in two steps (like your second query above). I have accepted Adeeb's answer as he was a little quicker with his answer.

user2460074 · Accepted Answer · 2016-01-18 11:22:24Z

2

Change your script to this

  CREATE TABLE TestCTE (SomeInt INTEGER)
  WITH CTE AS (SELECT 1 as SomeInt) 
  INSERT INTO TestCTE  SELECT SomeInt from CTE;

edited Jan 18, 2016 at 11:22

answered Jan 18, 2016 at 11:11

user2460074

1,2573 gold badges13 silver badges28 bronze badges

1 Comment

fez Over a year ago

This returns the same error. ERROR: 42601: syntax error at or near "insert". I recreated this with an integer column rather than timestamp and got the same error message.

fez · Accepted Answer · 2016-01-18 11:52:20Z

1

Try this

 CREATE TABLE TestCTE (SomeInt INTEGER)
;WITH CTE AS
(SELECT 1 as SomeInt)
INSERT (SomeInt) INTO TestCTE
SELECT SomeInt FROM CTE;

edited Jan 18, 2016 at 11:52

fez

1,8453 gold badges23 silver badges33 bronze badges

answered Jan 18, 2016 at 11:41

Hiten004

2,5011 gold badge24 silver badges34 bronze badges

Collectives™ on Stack Overflow

Redshift INSERT INTO TABLE from CTE

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Related