Note that even though the new table is a separate table, it keeps using the old sequence. INSERT INTO test_old2 (payload) VALUES ('e') RETURNING * If you use the CREATE TABLE / LIKE functionality to copy the structure of a table, serial columns pose a problem: CREATE TABLE test_old2 (LIKE test_old INCLUDING ALL) You can also turn an existing integer column into an identity column with one command: ALTER TABLE test_newĪLTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY Copying table structures But just in case, you get a nice error message: => ALTER TABLE test_new ALTER COLUMN id DROP DEFAULT ĮRROR: column "id" of relation "test_new" is an identity column You cannot accidentally make a mistake and drop a default, because there is none. SET DEFAULT commands shown earlier.ĭropping the identity property of an existing column is easy: ALTER TABLE test_new ALTER COLUMN id DROP IDENTITY You will have to manually assemble the CREATE SEQUENCE and ALTER TABLE. If you want to take an existing integer column and turn it into a serial column, there is no single command to do that. It will drop the default but leave the sequence in place. If you instead drop the default value like ALTER TABLE test_old ALTER COLUMN id DROP DEFAULT To drop serialness, you can drop the sequence (again, after ascertaining the name) with the CASCADE option, which cascades to remove the default value of the associated column: DROP SEQUENCE test_old_id_seq CASCADE But dropping serialness from an existing column or adding it to an existing column is not straightforward. You can specify serial as a column type when creating a table or when adding a column. Since serial is not a real type, it can only be used in certain circumstances. With an identity column, you don’t need to know the name of the sequence: ALTER TABLE test_new ALTER COLUMN id RESTART WITH 1000 Schema management You also need to know the name of the sequence if you want to make some changes to the sequence: ALTER SEQUENCE test_old_id_seq RESTART WITH 1000 ![]() Here, of course, it appears in the error message, and it is easy to guess, but sometimes a slightly different name is chosen, and then your deployment scripts will fail. If you have deployment scripts, this is annoying and problematic, because the name of the sequence is automatically generated. You can fix the error by also running GRANT USAGE ON SEQUENCE test_old_id_seq INSERT INTO test_new (payload) VALUES ('d') INSERT INTO test_old (payload) VALUES ('d') ĮRROR: permission denied for sequence test_old_id_seq One common problem is that permissions for the sequence created by a serial column need to be managed separately: CREATE USER foo The new way creates the sequence as a proper internal dependency of the table, so that various weird implementation details of the serial pseudotype are not exposed. The OWNED BY in the last command is an attempt to remember something about the serialness, but it is still insufficient in some cases. It expands this at parse time into something like CREATE SEQUENCE test_old_id_seq ĪLTER COLUMN id SET DEFAULT nextval('test_old_id_seq') ĪLTER SEQUENCE test_old_id_seq OWNED BY test_old.id PermissionsĪ general problem with the old way is that the system doesn’t actually remember that the user typed serial. ![]() So now you can move code around between, for example, PostgreSQL, DB2, and Oracle without any change (in this area). Some have lately been adopting the standard SQL syntax, however. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. The new syntax conforms to the SQL standard. INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING * ĭo pretty much the same thing, except that the new way is more verbose. Id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING * Depesz already wrote a blog post about it and showed that it works pretty much like serial columns: CREATE TABLE test_old ( For PostgreSQL 10, I have worked on a feature called “identity columns”.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |