1

I know that I can create an index that uses concatenation as follows:

CREATE INDEX idx_table1_field1_field2 ON table1((field1 || field2));

But this is not quite what I need. I will try to explain exactly what kind of index I need. Let's assume there is a table (let's call it table1) that contains two fields (field1 and field2) of the character data type, each with a length of 15.

+----------------+----------------+
| field1         | field2         |
| character (15) | character (15) |
+----------------+----------------+
| James          | Black          |
+----------------+----------------+
| Michael        | White          |
+----------------+----------------+
| David          | Silver         |
+----------------+----------------+

Now if I execute the following SQL query:

SELECT field1 || field2, CONCAT(field1, field2) FROM table1

I get the following result:

+------------------+---------------------------------+
|  ?column?        | concat                          |
|  text            | text                            |
+------------------+---------------------------------+
| JamesBlack       | James          Black            |
+------------------+---------------------------------+
| MichaelWhite     | Michael        White            |
+------------------+---------------------------------+
| DavidSilver      | David          Silver           |
+------------------+---------------------------------+

If I use two vertical bars (||), the server does not check that the name James has 5 characters and does not add 10 spaces to the field length (15). If I use the CONCAT function, I see that the field length is not ignored and the server added 10 spaces to the name James.

I desire to create an index that will take the field length into account (how CONCAT does this).

To create the desired index, I tried to execute the following SQL query:

CREATE INDEX idx_table1_field1_field2 ON table1(CONCAT(field1, field2));

But I got the following error: ERROR: functions in index expression must be marked IMMUTABLE

Is there another way to create the such index?

Improve this question
5
  • If you don't want trailing spaces why are you using character(n) in the first place? 15 is the field length if you choose character. If that's not what you want, use varchar like everyone else. Commented Feb 5 at 23:05
  • I second Richard's comment, character is a red flag. You almost always want text. See postgresql.org/docs/current/datatype-character.html Commented Feb 5 at 23:25
  • 1
    This question is similar to: Why am I getting a an error when creating a generated column in PostgreSQL?. Specifically he mentions making an immutable version of concat. Or don't use char. If you really need the padding, pad it yourself. If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. Commented Feb 5 at 23:34
  • @Schwern But if I use pad, I have to specify the length. I would like to find a solution without explicitly specifying the length. What other data type should I try? Commented Feb 6 at 6:09
  • @Schwern And thank you for the link. Now I'm examining the essence of that question. There is probably a similar problem. Commented Feb 6 at 6:34

2 Answers 2

Reset to default
3

You can use RPAD() in this, to fill it up with whitespace (the default):

CREATE TABLE bar(field1 char(15), field2 char(15));

CREATE INDEX ON bar ((rpad(field1,15) || rpad(field2, 15)));

INSERT INTO bar(field1, field2) VALUES ('a', 'b');

EXPLAIN(ANALYSE , VERBOSE , BUFFERS , SETTINGS )
SELECT * FROM bar WHERE (rpad(field1,15) || rpad(field2, 15)) = 'a              b              ';

It's important that you write the WHERE condition just like the index definition, otherwise the index can't be used.

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

1 Comment

Thank you for your example! I understand the restrictions related to using such an index.
2

Unless you definitely want fixed length fields, and your example uses names so you probably don't, don't use the character type. Use text, or if you have a specific reason to limit your data length, varchar.

From the PostgreSQL docs...

There is no performance difference among these three types, apart from increased storage space when using the blank-padded type [character], and a few extra CPU cycles to check the length when storing into a length-constrained column. While character(n) has performance advantages in some other database systems, there is no such advantage in PostgreSQL; in fact character(n) is usually the slowest of the three because of its additional storage costs. In most situations text or character varying [varchar] should be used instead.

The padding isn't a property of the data, it's formatting. If you really want that padding, use text and add it yourself with rpad.

create index idx_format on table1((rpad(field1, 15) || rpad(field2, 15)));

Or try to make your own immutable character concat.

To explain || vs concat we reference 9.4 String Functions And Operators.

Except where noted, these functions and operators are declared to accept and return type text. They will interchangeably accept character varying arguments. Values of type character will be converted to text before the function or operator is applied, resulting in stripping any trailing spaces in the character value.

|| is defined to take text and returned text, text || text → text. It will cast your char to text losing the trailing whitespace. concat (which is non-standard) is defined to take any type and return text, concat ( val1 "any" [, val2 "any" [, ...] ] ) → text.

Improve this answer

3 Comments

I appreciate your answer. But I need character. I need fields with fixed lengths. I have a database with a large number of fields of the character data type. I have an application that uses this database. Now I'm migrating this database to PostgreSQL.
It seems like I should implement my own immutable character CONCAT.
@IvanYuzafatau You need backwards compatibility. A strategy I like when I want to improve a schema and maintain backwards compatibility is to create the table with a different name and my schema improvements (switch to text), then create a backwards compatible view. create view table1 as select rpad(field1, 15) as field1, rpad(field2, 15) as field2 from table1_new. Old queries relying on the char padding hit the view, new queries use the new table with text fields. Doesn't help your indexing problem, tho.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.