postgres sequence - ghdrako/doc_snipets GitHub Wiki

sequence postgres

Permitions

By default, only the owner of the sequence has permission to access it. To allow other roles or users to use the sequence, you need to grant them permission explicitly. You grant the USAGE permission on the sequence, which allows the role or user to use the sequence to generate values, and the SELECT permission, which allows the role or user to retrieve the current value of the sequence.

grant usage, select on sequence claim01_claim_id_seq to readonly;
\z claim01_claim_id_seq

create sequence manualy

CREATE SEQUENCE name [INCREMENT [ BY ]
increment][ MINVALUE minvalue | NO MINVALUE ] [
MAXVALUE maxvalue| NO MAXVALUE ][ START [ WITH ]
start ] [ CACHE cache ] [ [ NO ] CYCLE]

Sequence Functions:

  • nextval(regclass) -> bigint
    • Increments sequence value set as per increment value set at the time of creating the sequence and return next incremented value in the sequence.
    • By default, it increments the value by 1.
    • It needs USAGE and UPDATE privileges on the sequence.
    • Syntax: SELECT NEXTVAL(‘SCHEMA_NAME.SEQ_NAME’) where SCHEMA_NAME is the schema name and SEQ_NAME is the sequence name.
    • Eg: SELECT NEXTVAL(‘test_schema.test_seq’);
  • currval(regclass) -> bigint
    • It gives the latest used value for a specific sequence.
    • It gives session level value, and in case nextval is not initiated, then it will give an error.
SELECT currval('sale_id_seq');
ERROR:  currval of sequence "sale_id_seq" is not yet defined in this session
  • It also needs USAGE and UPDATE privileges on the sequence.
  • setval (regclass, bigint [, boolean ]) → bigint
    • This function can set a custom value for the sequence.
    • It needs the UPDATE privilege on the sequence.
    • For example:
select setval(‘test_schema.test_seq’,99)
# Next nextval will return 100
select setval(‘test_schema.test_seq’,99,true)
# Next nextval will return 100
select setval(‘test_schema.test_seq’,99,false)
#Next nextval will return 99
  • lastval() → bigint
    • Determines the last value used in the particular session.
    • It gives session level value, and in case nextval is not initiated, then it will give an error.
    • This function is identical to currval, except that instead of taking the sequence name as an argument, it refers to whichever sequence nextval was most recently applied to in the current session. (Reference: PostgreSQL Community Sequences).

In PostgreSQL 10 the view pg_sequences was added to provide easy access to information about sequences:

SELECT * FROM pg_sequences WHERE sequencename = 'sale_id_seq';
─[ RECORD 1 ]─┬────────────
schemaname    │ public
sequencename  │ sale_id_seq
sequenceowner │ db
data_type     │ integer
start_value   │ 1
min_value     │ 1
max_value     │ 2147483647
increment_by  │ 1
cycle         │ f
cache_size    │ 1
last_value    │ 155

Another way to get the current value of a sequence is using the undocumented function pg_sequence_last_value:

db=# SELECT pg_sequence_last_value('sale_id_seq');
 pg_sequence_last_value
────────────────────────
                   155

You can also query a sequence, just like you would a table:

db=# SELECT * FROM sale_id_seq;

 last_value │ log_cnt │ is_called
────────────┼─────────┼───────────
        155 │      10 │ t

Serial Data Type Sequence

starszy sposób definiowania automatycznych wartości w PostgreSQL. Sekwencja jest zarządzana jako osobny obiekt w bazie danych. Eksportowanie i importowanie danych (np. w przypadku dumpu) może wymagać ręcznej pracy przy ponownym łączeniu kolumn z sekwencjami

CREATE TABLE claim01
(
   claim_id      serial not null,
   id integer,
   name varchar(6),
  CONSTRAINT  claim01_pk PRIMARY KEY(claim_id));

When you create a sequence using the serial data type or any other sequence data type in PostgreSQL, the sequence is automatically named using the [table_name]_[column_name]_seq naming convention.

PostgreSQL has a maximum identifier length of 63 characters, which includes the sequence name. So, if the combined length of the table and column names exceeds 63 characters, the sequence name will be truncated to fit within the maximum identifier length.

Sequence with Identity column definition

Jest to nowoczesny sposób definiowania kolumn z automatycznym generowaniem wartości w PostgreSQL, wprowadzony od wersji 10. Nie korzysta z oddzielnej sekwencji przypisanej nazwą, co czyni strukturę bardziej przejrzystą.Działa lepiej w kontekście eksportu i importu danych, ponieważ odwołuje się wewnętrznie do mechanizmu bazy danych.

Obsługuje dwa tryby:

  • GENERATED ALWAYS: wartość generowana automatycznie, ręczne nadpisywanie nie jest dozwolone (chyba że wymuszone przez OVERRIDING).
  • GENERATED BY DEFAULT: można nadpisać wartość podczas wstawiania, jeśli to konieczne.
CREATE TABLE claim02
(claim_id   integer   GENERATED ALWAYS AS IDENTITY,
 id integer,
 name varchar(6),
CONSTRAINT  claim02_pk PRIMARY KEY(claim_id)
);

After the creation of the table, you can check the sequence name with the format of [tablename]_[columnname]_[seq] as well as the definition.

 \ds
                            List of relations
 Schema  |                 Name                 |   Type   |  Owner
---------+-------------------------------------+---------+-------
  public | claim02_claim_id_seq                 | sequence | user1

 \d+ claim02_claim_id_seq
                Sequence "public.claim02_claim_id_seq"
  Type   | Start | Minimum |  Maximum   | Increment | Cycles?  
| Cache
---------+------+---------+-----------+-----------+--------- 
+-------
 integer |     1 |       1 | 2147483647 |         1 | no       
|     1
Sequence for identity column: public.claim02.claim_id

alter [table] alter [column] restart with[number] -- specific  If the column is the identity column
or
SELECT  setval('sequence_name)',[number], TRUE);

Sequence cache

ALTER SEQUENCE claim09_id_seq1 CACHE 20;