force | 100

(3 rows)

-- test smallserial / bigserial

CREATE TABLE serialTest2 (f1 text, f2 serial, f3 smallserial, f4 serial2,

f5 bigserial, f6 serial8);

NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f2_seq" for serial column "serialtest2.f2"

NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f3_seq" for serial column "serialtest2.f3"

NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f4_seq" for serial column "serialtest2.f4"

NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f5_seq" for serial column "serialtest2.f5"

NOTICE: CREATE TABLE will create implicit sequence "serialtest2_f6_seq" for serial column "serialtest2.f6"

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;

SELECT nextval('sequence_test'::text);

(1 row)

-- Information schema

SELECT * FROM information_schema.sequences WHERE sequence_name IN ('sequence_test2');

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)

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

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

(6 rows)

-- Test comments

COMMENT ON SEQUENCE asdf IS 'won''t work';

SELECT lastval();

ERROR: permission denied for sequence seq3

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 seq_user;

DROP SEQUENCE seq;