![]() ![]() ![]() If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value. The sequence generator operation is not transaction-safe. RETURNING id Code language: SQL (Structured Query Language) ( sql ) id The following statement inserts a new row into the fruits table and returns the value generated for the id column. If you want to get the value generated by the sequence when you insert a new row into the table, you use the RETURNING id clause in the INSERT statement. (1 row) Code language: SQL (Structured Query Language) ( sql ) ![]() For example, the following statement returns the recent value generated by the fruits_id_seq object: SELECT currval(pg_get_serial_sequence( 'fruits', 'id')) Code language: SQL (Structured Query Language) ( sql ) currval You can pass a sequence name to the currval() function to get the recent value generated by the sequence. To get the sequence name of a SERIAL column in a table, you use the pg_get_serial_sequence() function as follows: pg_get_serial_sequence('table_name','column_name') Code language: SQL (Structured Query Language) ( sql ) (2 rows) Code language: SQL (Structured Query Language) ( sql ) SELECT * FROM fruits Code language: SQL (Structured Query Language) ( sql ) id | name PostgreSQL inserted two rows into the fruits table with the values for the id column are 1 and 2. VALUES( DEFAULT, 'Apple') Code language: SQL (Structured Query Language) ( sql ) VALUES( 'Orange') Code language: SQL (Structured Query Language) ( sql ) See the following example: INSERT INTO fruits( name) To assign the default value for a serial column when you insert row into the table, you ignore the column name or use the DEFAULT keyword in the INSERT statement. The following statement creates the fruits table with the id column as the SERIAL column: CREATE TABLE fruits( However, this can be done easily by specifying the PRIMARY KEY constraint for the SERIAL column. It is important to note that the SERIAL does not implicitly create an index on the column or make the column as the primary key column. PostgreSQL provides three serial pseudo-types SMALLSERIAL, SERIAL, and BIGSERIAL with the following characteristics: Name OWNED BY table_name.id Code language: SQL (Structured Query Language) ( sql ) Id integer NOT NULL DEFAULT nextval( 'table_name_id_seq') ![]() Is equivalent to the following statements: CREATE SEQUENCE table_name_id_seq
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |