Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

value too long for type character(1) after update to 11.1.0 #2861

Closed
psteinroe opened this issue Jul 13, 2023 · 5 comments · Fixed by #2866
Closed

value too long for type character(1) after update to 11.1.0 #2861

psteinroe opened this issue Jul 13, 2023 · 5 comments · Fixed by #2866
Labels

Comments

@psteinroe
Copy link

Environment

  • Supabase CLI 1.77.9 (latest at the time of writing)

Description of issue

I have table contact that refers to a table country via a foreign key of type char(2).

since updating to latest cli, an

.insert({country: 'DE'})

returns

    {
      code: '22001',
      details: null,
      hint: null,
      message: 'value too long for type character(1)'
    }

Executing the sql statement directly does work as expected.

insert into contact (country) values (
  'DE'
  );

Based on my findings, PostgREST must be causing this, but feel free to move this issue if required.

You can find a full repro in https://github.com/psteinroe/supabase-cache-helpers

The daily tests started failing at the same time the cli updated the postgrest version.

failing ci: https://github.com/psteinroe/supabase-cache-helpers/actions/workflows/ci.yml?query=branch%3Amain

commit that updated pgrst version from 10.1.2 to 11.1.0: supabase/cli@ada5272

@steve-chavez
Copy link
Member

@psteinroe Please share the tables definition, I'm a bit lost on those repos.

Maybe related to #1586

@psteinroe
Copy link
Author

@psteinroe Please share the tables definition, I'm a bit lost on those repos.

sure!

create table public.country (
    code char(2) primary key not null,
    name varchar(255) not null,
);
create table public.contact (
    id uuid primary key not null default gen_random_uuid (),
    country char(2) references public.country on delete set null
);

maybe the foreign key ref is not required, did not test it yet.

the linked issue seems pretty close to mine. let me know if I should try to repro it in a more enclosed environment.

@steve-chavez
Copy link
Member

✔️ Reproduced:

create table country1 (
    code char(2) primary key not null,
    name varchar(255) not null
);

create table contact1 (
    id uuid primary key not null default gen_random_uuid (),
    country char(2) references country1 on delete set null
);

insert into country1 values ('DE', 'Germany');
http POST 'localhost:3000/contact1' <<JSON
> {"country": "DE"}
> JSON
HTTP/1.1 400 Bad Request
Content-Type: application/json; charset=utf-8
Date: Thu, 13 Jul 2023 13:53:49 GMT
Server: postgrest/11.1.0 (5a04ec7)
Transfer-Encoding: chunked

{
    "code": "22001",
    "details": null,
    "hint": null,
    "message": "value too long for type character(1)"
}

Same underlying reason as #1586 (comment)

I think this might have happened for inserts on #2542. Back in 10.1.1.

@steve-chavez
Copy link
Member

I've noticed it's possible to cast using the length in the char.

select * from json_to_record('{"c":"bar"}') as x(c char(2));
ERROR:  value too long for type character(2)

select * from json_to_record('{"c":"bar"}') as x(c char(3));
  c  
-----
 bar

But pg loses the length of the type in some cases (this is happening on our schema cache).

select pg_typeof('ab'::char(2));

 pg_typeof 
-----------
 character

Although I do see it on some psql builtins.

\d+ country1
                                                 Table "test.country1"
 Column |          Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 code   | character(2)           |           | not null |         | extended |             |              | 

So it must be possible to obtain it from the catalogs.

@alexStrickner00
Copy link

we observed the same issue for a table containing a char(3) column

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Development

Successfully merging a pull request may close this issue.

3 participants