postgrest: value too long for type character(1)

[lucas-r-oliveira]

Environment

• PostgreSQL version: 11.7
• PostgREST version: 7.0
• Operating system: Raspbian 10

Description of issue

I have this table

CREATE TABLE api.user (
id serial NOT NULL,
name varchar(80) NOT NULL,
pin char(4) NOT NULL,
CONSTRAINT pk_user PRIMARY KEY (id)
);

and this function:
create or replace function functions.register_user(username varchar(80),
pin char(4))
returns void as
$$
insert into api.user values(DEFAULT, username, pin);
$$ language sql;

Within the psql client I can easily run for example
'insert into api.user values(DEFAULT, "test", "0000");'

Whenever I try something similar with curl, I get the following response:
{"details":null,"code":"22001","message":"value too long for type character(1)","hint":null}

It works only if I POST 'pin' with at most 1 char

[wolfgangwalther]

What happens here is, that:

1. Postgres discards the (4) in create function ... (..., pin char(4)).... You can test that easily by creating a noop function that takes a char(4) as argument - you will be able to call it with any length of text. According to the docs:

The full SQL type syntax is allowed for declaring a function's arguments and return value. However, parenthesized type modifiers (e.g., the precision field for type numeric) are discarded by CREATE FUNCTION. Thus for example CREATE FUNCTION foo (varchar(10)) ... is exactly the same as CREATE FUNCTION foo (varchar)

1. However PostgREST uses the function argument types returned by postgres as just character to create a sub-query similiar to this:
   SELECT * FROM json_to_recordset('[{"pin":"abcd"}]') AS _(pin character)
   In this case postgres takes the character as the same thing as character(1), which leads to the same error you are getting.

Not sure, yet, what the best way to fix this will be, but this is definitely a bug.

Since postgres removes the (4) it does not make a difference in your case and you should be able to just use pin varchar or pin text in your function definition as a workaround.

[steve-chavez]

Additionally you might consider changing to a check constraint to validate length.
This is also recommended on the pg wiki: Don't use char(n) even for fixed-length identifiers.

[lucas-r-oliveira]

I used varchar in the meantime and it worked. Thank you so much for your answers.