postgres syntax error on grant select on table

Question

I have a query:

            DROP TABLE IF EXISTS abc;
            CREATE TABLE abc AS
            SELECT title            
            FROM people
            WHERE title like 'ram%'           
            GRANT SELECT ON abc IN SCHEMA public TO ram;

But on running this: I get the following syntax error:

(psycopg2.errors.SyntaxError) syntax error at or near "IN"
LINE 26:         GRANT SELECT ON abc IN SCHEMA publi...

without grant query its working fine.

What am I missing here?

Laurenz Albe · Accepted Answer · 2021-08-27 06:13:49Z

1

To specify the schema, use ON public.abc.
The syntax IN SCHEMA public can only be used with ON ALL TABLES.

Also, you have to separate CREATE TABLE and GRANT with a semicolon, as they are two separate statements.

answered Aug 27, 2021 at 6:13

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

richyen · Accepted Answer · 2021-08-27 07:14:03Z

1

PostgreSQL does not support GRANT syntax during CREATE TABLE command. You'll need to separate them.

For the GRANT, you will be better off prefixing the table name with schema (i.e., public.abc), as IN SCHEMA syntax is not supported unless you're GRANTing ON ALL TABLES. Here's a full working example:

            DROP TABLE IF EXISTS abc;
            CREATE TABLE abc AS
            SELECT title            
            FROM people
            WHERE title like 'ram%';
            GRANT SELECT ON public.abc TO ram;

edited Aug 27, 2021 at 7:14

answered Aug 27, 2021 at 6:12

richyen

10.3k4 gold badges18 silver badges36 bronze badges

Collectives™ on Stack Overflow

postgres syntax error on grant select on table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related