---

--- test creation of SERIAL column

---

CREATE TABLEserialTest (f1 text, f2 serial);

INSERT INTOserialTest VALUES ('foo');

INSERT INTOserialTest VALUES ('bar');

INSERT INTOserialTest VALUES ('force', 100);

INSERT INTOserialTest VALUES ('wrong', NULL);

CREATE TABLEserialTest1 (f1 text, f2 serial);

INSERT INTOserialTest1 VALUES ('foo');

INSERT INTOserialTest1 VALUES ('bar');

INSERT INTOserialTest1 VALUES ('force', 100);

INSERT INTOserialTest1 VALUES ('wrong', NULL);

ERROR: null value in column "f2" violates not-null constraint

DETAIL: Failing row contains (wrong, null).

SELECT * FROMserialTest;

SELECT * FROMserialTest1;

f1 | f2

-------+-----

foo | 1

DROP SEQUENCE foo_seq_new;

-- renaming serial sequences

ALTER TABLEserialtest_f2_seq RENAME TOserialtest_f2_foo;

INSERT INTOserialTest VALUES ('more');

SELECT * FROMserialTest;

ALTER TABLEserialtest1_f2_seq RENAME TOserialtest1_f2_foo;

INSERT INTOserialTest1 VALUES ('more');

SELECT * FROMserialTest1;

f1 | f2

-------+-----

foo | 1

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 | sequence_test4 | bigint | 64 | 2 | 0 | -1 | -9223372036854775808 | -1 | -1 | NO

regression | public | serialtest1_f2_foo | 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

regression | public | serialtest_f2_foo | bigint | 64 | 2 | 0 | 1 | 1 | 9223372036854775807 | 1 | NO

(9 rows)

SELECT 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 | sequence_test4 | -1 | -9223372036854775808 | -1 | -1 | f | 1 | -1

public | serialtest1_f2_foo | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 3

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

public | serialtest_f2_foo | 1 | 1 | 9223372036854775807 | 1 | f | 1 | 3

(9 rows)

SELECT * FROM pg_sequence_parameters('sequence_test4'::regclass);

ERROR: must be owner of relation sequence_test2

ROLLBACK;

-- Sequences should get wiped out as well:

DROP TABLEserialTest, serialTest2;

DROP TABLEserialTest1, serialTest2;

-- Make sure sequences are gone:

SELECT * FROM information_schema.sequences WHERE sequence_name IN

('sequence_test2', 'serialtest2_f2_seq', 'serialtest2_f3_seq',

CREATETABLEserialTest (f1text, f2serial);

CREATETABLEserialTest1 (f1text, f2serial);

INSERT INTOserialTestVALUES ('foo');

INSERT INTOserialTestVALUES ('bar');

INSERT INTOserialTestVALUES ('force',100);

INSERT INTOserialTestVALUES ('wrong',NULL);

INSERT INTOserialTest1VALUES ('foo');

INSERT INTOserialTest1VALUES ('bar');

INSERT INTOserialTest1VALUES ('force',100);

INSERT INTOserialTest1VALUES ('wrong',NULL);

SELECT*FROMserialTest;

SELECT*FROMserialTest1;

CREATETABLEserialTest2 (f1text, f2serial, f3 smallserial, f4 serial2,

DROPSEQUENCE foo_seq_new;

ALTERTABLEserialtest_f2_seq RENAME TOserialtest_f2_foo;

INSERT INTOserialTestVALUES ('more');

SELECT*FROMserialTest;

ALTERTABLEserialtest1_f2_seq RENAME TOserialtest1_f2_foo;

INSERT INTOserialTest1VALUES ('more');

SELECT*FROMserialTest1;

ROLLBACK;

DROPTABLEserialTest, serialTest2;

DROPTABLEserialTest1, serialTest2;

SELECT*FROMinformation_schema.sequencesWHERE sequence_nameIN