Thread: create table with a CTE
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.
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> 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 asWITH 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".