Question 89823 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres invalid input error on SELECT statement – Postgresql

I am using Supabase as my DB.

I have users table with id field being populated with uuid_generate_v4().

I also have table bank_ao with foreign key bankId pointed to users.id.

When I do following:


const uuid = req.params.id //this 100% works correctly and returns UUID every time!

await supabase
      .from("bank_ao")
      .select()
      .eq("bankId", uuid);

I get this error

{"code":"22P02","details":null,"hint":null,"message":"invalid input syntax for type bigint: "914eda70-2ecf-49b0-9ea6-87640944ed16""}

I don’t have BIGINT field in my entire DB. I have checked 100x.

I also tried to add ' around my uuid variable, but it still doesn’t work.

Any ideas?

Could this be some error specific to Supabase?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem was the following.

    Before I used UUID as my id column value, it used to be BIGINT. When I changed it, it didn't change in cache, or some other type of memory. So, it threw an error.

    I fixed it by re-creating entire database (I tried with table first, but it didn't work).

    I have read online that if this is local version of Supabase you can just restart Postgres and it works fine (https://github.com/supabase/supabase/discussions/11891).


  2. 0

    You need to cast the UUID into text to compare it there:

    const uuid = 'e1bee290-45d1-4233-ad96-b9924288d64c'
const supabase = createClient(SUPABASE_URL, SUPABASE_KEY);
const { data: ret, error } = await supabase
.from('bank_ao')
.select()
.eq('bankId::text', uuid);

console.log(ret);

    Assuming table format as:

    enter image description here

    Output:

    [
    {
    bankId: ‘e1bee290-45d1-4233-ad96-b9924288d64c’,
    created_at: ‘2023-02-08T16:03:21.064501+00:00’,
    foo: ‘bar’,
    bar: { bar: ‘foo’ }
    }
    ]

    Please note that for using BigInt in Javascript safely, then you can check it here (similar approach).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search