Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-serial/ below:

PostgreSQL - SERIAL - GeeksforGeeks

When working with PostgreSQL, we need to create tables with unique primary keys. PostgreSQL offers a powerful feature known as the SERIAL pseudo-type which simplifies generating auto-incrementing sequences for columns.

In this article, we’ll learn about the PostgreSQL SERIAL pseudo-type by explain how it works and provide practical examples with outputs.

In PostgreSQL, the SERIAL pseudo-type allows you to create an auto-incrementing integer column, typically used for primary keys.

It automatically generates a sequence of numbers that increase by one for each new row. This feature simplifies the process of creating unique identifiers for each row in a table.

• The PostgreSQL SERIAL type creates a sequence and sets it as the default value for the column.
• It adds a NOT NULL constraint because the sequence values are always non-null.
• When a table or column is dropped, the associated sequence is automatically removed.
• SERIAL does not create an index on the column by default, so you may need to explicitly define a PRIMARY KEY or UNIQUE constraint.

PostgreSQL offers three variations of the SERIAL pseudo-type, depending on the storage size and value range:

1. SMALLSERIAL: Uses 2 bytes and supports values from 1 to 32,767.
   
2. SERIAL: Uses 4 bytes and supports values from 1 to 2,147,483,647.
   
3. BIGSERIAL: Uses 8 bytes and supports values from 1 to 9,223,372,036,854,775,807.

To define a SERIAL column in PostgreSQL, you simply specify the type as SERIAL when creating the table. Here's the basic syntax:

CREATE TABLE table_name (
    column_name SERIAL
);

Let’s create a table employees with an auto-incrementing emp_id column using the PostgreSQL SERIAL pseudo-type.

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name TEXT NOT NULL,
    emp_email VARCHAR(100) NOT NULL,
    emp_age SMALLINT
);

In this example:

• The emp_id column is defined as SERIAL, which automatically generates unique integer values for each new row.
• The PRIMARY KEY constraint ensures that emp_id remains unique.

Now, let’s insert some data into the employees table, omitting the emp_id column since it will be auto-generated.

INSERT INTO employees (emp_name, emp_email, emp_age)
VALUES
    ('Alice', 'alice@example.com', 30),
    ('Bob', 'bob@example.com', 35),
    ('Charlie', 'charlie@example.com', 28);

Output:

As shown, the PostgreSQL serial column example automatically assigns sequential values (1, 2, 3, …) to the emp_id column.

You can explicitly insert a value into the SERIAL column using the DEFAULT keyword. This is useful if you want to ensure that the next available sequence value is used.

INSERT INTO employees (emp_id, emp_name, emp_email, emp_age)
VALUES
    (DEFAULT, 'David', 'david@example.com', 32);

Output:

The DEFAULT keyword inserts the next value in the sequence for emp_id.

The RETURNING clause is a handy feature that allows you to retrieve the value of the SERIAL column immediately after inserting a row.

INSERT INTO employees (emp_name, emp_email, emp_age)
VALUES ('Emma', 'emma@example.com', 29)
RETURNING emp_id;

Output:

The RETURNING clause retrieves the newly inserted emp_id value (5 in this case).

If you want to retrieve the sequence name associated with a SERIAL column, you can use the pg_get_serial_sequence() function.

SELECT pg_get_serial_sequence('employees', 'emp_id');

Output:

This shows that the sequence name for the emp_id column is employees_emp_id_seq.

To get the current value generated by the SERIAL sequence, use the currval() function.

SELECT currval(pg_get_serial_sequence('employees', 'emp_id'));

Output:

This confirms that the last value generated by the sequence is 5.

Understanding the difference between SERIAL and BIGSERIAL is crucial when designing your database schema. SERIAL is suitable for most applications, but if we need a larger range of values then consider using BIGSERIAL.

• SERIAL uses 4 bytes, allowing for a maximum value of 2,147,483,647.
  
• BIGSERIAL uses 8 bytes, allowing for a maximum value of 9,223,372,036,854,775,807.

CREATE TABLE large_numbers (
    big_id BIGSERIAL PRIMARY KEY,
    description TEXT
);

This table uses BIGSERIAL to ensure it can accommodate larger values.

• SERIAL in PostgreSQL automatically generates unique, sequential integers for primary keys.
  
• It includes SMALLSERIAL, SERIAL, and BIGSERIAL with varying sizes and ranges.
  
• Easily integrates into table creation for streamlined primary key management.
  
• Enhances database efficiency by automating ID generation and supporting efficient querying and indexing.

The PostgreSQL SERIAL pseudo-type is an efficient way to handle auto-incrementing columns, especially for primary keys. By using the SERIAL, BIGSERIAL, or SMALLSERIAL types, you can simplify your table designs while ensuring that unique, sequential values are automatically generated for each new row. The examples provided in this article should give you a clear understanding of how to use SERIAL in PostgreSQL.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4