How to insert without giving column names but not giving identity column-CodePudding

Table definition:

CREATE TABLE IF NOT EXISTS public.test
(
    "Id" integer NOT NULL GENERATED ALWAYS AS IDENTITY (INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1),
    "SomeColumn" character(100) COLLATE pg_catalog."default",
    CONSTRAINT test_pkey PRIMARY KEY ("Id")
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.test
    OWNER to postgres;

I am trying this query:

INSERT INTO public.test VALUES ('testData');

But PostgreSQL throws this error:

ERROR: invalid input syntax for type integer: "testData"
LINE 1: INSERT INTO public.test VALUES ('testData');

I know this is valid in SQL Server. Is there a way the achieve this behaviour in PostgreSQL?

I do not want to specify the column names. Columns are defined in the order, but the identity column does not exist in the query.

CodePudding user response：

I want to not give the column names

That's a bad idea. You should always specify the target columns for an INSERT statement. Especially if you want to skip some, but not others.

However, if you insist on bad coding style, you can use the DEFAULT keyword

INSERT INTO public.test VALUES (DEFAULT, 'testData');

CodePudding user response：

INSERT INTO public.test VALUES (NULL, 'testData');