In the statement below, the datatype of full_name is changed from char(25) to varchar(25). We again do this by using an ALTER TABLE statement, but this time in conjunction with ALTER COLUMN to target a specific column for change. We've already seen how to change a column's name, but there may also be situations where you need to change the column's data type. Remember when we created our table, there were two mandatory pieces of information that we needed to supply about each column: name and data type. We can now use \d all_users to see that the column name has been changed. The syntax is very similar to that for renaming a table, except that in between the RENAME and TO clauses, we need to use the COLUMN clause, passing it the name of the column we want to change. For example, we have been storing full names in the username column, and we may decide to rename it to full_name. \d all_usersĪs well as renaming an entire table, you can also use the RENAME clause to rename a specific column within the table. If we use \d all_users instead, we can see the description of what used to be called the users table all that's changed is the name of the table. table) called users since it has been renamed. PostgreSQL is unable to find a relation (i.e. If we now try to describe our users table however, we get the following response. This response is pretty generic other than telling us our statement was executed without error, it doesn't really tell us what's changed. The ALTER TABLE in the output is the response that we receive. On the first line above we run our SQL statement. Let's rename our users table to all_users. We then need to specify what we want to rename the table to using the TO clause followed by the new name. A table can be renamed using the RENAME clause. One example of a change we might want to make to a table is to rename the table itself. "users_id_key" UNIQUE CONSTRAINT, btree (id) Id | integer | not null default nextval('users_id_seq'::regclass) Remember that within the psql console we can use the meta-command \d followed by a table name to describe that table. In a moment we'll work through some specific examples of using the ALTER TABLE statement, but first let's remind ourselves of what our table currently looks like. The basic format of an ALTER TABLE statement is: ALTER TABLE table_to_change An ALTER TABLE statement is part of DDL, and is for altering a table schema only we'll look at updating data in a table later in this book. Alter Table SyntaxĮxisting tables can be altered with an ALTER TABLE statement. Before jumping in to alter your table, take extra time to think about how the change will affect your existing data. Deleting a column means all data in that column will be lost forever. Adding an extra column to a table will add an extra column to all existing rows, just as it would in a spreadsheet. It is important to consider how schema changes will affect the data in a table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |