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, 494 views

Next post: 19. An introduction to the Object-Relational Mapper
Previous post: 17. SQL functions and summaries