How to create a sequence for integers in PostgreSQL?

Question

I'm trying to create a sequence for a product id to start at 100 in Supabase SQL editor and I'm getting this error:

Failed to validate SQL query: syntax error at or near ")"

What am I doing wrong in this code?

create sequence product_id_seq
    start with 100
    increment by 1
    no maxvalue
    no minvalue;

create table products 
(
    id int4 not null default nextval('product_id_seq'),
    name text not null,
    price numeric(10,2) not null,
    description text,
    category_id integer references categories (id),
    preview text,
    images text[],
    stock integer not null,
    sale boolean,
    sale_price numeric(10,2),
    sale_text text,
    featured integer references featured (id),
    primary key (id)
);

The code is correct for PostgreSQL. Don't know what this Supabase thing is doing. — , Feb 27 '23 at 13:21
@a_horse_with_no_name I found the error, see my answer. Thanks anyways — Mintee, Feb 27 '23 at 13:26

Mintee · Answer 1 · 2023-02-27T13:41:12.823

0

The error was here:

featured integer references featured (id),

Apparently the column name can't be the same the table name referenced to, I changed it to:

featured_in integer references featured (id),

But this is most likely a Supabase thing and not PostgreSQL overall.

edited Feb 27 '23 at 13:41

answered Feb 27 '23 at 13:25

Mintee

505
4
22

That must be a Supabase problem/bug. Because Postgres does not have such a limitation https://dbfiddle.uk/0VaLxzfK – Feb 27 '23 at 13:29
@a_horse_with_no_name Don't think it's a bug but it's like they use a different/limited version of PostgreSQL. For example WITH CHECK OPTION is not supported in Supabase. – Mintee Feb 27 '23 at 13:36
Then you shouldn't tag your question with PostgreSQL if it's not really PostgreSQL – Feb 27 '23 at 14:36

How to create a sequence for integers in PostgreSQL?

1 Answers1