What does %% in PL/pgSQL mean?

Question

I was reading over Instagrams sharding solution and I noticed the following line:

SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;

What does the %% in the SELECT line above do? I looked up PostgreSQL and the only thing I found was that %% is utilized when you want to use a literal percent character.

CREATE OR REPLACE FUNCTION insta5.next_id(OUT result bigint) AS $$
DECLARE
   our_epoch bigint := 1314220021721;
   seq_id bigint;
   now_millis bigint;
   shard_id int := 5;
BEGIN
   SELECT nextval('insta5.table_id_seq') %% 1024 INTO seq_id;

   SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
   result := (now_millis - our_epoch) << 23;
   result := result | (shard_id << 10);
   result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;

it sure *looks* like it's being used like mod operator but I thought mod was only one `%` — Brad, Apr 04 '14 at 14:20
My guess is that the particular CMS does not like a single `%` and that the author tried to escape it by doubling it. (or even worse: the CMS escapes it by doubling it on output) — joop, Apr 04 '14 at 14:46
Normally [`%%` would be a unary operator from `hstore`](http://www.postgresql.org/docs/current/static/hstore.html) but that doesn't seem to be the case here as pointed out by @pozs. Custom operator in their install, maybe? — Craig Ringer, Apr 04 '14 at 15:32

Erwin Brandstetter · Accepted Answer · 2021-07-09T01:22:20.187

The only place I can think of, where a % would be doubled up in standard Postgres is inside the format() function, commonly used for producing a query string for dynamic SQL. Compare examples here on SO.

The manual:

In addition to the format specifiers described above, the special sequence %% may be used to output a literal % character.

Tricky when using the modulo operator % in a dynamic statement!

I suspect they are running dynamic SQL behind the curtains - which they generalized and simplified for the article. (The schema-qualified name of the sequence is 'insta5.table_id_seq' and the table wouldn't be named "table".) In the process they forgot to "unescape" the modulo operator.
That's what they may actually be running:

EXECUTE format($$SELECT nextval('%I') %% 1024$$, seq_name)
INTO seq_id;

Inside of plpgsql, I just used: `seq_id := nextval(sequence_name) % 1024;` — Philippe, Apr 18 '18 at 19:18

score 2 · Answer 2 · answered Apr 04 '14 at 14:39

2

With default installation (on 9.2):

ERROR: operator does not exist: bigint %% integer
SQL state: 42883

So i would say it could be

a custom operator
or a typo, and they want to write the modulo operator: %

answered Apr 04 '14 at 14:39

pozs

34,608
5
57
63

Typo is the answer. Reading the article, the logic requires the modulo operator in its place. I posted a hypothesis how that might have happened ... – Erwin Brandstetter Apr 04 '14 at 18:26

score 1 · Answer 3 · answered Apr 04 '14 at 14:24

1

Looks like an escaped modulo operator to me.

answered Apr 04 '14 at 14:24

Jodrell

34,946
5
87
124

What does %% in PL/pgSQL mean?

3 Answers3

Linked