RedSift INSERT INTO TABLE of CTE

Question

According to the Redshift WITH Clause directive, you can use the WITH clause with a statement INSERT INTO...SELECT. However, checking this, I get the following error. Is this not possible, or do I have the wrong syntax?

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

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

Interestingly, it supports insertion into a new table i.e.

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

Command completed successfully (1 row affected)

The EDIT: . To confirm, I get the same error when using a column INTEGER, not TIMESTAMP:

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

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

Answer 1

CTE ( , )

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

Answer 2

; , , - :

: create table as select

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

:

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

Vanilla Postgres, RDS

Answer 3

Change script to

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

Answer 4

try it

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