18. Altering the database
Here are more commands we can use. Let’s say we create a database we didn’t want (spelled wrongly, etc)
CREATE DATABASE ooops;
We can get rid of it using:
DROP DATABASE ooops;
We can get rid of tables we don’t need any more:
DROP TABLE genes_with_ec;
Let’s say we want to look at the gene_id and source columns from genes, but restrict to the first 2:
example=# SELECT gene_id, source FROM genes LIMIT 2; gene_id | source ---------+-------- Gene1 | ena Gene2 | ena (2 rows)
Let’s say we’ve decided the source values are not informative for our data model. So now we can use the ALTER command to adjust our tables.
ALTER TABLE genes DROP COLUMN source CASCADE;
Note we added CASCADE at the end. The source column is referenced in the materialized view. So we want this change to cascade into the materialized view. You’ll need to add CASCADE to make sure the materialized view is also updated appropriately.
We can confirm that source has been removed:
example=# SELECT * from genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk ----+---------+------------+--------+-------+------+---------------+------- 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 (5 rows)
Similarly we can add a column with ADD COLUMN in a ALTER command:
example=# ALTER TABLE genes ADD COLUMN sense CHAR; ALTER TABLE example=# SELECT * from genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk | sense ----+---------+------------+--------+-------+------+---------------+-------+------- 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 | 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 | 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 | 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 | 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 | (5 rows)
We can fill in some data here using UPDATE:
example=# UPDATE genes SET sense='+' WHERE gene_id='Gene1'; UPDATE 1 example=# SELECT * FROM genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk | sense ----+---------+------------+--------+-------+------+---------------+-------+------- 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 | 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 | 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 | 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 | 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 | + (5 rows)
You can see that this has listed the columns and sorted them using the new sense column. The most recent changes to the table are listed at the bottom.
We could manipulate multiple specific rows:
example=# UPDATE genes SET sense='-' WHERE pk=2 OR pk=3; UPDATE 2 example=# SELECT * FROM genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk | sense ----+---------+------------+--------+-------+------+---------------+-------+------- 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 | 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 | 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 | + 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 | - 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 | - (5 rows)
or
example=# UPDATE genes SET sense='U' WHERE entity='Plasmid'; UPDATE 2 example=# SELECT * FROM genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk | sense ----+---------+------------+--------+-------+------+---------------+-------+------- 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 | + 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 | - 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 | - 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 | U 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 | U (5 rows)
We can add a column and give it a default value too:
example=# ALTER TABLE genes ADD COLUMN start_codon CHAR DEFAULT('M'); ALTER TABLE example=# SELECT * FROM genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk | sense | start_codon ----+---------+------------+--------+-------+------+---------------+-------+-------+------------- 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 | + | M 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 | - | M 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 | - | M 4 | Gene4 | Plasmid | ena | 1001 | 1111 | 1 | 2 | U | M 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 | U | M (5 rows)
We can also update multiple values given a condition:
example=# UPDATE genes SET start=558, stop=696 WHERE pk=4; UPDATE 1 example=# SELECT * FROM genes; pk | gene_id | entity | source | start | stop | sequencing_pk | ec_pk | sense | start_codon ----+---------+------------+--------+-------+------+---------------+-------+-------+------------- 1 | Gene1 | Chromosome | ena | 190 | 255 | 1 | 1 | + | M 2 | Gene2 | Chromosome | ena | 375 | 566 | 1 | 2 | - | M 3 | Gene3 | Chromosome | ena | 780 | 980 | 1 | 1 | - | M 5 | Gene5 | Plasmid | ena | 786 | 888 | 1 | 2 | U | M 4 | Gene4 | Plasmid | ena | 558 | 696 | 1 | 2 | U | M (5 rows)
Wednesday 27 October 2021, 849 views
Next post: 19. An introduction to the Object-Relational Mapper Previous post: 17. SQL functions and summaries
Advanced Web Development index
- 38. Writing API tests
- 37. Testing in Django
- 36. Class-based views in the Django REST framework
- 35. Building a RESTful web service in Django
- 34. Introduction to CRUD, REST and APIs
- 33. Refactoring with generic views in Django
- 32. Django validators
- 31. Django forms (2) – using the ModelForm class
- 30. Django forms (1)
- 29. JavaScript basics
- 28. Adding CSS to the template
- 27. Django templating
- 26. Deleting and updating records
- 25. Joins, filters and chaining commands
- 24. Using the ORM in views.py
- 23. Adding to the database by writing a script
- 22. Adding to the database with Django Admin
- 21. Migrations
- 20. ORM – work through example
- 19. An introduction to the Object-Relational Mapper
- 18. Altering the database
- 17. SQL functions and summaries
- 16. SQL Query performance
- 15. Queries and table joins in SQL
- 14. Inserts and queries in SQL
- 13. Good practice in relational database design
- 12. Limitations to database modelling
- 11. Building a database using SQL
- 10. Introduction to PostgreSQL
- 9. How to start writing a new application in Django
- 8. Building a lightweight project
- 7. Django URLs
- 6. Django templates
- 5. Django models
- 4. Django views
- 3. Creating a new hello app
- 2. Creating a new virtual environment
- 1. Setting up Django
Leave a Reply