📔
Databases
  • Table of contents
  • SQL
    • Getting Started
      • PgAdmin Tool
    • Data Types
      • Arrays
      • Date/Time/Stamps
      • JSON
      • Internal Functions
      • Sequences
      • User Defined Data Types
    • Database
      • Schema
    • Table
      • Aggregation
      • Usefull Functions
      • Combining Tables
      • Constraints
      • Common Table Expression
      • GROUP BY and HAVING
      • OPERATORS
      • ORDER BY and DISTINCT
      • Views
      • WHERE Clause
    • Order of SQL Execution
    • Advance Table
      • Internals
      • Managing Tables
      • Partitioning Tables
      • Pivotal or Crosstab Tables
    • Joins
      • Cross & Natural Joins
      • Full, Multiple & Self Joins
      • Inner Join
      • Left and Right JOIN
    • Functions
      • Cursors
      • PL/pgSQL
      • Stored Procedures
      • Triggers
        • More on Triggers
    • Indexing
      • Custom Indexes
      • GIN Index
      • Indexes
      • SQL
      • Unique Index
    • Summarization
      • SubQueries
      • Window
  • MongoDB
    • Mongo Administration
    • MongoDB Aggregation
    • MQL
  • Redis
  • Cassandra
    • CQL
    • Data Modelling
      • Advance Data Modelling
    • Cassandra Administration
    • Cassandra Features
Powered by GitBook
On this page
  • Sequences
  • List all sequence
  • Alpha-Numeric Sequence

Was this helpful?

  1. SQL
  2. Data Types

Sequences

Sequences

  • Specify datatype ( SMALLINT | INT | BIGINT )

  • Default is BIGINT

List all sequence

SELECT relname AS seq_name 
    FROM pg_class WHERE relkind = 'S';
CREATE SEQUENCE IF NOT EXISTS test_sequence AS bigint;

SELECT NEXTVAL('test_sequence');

 nextval 
---------
       1


SELECT CURRVAL('test_sequence');

 currval 
---------
       1

SELECT SETVAL('test_sequence',3);

 setval 
--------
      3


-- set this value after the nextval is called, 
-- check using the currval cmd
SELECT SETVAL('test_sequence',300,false);

-- CHECKING CURRENT VALUE 
SELECT CURRVAL('test_sequence');
 currval 
---------
       3


ALTER SEQUENCE test_sequence RESTART WITH 100;

SELECT NEXTVAL('test_sequence');
 nextval 
---------
     100

CREATE SEQUENCE IF NOT EXISTS test_seq3
INCREMENT 50
MINVALUE 100
MAXVALUE 1000
START WITH 150;

SELECT nextval('test_seq3');

  nextval 
---------
     150

CREATE SEQUENCE IF NOT EXISTS seq_des
INCREMENT -1
MINVALUE 1
MAXVALUE 999
START 99
NO CYCLE | CYCLE ;

SELECT nextval('seq_des');

 nextval 
---------
      99

-- DROP SEQUENCE

DROP SEQUENCE IF EXISTS seq_des;

CREATE TABLE IF NOT EXISTS table_seq (
    id INT primary key ,
    name VARCHAR(10)
);

CREATE sequence IF NOT EXISTS table_seq_id_seq
start with 1 owned BY table_seq.id;

ALTER TABLE table_seq
ALTER COLUMN id SET DEFAULT nextval('table_seq_id_seq')

Alpha-Numeric Sequence

CREATE sequence table_text_seq;

CREATE TABLE contacts (
    id text NOT null default ('ID' || nextval('table_text_seq')),
    name VARCHAR(150) NOT null
);

INSERT INTO  contacts (name) VALUES ('uday 1'),('uday 2'),('uday 3');

SELECT * FROM contacts;

 id  |  name  
-----+--------
 ID1 | uday 1
 ID2 | uday 2
 ID3 | uday 3
PreviousInternal FunctionsNextUser Defined Data Types

Last updated 3 years ago

Was this helpful?