path: root/src/test/regress/expected/sequence.out

---
--- test creation of SERIAL column
---
CREATE TABLE serialTest (f1 text, f2 serial);
INSERT INTO serialTest VALUES ('foo');
INSERT INTO serialTest VALUES ('bar');
INSERT INTO serialTest VALUES ('force', 100);
INSERT INTO serialTest VALUES ('wrong', NULL);
ERROR:  null value in column "f2" violates not-null constraint
DETAIL:  Failing row contains (wrong, null).
SELECT * FROM serialTest;
  f1   | f2  
-------+-----
 foo   |   1
 bar   |   2
 force | 100
(3 rows)

-- test smallserial / bigserial
CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,
  f5 bigserial, f6 serial8);
INSERT INTO serialTest2 (f1)
  VALUES ('test_defaults');
INSERT INTO serialTest2 (f1, f2, f3, f4, f5, f6)
  VALUES ('test_max_vals', 2147483647, 32767, 32767, 9223372036854775807,
          9223372036854775807),
         ('test_min_vals', -2147483648, -32768, -32768, -9223372036854775808,
          -9223372036854775808);
-- All these INSERTs should fail:
INSERT INTO serialTest2 (f1, f3)
  VALUES ('bogus', -32769);
ERROR:  smallint out of range
INSERT INTO serialTest2 (f1, f4)
  VALUES ('bogus', -32769);
ERROR:  smallint out of range
INSERT INTO serialTest2 (f1, f3)
  VALUES ('bogus', 32768);
ERROR:  smallint out of range
INSERT INTO serialTest2 (f1, f4)
  VALUES ('bogus', 32768);
ERROR:  smallint out of range
INSERT INTO serialTest2 (f1, f5)
  VALUES ('bogus', -9223372036854775809);
ERROR:  bigint out of range
INSERT INTO serialTest2 (f1, f6)
  VALUES ('bogus', -9223372036854775809);
ERROR:  bigint out of range
INSERT INTO serialTest2 (f1, f5)
  VALUES ('bogus', 9223372036854775808);
ERROR:  bigint out of range
INSERT INTO serialTest2 (f1, f6)
  VALUES ('bogus', 9223372036854775808);
ERROR:  bigint out of range
SELECT * FROM serialTest2 ORDER BY f2 ASC;
      f1       |     f2      |   f3   |   f4   |          f5          |          f6          
---------------+-------------+--------+--------+----------------------+----------------------
 test_min_vals | -2147483648 | -32768 | -32768 | -9223372036854775808 | -9223372036854775808
 test_defaults |           1 |      1 |      1 |                    1 |                    1
 test_max_vals |  2147483647 |  32767 |  32767 |  9223372036854775807 |  9223372036854775807
(3 rows)

SELECT nextval('serialTest2_f2_seq');
 nextval 
---------
       2
(1 row)

SELECT nextval('serialTest2_f3_seq');
 nextval 
---------
       2
(1 row)

SELECT nextval('serialTest2_f4_seq');
 nextval 
---------
       2
(1 row)

SELECT nextval('serialTest2_f5_seq');
 nextval 
---------
       2
(1 row)

SELECT nextval('serialTest2_f6_seq');
 nextval 
---------
       2
(1 row)

-- basic sequence operations using both text and oid references
CREATE SEQUENCE sequence_test;
CREATE SEQUENCE IF NOT EXISTS sequence_test;
NOTICE:  relation "sequence_test" already exists, skipping
SELECT nextval('sequence_test'::text);
 nextval 
---------
       1
(1 row)

SELECT nextval('sequence_test'::regclass);
 nextval 
---------
       2
(1 row)

SELECT currval('sequence_test'::text);
 currval 
---------
       2
(1 row)

SELECT currval('sequence_test'::regclass);
 currval 
---------
       2
(1 row)

SELECT setval('sequence_test'::text, 32);
 setval 
--------
     32
(1 row)

SELECT nextval('sequence_test'::regclass);
 nextval 
---------
      33
(1 row)

SELECT setval('sequence_test'::text, 99, false);
 setval 
--------
     99
(1 row)

SELECT nextval('sequence_test'::regclass);
 nextval 
---------
      99
(1 row)

SELECT setval('sequence_test'::regclass, 32);
 setval 
--------
     32
(1 row)

SELECT nextval('sequence_test'::text);
 nextval 
---------
      33
(1 row)

SELECT setval('sequence_test'::regclass, 99, false);
 setval 
--------
     99
(1 row)

SELECT nextval('sequence_test'::text);
 nextval 
---------
      99
(1 row)

