create table with a CTE

On Sat, Nov 14, 2020 at 6:21 AM PG Doc comments form <noreply@postgresql.org>
wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-createtableas.html
Description:

Hi.
I think you should include an example on how to create a table from a query
that uses a CTE in the query.

[shrug]
The obvious attempt to specify:

create table cte_tbl as
WITH cte (val) AS (select 1)
SELECT * FROM cte;

works so I'm not really sure that such an example adds valuable insight.

I suppose that adding an example, and some explanation, that one can get
update/delete output to populate the newly created table by wrapping them
in a CTE would provide the user a suggestion on how to overcome the fact
that simply writing the following doesn't work.

create table cte_update as
update other_table set value = 2
returning *;

That isn't something we make a concerted effort to accomplish but it also
isn't avoided.

David J.

On 2020-11-15 04:55, David G. Johnston wrote:

On Sat, Nov 14, 2020 at 6:21 AM PG Doc comments form
<noreply@postgresql.org <mailto:noreply@postgresql.org>> wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/13/sql-createtableas.html
Description:

Hi.
I think you should include an example on how to create a table
from a query
that uses a CTE in the query.

[shrug]
The obvious attempt to specify:

create table cte_tbl as
WITH cte (val) AS (select 1)
SELECT * FROM cte;

works so I'm not really sure that such an example adds valuable insight.

That is probably a totaly worthless example for a experienced user.

The only insight would be for a new user that "oh, I can do it like that".