Server [localhost]: Database [postgres]: baking Port [5432]: Username [postgres]: kkuzmin Active code page: 1252 psql (13.2) Type "help" for help. baking=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+----------------------------+----------------------------+----------------------- baking | kkuzmin | UTF8 | English_United States.1252 | English_United States.1252 | kkuzmin | kkuzmin | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) baking=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | favorites | table | kkuzmin public | results | table | kkuzmin public | showstoppers | table | kkuzmin public | signatures | table | kkuzmin public | technicals | table | kkuzmin (7 rows) baking=> \d bakers Table "public.bakers" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+----------+--------- baker | character varying(10) | | not null | fullname | character varying(100) | | | age | integer | | | occupation | character varying(100) | | | hometown | character varying(100) | | | Indexes: "bakers_pkey" PRIMARY KEY, btree (baker) Referenced by: TABLE "favorites" CONSTRAINT "favorites_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "results" CONSTRAINT "results_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "showstoppers" CONSTRAINT "showstoppers_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "signatures" CONSTRAINT "signatures_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) TABLE "technicals" CONSTRAINT "technicals_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) baking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Jon | Jon Jenkins | 47 | Blood courier | Newport Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Manon | Manon Lagrève | 26 | Software project manager | London Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (12 rows) baking=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+----------------------------+----------------------------+----------------------- baking | kkuzmin | UTF8 | English_United States.1252 | English_United States.1252 | kkuzmin | kkuzmin | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows) baking=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | favorites | table | kkuzmin public | results | table | kkuzmin public | showstoppers | table | kkuzmin public | signatures | table | kkuzmin public | technicals | table | kkuzmin (7 rows) baking=> \d episodes Table "public.episodes" Column | Type | Collation | Nullable | Default -------------+------------------------+-----------+----------+--------- id | integer | | not null | title | character varying(100) | | | firstaired | date | | | viewers7day | double precision | | | signature | character varying(100) | | | technical | character varying(100) | | | showstopper | character varying(100) | | | Indexes: "episodes_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "favorites" CONSTRAINT "favorites_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "results" CONSTRAINT "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "showstoppers" CONSTRAINT "showstoppers_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "signatures" CONSTRAINT "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) TABLE "technicals" CONSTRAINT "technicals_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> \d signature Did not find any relation named "signature". baking=> \d signatures Table "public.signatures" Column | Type | Collation | Nullable | Default -----------+------------------------+-----------+----------+--------- episodeid | integer | | not null | baker | character varying(100) | | not null | make | character varying(100) | | | Indexes: "signatures_pkey" PRIMARY KEY, btree (episodeid, baker) Foreign-key constraints: "signatures_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) "signatures_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | favorites | table | kkuzmin public | results | table | kkuzmin public | showstoppers | table | kkuzmin public | signatures | table | kkuzmin public | technicals | table | kkuzmin (7 rows) baking=> \d results Table "public.results" Column | Type | Collation | Nullable | Default -----------+------------------------+-----------+----------+--------- episodeid | integer | | not null | baker | character varying(100) | | not null | result | character varying(20) | | | Indexes: "results_pkey" PRIMARY KEY, btree (episodeid, baker) Foreign-key constraints: "results_baker_fkey" FOREIGN KEY (baker) REFERENCES bakers(baker) "results_episodeid_fkey" FOREIGN KEY (episodeid) REFERENCES episodes(id) baking=> SELECT * FROM results; episodeid | baker | result -----------+---------+------------ 1 | Manon | star baker 1 | Imelda | eliminated 2 | Rahul | star baker 2 | Luke | eliminated 3 | Rahul | star baker 3 | Antony | eliminated 4 | Dan | star baker 5 | Terry | eliminated 5 | Kim-Joy | star baker 5 | Karen | eliminated 6 | Briony | star baker 6 | Dan | eliminated 7 | Kim-Joy | star baker 7 | Jon | eliminated 8 | Ruby | star baker 8 | Manon | eliminated 9 | Ruby | star baker 9 | Briony | eliminated (18 rows) baking=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | favorites | table | kkuzmin public | results | table | kkuzmin public | showstoppers | table | kkuzmin public | signatures | table | kkuzmin public | technicals | table | kkuzmin (7 rows) baking=> SELECT * FROM favorites; episodeid | baker -----------+--------- 1 | Briony 2 | Jon 2 | Dan 3 | Dan 4 | Rahul 4 | Jon 5 | Rahul 6 | Rahul 6 | Ruby 7 | Rahul 8 | Briony 9 | Kim-Joy (12 rows) baking=> SELECT * FROM makers; ERROR: relation "makers" does not exist LINE 1: SELECT * FROM makers; ^ baking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Jon | Jon Jenkins | 47 | Blood courier | Newport Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Manon | Manon Lagrève | 26 | Software project manager | London Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (12 rows) baking=> SELECT baking-> age, fullname baking-> FROM baking-> bakers; age | fullname -----+--------------------- 30 | Antony Amourdoux 33 | Briony Williams 36 | Dan Beasley-Harling 33 | Imelda McCarron 47 | Jon Jenkins 60 | Karen Wright 27 | Kim-Joy Hewlett 30 | Luke Thompson 26 | Manon Lagrève 30 | Rahul Mandal 29 | Ruby Bhogal 56 | Terry Hartill (12 rows) baking=> SELECT baking-> age baking-> , fullname baking-> FROM baking-> bakers; age | fullname -----+--------------------- 30 | Antony Amourdoux 33 | Briony Williams 36 | Dan Beasley-Harling 33 | Imelda McCarron 47 | Jon Jenkins 60 | Karen Wright 27 | Kim-Joy Hewlett 30 | Luke Thompson 26 | Manon Lagrève 30 | Rahul Mandal 29 | Ruby Bhogal 56 | Terry Hartill (12 rows) baking=> \s history is not supported by this installation baking=> \edit age | fullname | hometown -----+---------------------+--------------- 30 | Antony Amourdoux | London 33 | Briony Williams | Bristol 36 | Dan Beasley-Harling | London 33 | Imelda McCarron | County Tyrone 47 | Jon Jenkins | Newport 60 | Karen Wright | Wakefield 27 | Kim-Joy Hewlett | Leeds 30 | Luke Thompson | Sheffield 26 | Manon Lagrève | London 30 | Rahul Mandal | Rotherham 29 | Ruby Bhogal | London 56 | Terry Hartill | West Midlands (12 rows) baking=> SELECT baking-> age baking-> , fullname AS name baking-> FROM baking-> bakers; age | name -----+--------------------- 30 | Antony Amourdoux 33 | Briony Williams 36 | Dan Beasley-Harling 33 | Imelda McCarron 47 | Jon Jenkins 60 | Karen Wright 27 | Kim-Joy Hewlett 30 | Luke Thompson 26 | Manon Lagrève 30 | Rahul Mandal 29 | Ruby Bhogal 56 | Terry Hartill (12 rows) baking=> SELECT baking-> age baking-> FROM baking-> bakers; age ----- 30 33 36 33 47 60 27 30 26 30 29 56 (12 rows) baking=> SELECT DISTINCT baking-> age baking-> FROM baking-> bakers; age ----- 29 36 60 26 30 47 33 27 56 (9 rows) baking=> SELECT DISTINCT baking-> age baking-> , upper(fullname) baking-> , 'season 4' baking-> , fullname || ' ' || hometown baking-> FROM baking-> bakers; age | upper | ?column? | ?column? -----+---------------------+----------+------------------------------- 29 | RUBY BHOGAL | season 4 | Ruby Bhogal London 30 | LUKE THOMPSON | season 4 | Luke Thompson Sheffield 60 | KAREN WRIGHT | season 4 | Karen Wright Wakefield 56 | TERRY HARTILL | season 4 | Terry Hartill West Midlands 33 | BRIONY WILLIAMS | season 4 | Briony Williams Bristol 36 | DAN BEASLEY-HARLING | season 4 | Dan Beasley-Harling London 33 | IMELDA MCCARRON | season 4 | Imelda McCarron County Tyrone 30 | RAHUL MANDAL | season 4 | Rahul Mandal Rotherham 30 | ANTONY AMOURDOUX | season 4 | Antony Amourdoux London 26 | MANON LAGRèVE | season 4 | Manon Lagrève London 47 | JON JENKINS | season 4 | Jon Jenkins Newport 27 | KIM-JOY HEWLETT | season 4 | Kim-Joy Hewlett Leeds (12 rows) baking=> SELECT DISTINCT baking-> age baking-> , upper(fullname) baking-> , 'season 4' AS season baking-> , fullname || ' ' || age::char(4) || ' ' || hometown AS bakerinfo baking-> FROM baking-> bakers; age | fullnameofcontestant | season | bakerinfo | substring -----+----------------------+----------+----------------------------------+----------------- 30 | ANTONY AMOURDOUX | season 4 | Antony Amourdoux 30 London | Amourdoux 33 | BRIONY WILLIAMS | season 4 | Briony Williams 33 Bristol | Williams 36 | DAN BEASLEY-HARLING | season 4 | Dan Beasley-Harling 36 London | Beasley-Harling 33 | IMELDA MCCARRON | season 4 | Imelda McCarron 33 County Tyrone | McCarron 47 | JON JENKINS | season 4 | Jon Jenkins 47 Newport | Jenkins 60 | KAREN WRIGHT | season 4 | Karen Wright 60 Wakefield | Wright 27 | KIM-JOY HEWLETT | season 4 | Kim-Joy Hewlett 27 Leeds | Hewlett 30 | LUKE THOMPSON | season 4 | Luke Thompson 30 Sheffield | Thompson 26 | MANON LAGRèVE | season 4 | Manon Lagrève 26 London | Lagrève 30 | RAHUL MANDAL | season 4 | Rahul Mandal 30 Rotherham | Mandal 29 | RUBY BHOGAL | season 4 | Ruby Bhogal 29 London | Bhogal 56 | TERRY HARTILL | season 4 | Terry Hartill 56 West Midlands | Hartill (12 rows) baking=> SELECT DISTINCT baking-> age baking-> , upper(fullname) baking-> , 'season 4' AS season baking-> , fullname || ' ' || ' ' || hometown AS bakerinfo baking-> , substring(fullname from strpos(fullname, ' ')) baking-> FROM baking-> bakers; age | upper | season | bakerinfo | substring -----+---------------------+----------+--------------------------------+------------------ 60 | KAREN WRIGHT | season 4 | Karen Wright Wakefield | Wright 27 | KIM-JOY HEWLETT | season 4 | Kim-Joy Hewlett Leeds | Hewlett 30 | LUKE THOMPSON | season 4 | Luke Thompson Sheffield | Thompson 30 | RAHUL MANDAL | season 4 | Rahul Mandal Rotherham | Mandal 33 | BRIONY WILLIAMS | season 4 | Briony Williams Bristol | Williams 30 | ANTONY AMOURDOUX | season 4 | Antony Amourdoux London | Amourdoux 29 | RUBY BHOGAL | season 4 | Ruby Bhogal London | Bhogal 33 | IMELDA MCCARRON | season 4 | Imelda McCarron County Tyrone | McCarron 36 | DAN BEASLEY-HARLING | season 4 | Dan Beasley-Harling London | Beasley-Harling 56 | TERRY HARTILL | season 4 | Terry Hartill West Midlands | Hartill 47 | JON JENKINS | season 4 | Jon Jenkins Newport | Jenkins 26 | MANON LAGRèVE | season 4 | Manon Lagrève London | Lagrève (12 rows) baking=> SELECT baking-> fullname baking-> , age baking-> FROM baking-> bakers baking-> WHERE baking-> age <= 30 baking-> and length(fullname) > 15; fullname | age ------------------+----- Antony Amourdoux | 30 (1 row) baking=> SELECT * FROM bakers; baker | fullname | age | occupation | hometown ---------+---------------------+-----+-----------------------------------+--------------- Antony | Antony Amourdoux | 30 | Banker | London Briony | Briony Williams | 33 | Full-time parent | Bristol Dan | Dan Beasley-Harling | 36 | Full-time parent | London Imelda | Imelda McCarron | 33 | Countryside recreation officer | County Tyrone Jon | Jon Jenkins | 47 | Blood courier | Newport Karen | Karen Wright | 60 | In-store sampling assistant | Wakefield Kim-Joy | Kim-Joy Hewlett | 27 | Mental health specialist | Leeds Luke | Luke Thompson | 30 | Civil servant/house and techno DJ | Sheffield Manon | Manon Lagrève | 26 | Software project manager | London Rahul | Rahul Mandal | 30 | Research scientist | Rotherham Ruby | Ruby Bhogal | 29 | Project manager | London Terry | Terry Hartill | 56 | Retired air steward | West Midlands (12 rows) baking=> SELECT baking-> fullname baking-> , age baking-> FROM baking-> bakers baking-> WHERE baking-> hometown = 'London'; fullname | age ---------------------+----- Antony Amourdoux | 30 Dan Beasley-Harling | 36 Manon Lagrève | 26 Ruby Bhogal | 29 (4 rows) baking=> SELECT baking-> fullname baking-> , age baking-> , occupation baking-> FROM baking-> bakers baking-> WHERE baking-> occupation LIKE '%parent'; fullname | age | occupation ---------------------+-----+------------------ Briony Williams | 33 | Full-time parent Dan Beasley-Harling | 36 | Full-time parent (2 rows) baking=> SELECT baking-> fullname baking-> , age baking-> , occupation baking-> FROM baking-> bakers baking-> WHERE baking-> occupation LIKE '%parent%'; fullname | age | occupation ---------------------+-----+------------------ Briony Williams | 33 | Full-time parent Dan Beasley-Harling | 36 | Full-time parent (2 rows) baking=> SELECT baking-> fullname baking-> , age baking-> , occupation baking-> FROM baking-> bakers baking-> WHERE baking-> occupation LIKE '%par%ent%'; fullname | age | occupation ---------------------+-----+------------------ Briony Williams | 33 | Full-time parent Dan Beasley-Harling | 36 | Full-time parent (2 rows) baking=> SELECT baking-> fullname baking-> , age baking-> , occupation baking-> FROM baking-> bakers baking-> WHERE baking-> lower(occupation) LIKE '%porject%'; fullname | age | occupation ----------+-----+------------ (0 rows) baking=> SELECT baking-> fullname baking-> , age baking-> , occupation baking-> FROM baking-> bakers baking-> WHERE baking-> lower(occupation) LIKE '%project%'; fullname | age | occupation ----------------+-----+-------------------------- Manon Lagrève | 26 | Software project manager Ruby Bhogal | 29 | Project manager (2 rows) baking=> create table abc (val varchar(10), id int) ; CREATE TABLE baking=> \d List of relations Schema | Name | Type | Owner --------+--------------+-------+--------- public | abc | table | kkuzmin public | bakers | table | kkuzmin public | episodes | table | kkuzmin public | favorites | table | kkuzmin public | results | table | kkuzmin public | showstoppers | table | kkuzmin public | signatures | table | kkuzmin public | technicals | table | kkuzmin (8 rows) baking=> \d abc Table "public.abc" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- val | character varying(10) | | | id | integer | | | baking=> SELECT * FROM abc; val | id -----+---- (0 rows) baking=> insert into abc values('cat'); INSERT 0 1 baking=> SELECT * FROM abc; val | id -----+---- cat | (1 row) baking=> insert into abc values('dog'); INSERT 0 1 baking=> SELECT * FROM abc; val | id -----+---- cat | dog | (2 rows) baking=> insert into abc values(null); INSERT 0 1 baking=> SELECT * FROM abc; val | id -----+---- cat | dog | | (3 rows) baking=> insert into abc values(null, 5); INSERT 0 1 baking=> SELECT * FROM abc; val | id -----+---- cat | dog | | | 5 (4 rows) baking=> select * from abc where val like '%'; val | id -----+---- cat | dog | (2 rows) baking=> insert into abc values('', 6); INSERT 0 1 baking=> SELECT * FROM abc; val | id -----+---- cat | dog | | | 5 | 6 (5 rows) baking=> select * from abc where val like '%'; val | id -----+---- cat | dog | | 6 (3 rows) baking=> select * from abc where val like '_'; val | id -----+---- (0 rows) baking=> select * from abc where val like '___'; val | id -----+---- cat | dog | (2 rows) baking=> insert into abc values('NULL', 7); INSERT 0 1 baking=> SELECT * FROM abc; val | id ------+---- cat | dog | | | 5 | 6 NULL | 7 (6 rows) baking=> select * from abc where length(val)=0; val | id -----+---- | 6 (1 row) baking=> select * from abc where length(val)>=0; val | id ------+---- cat | dog | | 6 NULL | 7 (4 rows) baking=> select * from abc where val is NULL; val | id -----+---- | | 5 (2 rows) baking=> select * from abc where val is NULL or val like '%'; val | id ------+---- cat | dog | | | 5 | 6 NULL | 7 (6 rows) baking=> select * from abc where val is not NULL; val | id ------+---- cat | dog | | 6 NULL | 7 (4 rows) baking=> \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+----------------------------+----------------------------+----------------------- baking | kkuzmin | UTF8 | English_United States.1252 | English_United States.1252 | kkuzmin | kkuzmin | UTF8 | English_United States.1252 | English_United States.1252 | postgres | postgres | UTF8 | English_United States.1252 | English_United States.1252 | template0 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | English_United States.1252 | English_United States.1252 | =c/postgres + | | | | | postgres=CTc/postgres (5 rows)