DISCARD SEQUENCES;
SELECT currval('sequence_test'::regclass);
ERROR:  currval of sequence "sequence_test" is not yet defined in this session
DROP SEQUENCE sequence_test;
-- renaming sequences
CREATE SEQUENCE foo_seq;
ALTER TABLE foo_seq RENAME TO foo_seq_new;
SELECT * FROM foo_seq_new;
 last_value | log_cnt | is_called 
------------+---------+-----------
          1 |       0 | f
(1 row)

SELECT nextval('foo_seq_new');
 nextval 
---------
       1
(1 row)

SELECT nextval('foo_seq_new');
 nextval 
---------
       2
(1 row)

SELECT * FROM foo_seq_new;
 last_value | log_cnt | is_called 
------------+---------+-----------
          2 |      31 | t
(1 row)

DROP SEQUENCE foo_seq_new;
-- renaming serial sequences
ALTER TABLE serialtest_f2_seq RENAME TO serialtest_f2_foo;
INSERT INTO serialTest VALUES ('more');
SELECT * FROM serialTest;
  f1   | f2  
-------+-----
 foo   |   1
 bar   |   2
 force | 100
 more  |   3
(4 rows)

--
-- Check dependencies of serial and ordinary sequences
--
CREATE TEMP SEQUENCE myseq2;
CREATE TEMP SEQUENCE myseq3;
CREATE TEMP TABLE t1 (
  f1 serial,
  f2 int DEFAULT nextval('myseq2'),
  f3 int DEFAULT nextval('myseq3'::text)
);
-- Both drops should fail, but with different error messages:
DROP SEQUENCE t1_f1_seq;
ERROR:  cannot drop sequence t1_f1_seq because other objects depend on it
DETAIL:  default for table t1 column f1 depends on sequence t1_f1_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
DROP SEQUENCE myseq2;
ERROR:  cannot drop sequence myseq2 because other objects depend on it
DETAIL:  default for table t1 column f2 depends on sequence myseq2
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- This however will work:
DROP SEQUENCE myseq3;
DROP TABLE t1;
-- Fails because no longer existent:
DROP SEQUENCE t1_f1_seq;
ERROR:  sequence "t1_f1_seq" does not exist
-- Now OK:
DROP SEQUENCE myseq2;
--
-- Alter sequence
--
ALTER SEQUENCE IF EXISTS sequence_test2 RESTART WITH 24
	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
NOTICE:  relation "sequence_test2" does not exist, skipping
CREATE SEQUENCE sequence_test2 START WITH 32;
SELECT nextval('sequence_test2');
 nextval 
---------
      32
(1 row)

ALTER SEQUENCE sequence_test2 RESTART WITH 24
	 INCREMENT BY 4 MAXVALUE 36 MINVALUE 5 CYCLE;
SELECT nextval('sequence_test2');
 nextval 
---------
      24
(1 row)

SELECT nextval('sequence_test2');
 nextval 
---------
      28
(1 row)

SELECT nextval('sequence_test2');
 nextval 
---------
      32
(1 row)

SELECT nextval('sequence_test2');
 nextval 
---------
      36
(1 row)

SELECT nextval('sequence_test2');
 nextval 
---------
       5
(1 row)

ALTER SEQUENCE sequence_test2 RESTART;
SELECT nextval('sequence_test2');
 nextval 
---------
      32
(1 row)

SELECT nextval('sequence_test2');
 nextval 
---------
      36
(1 row)

SELECT nextval('sequence_test2');
 nextval 
---------
       5
(1 row)

CREATE SEQUENCE sequence_test3;  -- not read from, to test is_called
-- Information schema
SELECT * FROM information_schema.sequences WHERE sequence_name IN
  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
  ORDER BY sequence_name ASC;
 sequence_catalog | sequence_schema |   sequence_name    | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value |    maximum_value    | increment | cycle_option 
------------------+-----------------+--------------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------------+-----------+--------------
 regression       | public          | sequence_test2     | bigint    |                64 |                       2 |             0 | 32          | 5             | 36                  | 4         | YES
 regression       | public          | sequence_test3     | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
 regression       | public          | serialtest2_f2_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
 regression       | public          | serialtest2_f3_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
 regression       | public          | serialtest2_f4_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
 regression       | public          | serialtest2_f5_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
 regression       | public          | serialtest2_f6_seq | bigint    |                64 |                       2 |             0 | 1           | 1             | 9223372036854775807 | 1         | NO
(7 rows)

