Thread: BUG #17130: Error while executing request
The following bug has been logged on the website:
Bug reference: 17130
Logged by: Viktor Alenkov
Email address: viktor@alenkov.name
PostgreSQL version: 13.3
Operating system: Docker
Description:
query:
CREATE SCHEMA "my_schema"
CREATE TABLE "my_ids" AS SELECT "a"."n" AS "id" FROM GENERATE_SERIES(1,
99) AS "a"("n");
I get the error `syntax error at or near "AS"`
On 8/3/21 9:20 AM, PG Bug reporting form wrote:
> CREATE SCHEMA "my_schema"
> CREATE TABLE "my_ids" AS SELECT "a"."n" AS "id" FROM GENERATE_SERIES(1,
> 99) AS "a"("n");
Understandable: you forgot the semicolon after the create schema statement.
Le mar. 3 août 2021 à 10:48, Erik Rijkers <er@xs4all.nl> a écrit :
On 8/3/21 9:20 AM, PG Bug reporting form wrote:
> CREATE SCHEMA "my_schema"
> CREATE TABLE "my_ids" AS SELECT "a"."n" AS "id" FROM GENERATE_SERIES(1,
> 99) AS "a"("n");
Understandable: you forgot the semicolon after the create schema statement.
Actually, the syntax should be valid. You can add a DDL right after CREATE SCHEMA without a semicolon in between (see the examples on https://www.postgresql.org/docs/13/sql-createschema.html). But I fail to explain why it doesn't work here. A workaround would be to add the semicolon as you said, but it should work without.
--
Guillaume.
On Tue, Aug 3, 2021 at 6:13 AM Guillaume Lelarge <guillaume@lelarge.info> wrote:
Le mar. 3 août 2021 à 10:48, Erik Rijkers <er@xs4all.nl> a écrit :On 8/3/21 9:20 AM, PG Bug reporting form wrote:
> CREATE SCHEMA "my_schema"
> CREATE TABLE "my_ids" AS SELECT "a"."n" AS "id" FROM GENERATE_SERIES(1,
> 99) AS "a"("n");
Understandable: you forgot the semicolon after the create schema statement.Actually, the syntax should be valid. You can add a DDL right after CREATE SCHEMA without a semicolon in between (see the examples on https://www.postgresql.org/docs/13/sql-createschema.html). But I fail to explain why it doesn't work here. A workaround would be to add the semicolon as you said, but it should work without.
CREATE TABLE and CREATE TABLE AS are two very different commands and only the former is documented to work.
David J.
Le mar. 3 août 2021 à 15:18, David G. Johnston <david.g.johnston@gmail.com> a écrit :
On Tue, Aug 3, 2021 at 6:13 AM Guillaume Lelarge <guillaume@lelarge.info> wrote:Le mar. 3 août 2021 à 10:48, Erik Rijkers <er@xs4all.nl> a écrit :On 8/3/21 9:20 AM, PG Bug reporting form wrote:
> CREATE SCHEMA "my_schema"
> CREATE TABLE "my_ids" AS SELECT "a"."n" AS "id" FROM GENERATE_SERIES(1,
> 99) AS "a"("n");
Understandable: you forgot the semicolon after the create schema statement.Actually, the syntax should be valid. You can add a DDL right after CREATE SCHEMA without a semicolon in between (see the examples on https://www.postgresql.org/docs/13/sql-createschema.html). But I fail to explain why it doesn't work here. A workaround would be to add the semicolon as you said, but it should work without.CREATE TABLE and CREATE TABLE AS are two very different commands and only the former is documented to work.
Oh, you're right. The schema_element only accepts CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER and GRANT. There's no CREATE TABLE AS on this list. My bad.
Guillaume.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Tue, Aug 3, 2021 at 6:13 AM Guillaume Lelarge <guillaume@lelarge.info> > wrote: >> Actually, the syntax should be valid. You can add a DDL right after CREATE >> SCHEMA without a semicolon in between (see the examples on >> https://www.postgresql.org/docs/13/sql-createschema.html). But I fail to >> explain why it doesn't work here. A workaround would be to add the >> semicolon as you said, but it should work without. > CREATE TABLE and CREATE TABLE AS are two very different commands and only > the former is documented to work. Right. CREATE SCHEMA allows additional DDL to create objects within the schema right away, but it only does DDL not DML, so you can't additionally populate the table as part of the command. regards, tom lane