JIYIK CN >

A VALUES in MySQL Auto_Incrementis an auto-incrementing variable that helps provide a unique identification for a set of data in a table. It is most commonly used PRIMARYin a key to uniquely index a row.

In MySQL, we can AUTO INCREMENTappend to any column we want.

CREATE TABLE test (
    id int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

However, today we will learn how to do this in PostgreSQL.

In PostgreSQL, SERIALthis is done using the keywordAUTO_INCREMENT

Let's make a table with a idand a column .colorCAT

create table cat(
	id SERIAL,
	color varchar not null
);

Let's go ahead and insert some values ​​into it.

insert into cat (color) values('black'), ('white'), ('brown'), ('tuxedo');

Make sure to specify COLUMN_NAMEso that the function doesn't select all columns and violate SERIALthe goal. If you dig into the PostgreSQL documentation, you'll learn that:

When you click RUN, it will display the following result.

Output:

So, you can see that SERIALthe method is effectively implemented AUTO_INCREMENT.

A brief working guide to PostgreSQL SERIALand its alternatives

SERIALThe keyword generates an integer column. Instead SERIAL, you can also use SERIAL4, which represents 4 bytes.

If you want more identifiers or a larger range of automatically generated values, you can also use BIGSERIALor SERIAL8, which can hold up to 2^31identifiers.

SERIALmethod can be replaced with the following.

create sequence id_col_AI;
create table cat(
	id integer DEFAULT nextval('id_col_AI') NOT NULL,
	color varchar not null
);

So, what's going on here? SEQUENCEA new number generator is defined. If you want to START VALUEchange to a value you like, you can use START (your number)the parameter at the end of the declaration.

Then IDin the column, define it as to DEFAULTget the next value from the generator within the column. DEFAULTIt is preferred to assign NULLa default value other than to the column.

You then reference the created SEQUENCEand call NEXTVAL(your seq)to get the values ​​in sequence. This NOT NULLprevents the user from implicitly or explicitly inserting any NULL.

If your table is already created, you can also try extending it with ALTER TABLEthe method.

ALTER table cat ALTER id set DEFAULT nextval('id_col_AI');

Using the PostgreSQL AUTO_INCREMENTclauseGENERATED { BY DEFAULT || ALWAYS} AS

You can also add auto increment column using following code.

id integer generated by default as identity

We use BY DEFAULTinstead of ALWAYSbecause the former tends to write user values, but the latter only allows system-specified values. You can also use ALWAYS.

On many systems, this will probably work perfectly. However, if using ALWAYSreturns an error on insert, INSERTappend a INSERT INTO clause OVERRIDING SYSTEM VALUEto allow user-specific values.

insert into cat (color) overriding system value
values('black'), ('white'), ('brown'), ('tuxedo');

This uses an automatically appended sequence, such as the random generator mentioned earlier.