|
| 1 | +drop table computer_terms; |
| 2 | +ERROR: Relation 'computer_terms' does not exist |
| 3 | +create table computer_terms(term text, category text, comments char(16)); |
| 4 | +create index computer_terms_index1 on computer_terms using btree(term); |
| 5 | +create index computer_terms_index2 on computer_terms using btree(category); |
| 6 | +insert into computer_terms values('computer display', 'X-A01-Y', 'a comment 1'); |
| 7 | +insert into computer_terms values('computer graphics', 'T-B01-Y', 'a comment 2'); |
| 8 | +insert into computer_terms values('computer programmer', 'S-Z01-Y', 'a comment 3'); |
| 9 | +vacuum computer_terms; |
| 10 | +select * from computer_terms; |
| 11 | + term | category | comments |
| 12 | +---------------------+----------+------------------ |
| 13 | + computer display | X-A01-Y | a comment 1 |
| 14 | + computer graphics | T-B01-Y | a comment 2 |
| 15 | + computer programmer | S-Z01-Y | a comment 3 |
| 16 | +(3 rows) |
| 17 | + |
| 18 | +select * from computer_terms where category = 'X-A01-Y'; |
| 19 | + term | category | comments |
| 20 | +------------------+----------+------------------ |
| 21 | + computer display | X-A01-Y | a comment 1 |
| 22 | +(1 row) |
| 23 | + |
| 24 | +select * from computer_terms where category ~* 'x-a01-y'; |
| 25 | + term | category | comments |
| 26 | +------------------+----------+------------------ |
| 27 | + computer display | X-A01-Y | a comment 1 |
| 28 | +(1 row) |
| 29 | + |
| 30 | +select * from computer_terms where category like '_-A01-_'; |
| 31 | + term | category | comments |
| 32 | +------------------+----------+------------------ |
| 33 | + computer display | X-A01-Y | a comment 1 |
| 34 | +(1 row) |
| 35 | + |
| 36 | +select * from computer_terms where category like '_-A%'; |
| 37 | + term | category | comments |
| 38 | +------------------+----------+------------------ |
| 39 | + computer display | X-A01-Y | a comment 1 |
| 40 | +(1 row) |
| 41 | + |
| 42 | +select * from computer_terms where term ~ 'computer [dg]'; |
| 43 | + term | category | comments |
| 44 | +-------------------+----------+------------------ |
| 45 | + computer display | X-A01-Y | a comment 1 |
| 46 | + computer graphics | T-B01-Y | a comment 2 |
| 47 | +(2 rows) |
| 48 | + |
| 49 | +select * from computer_terms where term ~* 'computer [DG]'; |
| 50 | + term | category | comments |
| 51 | +-------------------+----------+------------------ |
| 52 | + computer display | X-A01-Y | a comment 1 |
| 53 | + computer graphics | T-B01-Y | a comment 2 |
| 54 | +(2 rows) |
| 55 | + |
| 56 | +select *,character_length(term) from computer_terms; |
| 57 | + term | category | comments | length |
| 58 | +---------------------+----------+------------------+-------- |
| 59 | + computer display | X-A01-Y | a comment 1 | 16 |
| 60 | + computer graphics | T-B01-Y | a comment 2 | 17 |
| 61 | + computer programmer | S-Z01-Y | a comment 3 | 19 |
| 62 | +(3 rows) |
| 63 | + |
| 64 | +select *,octet_length(term) from computer_terms; |
| 65 | + term | category | comments | octet_length |
| 66 | +---------------------+----------+------------------+-------------- |
| 67 | + computer display | X-A01-Y | a comment 1 | 16 |
| 68 | + computer graphics | T-B01-Y | a comment 2 | 17 |
| 69 | + computer programmer | S-Z01-Y | a comment 3 | 19 |
| 70 | +(3 rows) |
| 71 | + |
| 72 | +select *,position('s' in term) from computer_terms; |
| 73 | + term | category | comments | strpos |
| 74 | +---------------------+----------+------------------+-------- |
| 75 | + computer display | X-A01-Y | a comment 1 | 12 |
| 76 | + computer graphics | T-B01-Y | a comment 2 | 17 |
| 77 | + computer programmer | S-Z01-Y | a comment 3 | 0 |
| 78 | +(3 rows) |
| 79 | + |
| 80 | +select *,substring(term from 10 for 4) from computer_terms; |
| 81 | + term | category | comments | substr |
| 82 | +---------------------+----------+------------------+-------- |
| 83 | + computer display | X-A01-Y | a comment 1 | disp |
| 84 | + computer graphics | T-B01-Y | a comment 2 | grap |
| 85 | + computer programmer | S-Z01-Y | a comment 3 | prog |
| 86 | +(3 rows) |
| 87 | + |