Postgres Index Creation - Single Quotes On Index Field Name

Question

Using Postgres 10.11.

Table schema:

create table "db"."table"
(
    id varchar(4096) not null
        constraint "db-table_pkey"
            primary key,
    etag varchar(255) not null,
    jsondata jsonb not null
);

We found to be accidentally creating an index like this:

create index "something"
    on "db"."table" (('id'::text), 
                     (jsondata -> 'someOtherField'::text), 
                     ((jsondata -> 'someOtherField2'::text) = 'false'::jsonb));

And this query would not use the above index:

select *
FROM "db"."table" 
where jsonData->'someOtherField' = '"value"'
  AND jsonData->'someOtherField2' = 'false'
  AND id = 'testid'
order by id 
limit 5

Remaking the index like this solved the issue:

create index "something"
    on "db"."table" ((id), -- JUST ID!!!
                     (jsondata -> 'someOtherField'::text), 
                     ((jsondata -> 'someOtherField2'::text) = 'false'::jsonb));

It seems we are not supposed to use single quotes on index column names. Can anyone tell me what the index was made on in the first part? Was it just made on the 'id' string constant? Could I even trigger that index with any query? Or are they the same index?

Laurenz Albe · Accepted Answer · 2020-11-24 14:58:24Z

1

It seems we are not supposed to use single quotes on index column names.

Your intuition is right.

'id' is a string constant, so if you index that, all index entries will have the same constant value. Such an index is pretty useless (there are use cases for partial or unique indexes on constants though).

To reference a column, you must use unquoted id or double quoted "id".

The documentation has a whole chapter on that.

answered Nov 24, 2020 at 14:58

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

Postgres Index Creation - Single Quotes On Index Field Name

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related