SELECT schemaname, sequencename, start_value, min_value, max_value, increment_by, cycle, cache_size, last_value
FROM pg_sequences
WHERE sequencename IN
  ('sequence_test2', 'sequence_test3', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
  ORDER BY sequencename ASC;
 schemaname |    sequencename    | start_value | min_value |      max_value      | increment_by | cycle | cache_size | last_value 
------------+--------------------+-------------+-----------+---------------------+--------------+-------+------------+------------
 public     | sequence_test2     |          32 |         5 |                  36 |            4 | t     |          1 |          5
 public     | sequence_test3     |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |           
 public     | serialtest2_f2_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
 public     | serialtest2_f3_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
 public     | serialtest2_f4_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
 public     | serialtest2_f5_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
 public     | serialtest2_f6_seq |           1 |         1 | 9223372036854775807 |            1 | f     |          1 |          2
(7 rows)

-- Test comments
COMMENT ON SEQUENCE asdf IS 'won''t work';
ERROR:  relation "asdf" does not exist
COMMENT ON SEQUENCE sequence_test2 IS 'will work';
COMMENT ON SEQUENCE sequence_test2 IS NULL;
-- Test lastval()
CREATE SEQUENCE seq;
SELECT nextval('seq');
 nextval 
---------
       1
(1 row)

SELECT lastval();
 lastval 
---------
       1
(1 row)

SELECT setval('seq', 99);
 setval 
--------
     99
(1 row)

SELECT lastval();
 lastval 
---------
      99
(1 row)

DISCARD SEQUENCES;
SELECT lastval();
ERROR:  lastval is not yet defined in this session
CREATE SEQUENCE seq2;
SELECT nextval('seq2');
 nextval 
---------
       1
(1 row)

SELECT lastval();
 lastval 
---------
       1
(1 row)

DROP SEQUENCE seq2;
-- should fail
SELECT lastval();
ERROR:  lastval is not yet defined in this session
CREATE USER regress_seq_user;
-- privileges tests
-- nextval
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT SELECT ON seq3 TO regress_seq_user;
SELECT nextval('seq3');
ERROR:  permission denied for sequence seq3
ROLLBACK;
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT UPDATE ON seq3 TO regress_seq_user;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

ROLLBACK;
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT USAGE ON seq3 TO regress_seq_user;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

ROLLBACK;
-- currval
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT SELECT ON seq3 TO regress_seq_user;
SELECT currval('seq3');
 currval 
---------
       1
(1 row)

ROLLBACK;
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT UPDATE ON seq3 TO regress_seq_user;
SELECT currval('seq3');
ERROR:  permission denied for sequence seq3
ROLLBACK;
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT USAGE ON seq3 TO regress_seq_user;
SELECT currval('seq3');
 currval 
---------
       1
(1 row)

ROLLBACK;
-- lastval
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT SELECT ON seq3 TO regress_seq_user;
SELECT lastval();
 lastval 
---------
       1
(1 row)

ROLLBACK;
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT UPDATE ON seq3 TO regress_seq_user;
SELECT lastval();
ERROR:  permission denied for sequence seq3
ROLLBACK;
BEGIN;
SET LOCAL SESSION AUTHORIZATION regress_seq_user;
CREATE SEQUENCE seq3;
SELECT nextval('seq3');
 nextval 
---------
       1
(1 row)

REVOKE ALL ON seq3 FROM regress_seq_user;
GRANT USAGE ON seq3 TO regress_seq_user;
SELECT lastval();
 lastval 
---------
       1
(1 row)

ROLLBACK;
-- Sequences should get wiped out as well:
DROP TABLE serialTest, serialTest2;
-- Make sure sequences are gone:
SELECT * FROM information_schema.sequences WHERE sequence_name IN
  ('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',
   'serialtest2_f4_seq', 'serialtest2_f5_seq', 'serialtest2_f6_seq')
  ORDER BY sequence_name ASC;
 sequence_catalog | sequence_schema | sequence_name  | data_type | numeric_precision | numeric_precision_radix | numeric_scale | start_value | minimum_value | maximum_value | increment | cycle_option 
------------------+-----------------+----------------+-----------+-------------------+-------------------------+---------------+-------------+---------------+---------------+-----------+--------------
 regression       | public          | sequence_test2 | bigint    |                64 |                       2 |             0 | 32          | 5             | 36            | 4         | YES
(1 row)

DROP USER regress_seq_user;
DROP SEQUENCE seq;
-- cache tests
CREATE SEQUENCE test_seq1 CACHE 10;
SELECT nextval('test_seq1');
 nextval 
---------
       1
(1 row)

SELECT nextval('test_seq1');
 nextval 
---------
       2
(1 row)

SELECT nextval('test_seq1');
 nextval 
---------
       3
(1 row)

DROP SEQUENCE test_seq1;