CODEWITHSUNDEEP
sqlpostgresql
Anraiki
Anraiki

Reputation: 796

Postgres SQL requires me to wrap my insert with brackets

I have a simple order table that holds ordered data for a shopping app.

CREATE TABLE IF NOT EXISTS public."order"
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    order_type character varying(12)[] COLLATE pg_catalog."default",
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    first_name character varying(24)[] COLLATE pg_catalog."default",
    last_name character varying(24)[] COLLATE pg_catalog."default",
    email character varying(64)[] COLLATE pg_catalog."default",
    guest boolean NOT NULL DEFAULT false,
    location integer,
    user_id integer,
    phone character varying(24)[] COLLATE pg_catalog."default"
)

Initially, I had a JSONB column but was having trouble inserting data

My current query ideally would look like this:

INSERT INTO public."order"(
    order_type, first_name, last_name, phone, email, guest, location, user_id)
    VALUES ('Something', 'Answer', 'hello', '1234567890', '[email protected]', true, 123, 0) RETURNING *;

But it is causing an error with the following:

ERROR: malformed array literal: "Something" LINE 3: VALUES ('Something', 'Answer', 'hello', '1234567890',...

DETAIL: Array value must start with "{" or dimension information. SQL state: 22P02 Character: 114

But if I wrap those values inside a "{}", it inserts fine.

Why is that? I have other table that don't need the values to be wrapped in brackets so there is something I am not understanding and need help with that.

INSERT INTO public."order"(
    order_type, first_name, last_name, phone, email, guest, location, user_id)
    VALUES ('{Something}', '{Answer}', '{hello}', '{1234567890}', '{[email protected]}', true, 123, 0) RETURNING *;

Successfully run. Total query runtime: 158 msec. 1 rows affected.

Upvotes: 0

Views: 207

Answers (1)

Anraiki
Anraiki

Reputation: 796

CREATE TABLE IF NOT EXISTS public."order"
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    order_type character varying(12)[] COLLATE pg_catalog."default",
    created_at timestamp with time zone,
    updated_at timestamp with time zone,
    first_name character varying(24)[] COLLATE pg_catalog."default",
    last_name character varying(24)[] COLLATE pg_catalog."default",
    email character varying(64)[] COLLATE pg_catalog."default",
    guest boolean NOT NULL DEFAULT false,
    location integer,
    user_id integer,
    phone character varying(24)[] COLLATE pg_catalog."default"
)

When creating the table

first_name character varying(24)[]

Having the "[]" by the data type would require to be an array

Thus

first_name character varying(24)

would no longer need the value to be wrap in brackets

Upvotes: 0

Related Questions