NEBC Database Course PostgreSQL Cheat-sheet v2 December 2008

Full text

(1)NEBC Database Course PostgreSQL Cheat-sheet v2 December 2008 For each example, replace mytable and mycol with your own table and column names. The new element in each example is highlighted in bold. SELECT Command. What it does. SELECT * from mytable. get all entries. SELECT DISTINCT * FROM mytable. get all unique entries. SELECT mycol1, mycol2 FROM table. get only columns mycol1 and mycol2. SELECT mycol1 AS foo FROM mytable. get mycol1, but call it “foo” in the results. SELECT * FROM mytable ORDER BY mycol1 ASC, mycol2 DESC. sort by mycol1 – if 2 rows have the same value here then sort these by mycol2 in reverse order. SELECT * FROM mytable WHERE mycol1 = mycol2. a simple search condition based on equality. SELECT * FROM mytable WHERE mycol1 = 5 OR. find all rows where mycol1 is 5, or where mycol1 is 6 but also mycol2 contains 'snake', 'Snake', 'sNAke' etc.. ( mycol1 = 6 AND lower(mycol2) = 'snake' ) SELECT * FROM table WHERE mycol1 IS NULL. searching for NULL values with 'IS NULL'. SELECT * FROM mytable WHERE mycol1 IN (3, 4, 5). specify that col1 must be 3, 4 or 5. SELECT * FROM mytable1 WHERE mycol1 IN. obtain the list of matches from col2 in table2. (SELECT mycol2 FROM mytable2) SELECT * FROM my table1 t1 WHERE EXISTS (SELECT * FROM mytable2 t2 WHERE t2.mycol2 = t1.mycol1) SELECT t1.*, t2.* FROM mytable1 t1 INNER JOIN mytable2 t2 ON (t1.pk_col = t2.fk_col) SELECT t1.*, t2.* FROM mytable1 t1 LEFT OUTER JOIN mytable2 t2 ON (t1.pk_col = t2.fk_col) SELECT mycol1 as foo, mycol2 AS bar FROM mytable1 UNION SELECT mycol1 as foo, mycol3 AS bar FROM mytable2. same as the previous, but using the EXISTS clause. Choose whichever you find easiest! inner join, where tables 1 and 2 have a one-to-many relationship an outer join where columns from table 1 will be displayed even if there is no matching entry in table 2 combine two SELECTS into a single result set – note the SELECT keyword appears twice, but the ORDER BY affects the whole result. Duplicate rows are not shown. Use 'UNION ALL' to show all duplicate rows. ORDER BY foo SELECT mycol1 as foo, mycol2 AS bar FROM mytable1 INTERSECT. Shows all rows common to two SELECT statements. 'INTERSECT ALL' will show all duplicate rows. SELECT mycol1 as foo, mycol3 AS bar FROM mytable2 ORDER BY foo SELECT mycol1, sum(mycol2) FROM mytable1 WHERE mycol3 = 1. group entries in table1 by col1, showing each value of col1 as well as the sum of col2 for those rows. GROUP BY mycol1 HAVING count(*) < 3. disregard rows where col3 is not 1, and show only results with 1 or 2 occurrences of that value of col1 in the table. SELECT * INTO mynewtable FROM myoldtable. Create newtable from oldtable.

(2) DELETE, INSERT, UPDATE Command. What it does. DELETE FROM mytable. delete EVERYTHING in a table. DELETE FROM mytable WHERE mycol1 != 5. delete by some condition. INSERT INTO mytable (mycol1, mycol2) VALUES ('foo', 'bar'). insert with specific values. INSERT INTO mytable2 SELECT mycol1, mycol2 FROM table1. insert values based on a SELECT statement. UPDATE mytable. update with WHERE condition. SET mycol1 = 'foo', mycol2 = mycol2 * mycol2 WHERE mycol3 IS NOT NULL. OPERATORS AND FUNCTIONS Command. What it does. SELECT (mycol1 / mycol2) *100 AS percentage FROM mytable. perform some arithmetic on the columns (other mathematical operators include + and -). SELECT sqrt(mycol1) FROM mytable. Select the square root of mycol1. SELECT trunc(mycol1, 3) FROM mytable. Truncate the value of mycol3 after 3 decimal points. SELECT abs(mycol), round(mycol,2), ln(mycol1), sin(mycol), tan(mycol). Absolute value of mycol,round mycol to 2 decimal places, natural log mycol,sine of mycol,tangent of mycol. FROM mytable. See documentation for further mathematical functions SELECT mycol1 || ' and ' || mycol2 FROM mytable. Concatenate mycol1 to mycol2 with the word 'and'. SELECT substr(mycol1, 2, 3) FROM mytable. Select substring of mycol1 starting at character 2, 3 characters in length. SELECT upper(mycol1) FROM mytable. Display mycol1 in upper case. SELECT lower(mycol1) FROM mytable. Display mycol1 in lower case. SELECT * FROM mytable WHERE mycol1 = 'badger'. Select rows where mycol1 exactly matches 'badger'. SELECT * FROM mytable WHERE mycol1 != 'badger'. Select rows where mycol1 does not match 'badger'. SELECT * FROM mytable WHERE mycol1 LIKE '%adg%'. Select rows where mycol1 contains the string 'adg' (note use of '%' wildcard character). SELECT sum(mycol1), avg(mycol1) FROM mytable. return both sum and average of mycol1. SELECT count(*) FROM mytable. Count the number of rows in specified table. SELECT mycol1, sum(mycol2) FROM mytable. group entries in mytable by mycol1, showing each value of mycol1 as well as the sum of mycol2 for those rows. WHERE mycol3 = 1 GROUP BY mycol1 HAVING count(*) < 3. disregard rows where mycol3 is not 1, and show only results with 1 or 2 occurrences of that value of mycol1. SELECT current_timestamp, now(). Two ways to display the current date and time. SELECT to_char(mydatecol,'DD/MM/YYYY') FROM mytable. Select mydatecol in the format DD/MM/YYYY(eg.21/04/2008). SELECT to_date('200425thJune','YYYYDDthmonth'). Convert the string '200425thJune' to a date. SELECT mydatecol::text FROM mytable1. Typecast date values explicitly to data type varchar. SELECT coalesce(mycol1::text, 'No value') FROM mytable1. Display 'No value' if value in mydf -h col1 is null. SELECT CASE WHEN mycol1 = true THEN 'badger' ELSE 'mushroom' END FROM mytable1. If boolean value in mycol1 is true print 'badger' otherwise print 'mushroom'.

(3) Data Types Data Type. Description. integer,int,int4. Whole number/integer. float. Floating point number. numeric(p,s). Exact numeric type with total digits 'p' and digits after decimal point 's'. date. Calender date. timestamp. Date and time. varchar(n), character varying(n). Variable length character string of max length 'n'. char(n). Fixed length character string of length 'n'. text. Variable length character of unlimited length. boolean, bool. A single true or false value (supported values: true/false,'t'/'f','true'/'false','y'/'n','yes'/'no','1','0').

(4)

(5) . . .

(6)

(7)

(8) ! ∀∀

(9) #

(10)

(11) ∃

(12) % &

(13)

(14) & % ∋ # ( % & )) ∗ +

(15) ∀ &)) (

(16)

(17) + ,

(18) % − ∃

(19) , − .( /

(20) % ! 010

(21) 0.0&.

(22)

(23) % # ∃ % +

(24) # ∃

(25) 2

(26) ! . # 2

(27) &

(28)

(29) ∗

(30)

(31) ∀ #

(32) 0

(33) 0 !

(34) 0∋&&&(0+

(35) & 3( 45 !%

(36) ! +

(37)

(38) !#!

(39) + %

(40) % 67 +

(41) 60% 0 1

(42) !

(43) !∋ +

(44)

(45) !

(46) % 67(& 8

(47) !

(48) ! ,−, 2 −+ 9 8

(49) +

(50) %

(51) !%

(52) 9 :(

(53) !∀ #

(54)

(55) % ! ,

(56) % −67;&8 ∀∀ 9 7( +

(57)

(58) !#

(59) 44# <5=

(60) % 8

(61) 44

(62) +

(63) 9 >(?

(64) !

(65) # 9. ?

(66)

(67) +

(68) ,

(69) ∋)(&&&− ∀

(70) ∗ ∃+

(71) !

(72)

(73) ∀

(74) ! +

(75)

(76) & ≅( 4 ∀

(77) +%

(78) .;∀ & Α( %

(79)

(80) !0

(81) & Β(< +

(82)

(83) !#Χ!

(84)

(85) 9 + ∆∆00∆∆ <5=

(86) <5=

(87) % 8?5=4∋

(88) % <5=%

(89) (. ∀∀#. #

(90) % ∀

(91) #! ! ∀

(92) &.

(93)

(94) . . M Day 1 : Advanced Querying.

(95)

(96)

(97)

(98)

(99) . !.

(100) ∀

(101) # ∀

(102) ∃ %∀

(103) .

(104) ∃ ∃

(105) &

(106) ∀

(107) ∀

(108) ∋. # ∃. (

(109)

(110) ∀ #)∀∃

(111)

(112) ∋ ∃

(113) ! ∗∃

(114) +

(115)

(116) ∀ #)∀∃

(117)

(118) ∋ ∃∃

(119) %∋ ∃

(120) &∋ ∃

(121) ,

(122)

(123) % ∃∋ −

(124) ∋% ∃ . /

(125) %%

(126) % # ∃

(127) ∃ ∀

(128) 0 ∃

(129) &

(130) 1&0

(131) −2034−&5%

(132) ∃

(133) 6 %∃ % 7

(134)

(135)

(136) ∀∃

(137) 0

(138)

(139) ∃∃ ∀∃

(140) % #! %

(141) %

(142) # ∀∃

(143) ∃8 9

(144)

(145) ∀∃

(146) 0

(147)

(148) % # ∃

(149) :

(150)

(151) ∃

(152) #

(153) # ∃∋

(154) ;

(155)

(156). <

(157) #∀∃

(158) .

(159) )=

(160) 6 >3−/. ?! ∃∀∃

(161) ∀ ∃

(162) ∃%2∋∃ ∋ ∀

(163)

(164)

(165) # ≅ . ;! ∀

(166)

(167) 6

(168) .

(169)

(170) . .

(171) . . .

(172)

(173) !

(174) ∀ .

(175) #∃% #! ! ! %!.

(176)

(177)

(178) & ∋

(179)

(180) ∀

(181) ∃ ( ! ).

(182)

(183)

(184)

(185) ( ∗ )∀

(186)

(187) ∋

(188)

(189) ∗ )∀

(190)

(191)

(192)

(193) ∗. +

(194) #,+ +

(195)

(196)

(197)

(198) (

(199)

(200) ∀

(201) ∋

(202) !+

(203)

(204)

(205) ∀

(206)

(207)

(208)

(209) ∀ ∃

(210)

(211) ! −

(212)

(213) ∀ ∀ .. .

(214) !

(215)

(216)

(217)

(218) ∀

(219)

(220) . ∃

(221)

(222) ∃

(223) ∀ ∃

(224) ∃ ∃ +

(225) ,

(226)

(227)

(228)

(229)

(230)

(231)

(232) ∀∋

(233) ∋ !

(234) (

(235)

(236) ∀ ∋

(237)

(238)

(239) (

(240)

(241) ! ( ∃

(242) ∃

(243) ∃

(244) ∀ .

(245) ∃

(246)

(247)

(248)

(249)

(250)

(251)

(252) ∀

(253)

(254) / !0∀ (

(255) . ∋

(256)

(257)

(258) ∋ ∋ ∃

(259) ∀

(260)

(261). ! ∋ ∃

(262) (

(263)

(264) ∃ ∋

(265) ∋ ! 1

(266) .

(267) 2

(268)

(269) (

(270)

(271) ∀ !. +

(272) ,3 4( ∀

(273) 55. ! !

(274) ! (5

(275)

(276) ( . ∀

(277) 55!6. .

(278) ( ∀

(279) ∃∀

(280) ∀

(281)

(282) ∀

(283)

(284) ∃

(285)

(286)

(287) ∃

(288)

(289)

(290) ∃

(291)

(292) ∃

(293) ∃ ∃ ∃ ∃

(294) ∀

(295) !&

(296).

(297)

(298)

(299)

(300)

(301)

(302)

(303) ! 0

(304).

(305)

(306)

(307) ! +

(308)

(309)

(310) ∀

(311) (

(312)

(313) ∀

(314)

(315) ! 7

(316)

(317). !

(318)

(319) !0

(320) ∀

(321) ∃

(322)

(323)

(324)

(325)

(326) 8.

(327) ∀

(328)

(329) 4

(330) ∀.

(331) 6!

(332)

(333)

(334) ∀ ∀

(335)

(336)

(337) ∃ ∋

(338) ∀

(339) ∀

(340)

(341)

(342) !.

(343) +

(344) %,

(345) . ∃ 9

(346)

(347)

(348) ! , ∋

(349) ∋ ! 1

(350)

(351) ∀∋ :

(352)

(353)

(354)

(355) ∋

(356) ∋

(357) !

(358)

(359) (

(360) . ∃

(361) ∀

(362)

(363) !

(364) ∋

(365)

(366) !+

(367)

(368) ∋

(369) ∃

(370)

(371)

(372) ! 3

(373)

(374)

(375)

(376)

(377)

(378) ! ∋

(379)

(380) ∃

(381)

(382) ∀

(383) !

(384) ∃ . ∋

(385) ∋ ! (

(386)

(387) ∀

(388) ∀

(389) ∋ ! ;

(390) ∋ ∃ ∀

(391)

(392) ∃

(393) ∀

(394) !

(395) ∀

(396) ∀ !

(397)

(398)

(399) (

(400) ∀ <

(401) ∃

(402) ∀

(403)

(404) ∋ ∋

(405) ! (

(406) ∋

(407)

(408)

(409)

(410) (

(411) ! .

(412)

(413)

(414)

(415) ! .

(416)

(417) ( ( . .

(418)

(419). . =∀

(420) . >?>>>>. ##>>>>. #>>>> 3

(421) =∀

(422) . ≅ . >?#Α>>. ###Α>>. ##Α>>. ΒΑ. ) ;

(423) :. #>>>>. #>>>. #%>>>. Β#. Χ . ##>>>. #>>>. #Α>>>. Β#/. ≅

(424) . ##%Α>>. #%Α>>. #Α%Α>>. Β>. ((. #>>>>. #Α>>>>. #/>>>>. ΒΑ. . #%>>>>. #∆>>>>. #Ε>>>>. ΒΑ. . ##>>>>. #>>>>. #/>>>> 3

(425) . ((. #>>>>. #%>>>>. #Ε>>>>. Β#>. ≅

(426) . ##Α>>. #Α#Α>>. #?#Α>>. Β#Α. Χ . #%>>>. #Α%>>>. #?%>>>. Β#Ε. ) ;

(427) :. #%>>>. #∆>>>. >>>>. Β. ≅ . #Α%Α>>. #/%Α>>. #%Α>>. Β>. =∀

(428) . #∆>>>>. #Ε>>>>. >>>>. ΒΑ. .

(429)

(430) . .

(431) .

(432)

(433)

(434) ! !

(435)

(436) ∀#

(437)

(438)

(439) ∃

(440)

(441) %∀

(442) . &∋. (

(443) ∀#

(444)

(445)

(446) . ∀

(447) ) . . ∀ # . . ∀ ∀

(448) ∗ . . (

(449) . . (

(450) ∀ + .

(451)

(452) !∀∀ # ∃

(453)

(454)

(455)

(456)

(457)

(458)

(459) !

(460) ∀

(461)

(462) # # %&&& ! ∃ !! ! ∃ ∋! ( ∃

(463) ∀∃ % ! &∃#∋∋

(464)

(465) ((((#∃#

(466) &#

(467) &

(468)

(469) )

(470)

(471) &

(472)

(473) & )& ! ∗+ ( ! , ∃

(474) ∗ ∃∗∀

(475) ++∀

(476)

(477)

(478)

(479)

(480) +

(481)

(482) +

(483)

(484) ∗+∗++∀

(485)

(486) + ∀

(487)

(488) +

(489)

(490) +∗+∗+

(491)

(492) &

(493)

(494)

(495)

(496) ∗+∗& !

(497) ∗ #

(498) ,# −& !∀∀ ∗+ ( ( ! ∃∋!( ! #∗. !& &#( #/ (#∃

(499) ∗∀∃

(500) !

(501) ∗ #− ./ 0

(502) #

(503) !

(504) #

(505). + 0#

(506)

(507) ∗

(508) ∋

(509) ++∀

(510)

(511)

(512)

(513)

(514) +

(515)

(516) +

(517)

(518) ∗+∗++∀

(519)

(520) + ∀

(521)

(522) +

(523)

(524) +∗+∗+

(525)

(526) &

(527)

(528)

(529)

(530) ∗+∗& 0∀∀∀ !∀ ∀ ∃1 #∋2# #

(531) 3∋1# 4

(532) ∗∀

(533) ∀

(534)

(535)

(536)

(537)

(538) &

(539)

(540) &

(541)

(542) ∗&&∗

(543)

(544)

(545) &

(546)

(547)

(548)

(549) &∗&∗ !

(550) &∗&

(551)

(552)

(553)

(554)

(555)

(556)

(557)

(558)

(559)

(560)

(561)

(562)

(563)

(564)

(565)

(566)

(567)

(568)

(569)

(570)

(571)

(572)

(573)

(574)

(575)

(576)

(577)

(578)

(579)

(580)

(581)

(582)

(583)

(584)

(585)

(586)

(587)

(588)

(589)

(590)

(591) &

(592)

(593)

(594) &

(595)

(596)

(597)

(598) ∗∗&

(599)

(600)

(601)

(602)

(603)

(604)

(605)

(606)

(607)

(608)

(609)

(610)

(611)

(612)

(613)

(614)

(615)

(616)

(617)

(618)

(619)

(620)

(621)

(622)

(623)

(624)

(625)

(626)

(627)

(628)

(629)

(630)

(631)

(632)

(633)

(634)

(635) !

(636) ∀

(637) 1

(638) #02#.

(639) ∗∀.

(640)

(641) ∀

(642)

(643)

(644)

(645)

(646) &

(647)

(648) &

(649)

(650) ∗&&∗

(651)

(652)

(653) &

(654)

(655)

(656)

(657) &∗&∗ !

(658) 3

(659)

(660)

(661)

(662)

(663)

(664)

(665)

(666)

(667)

(668)

(669)

(670)

(671)

(672)

(673)

(674)

(675)

(676)

(677)

(678)

(679)

(680)

(681)

(682)

(683)

(684)

(685)

(686)

(687)

(688)

(689)

(690)

(691) &

(692)

(693)

(694) &

(695)

(696)

(697)

(698) ∗∗&

(699)

(700)

(701)

(702)

(703)

(704)

(705)

(706)

(707)

(708)

(709)

(710)

(711)

(712)

(713)

(714)

(715)

(716)

(717)

(718)

(719)

(720)

(721)

(722)

(723)

(724)

(725)

(726)

(727) !

(728) ∀

(729) 1

(730) #02#

(731)

(732)

(733)

(734)

(735)

(736)

(737)

(738)

(739)

(740)

(741)

(742)

(743)

(744)

(745)

(746)

(747)

(748)

(749)

(750)

(751)

(752)

(753)

(754)

(755)

(756)

(757)

(758) ∋

(759) &∗&. 5 !∀∀ 1 ∗ ∀∀ ∀ ∃ ∀∀ 6

(760) ∗∀

(761) ∀

(762)

(763)

(764)

(765)

(766) &

(767)

(768) &

(769)

(770) ∗&&∗

(771)

(772)

(773)

(774)

(775)

(776)

(777) &

(778)

(779)

(780)

(781) &∗&∗ !

(782) ∗ 456 )

(783)

(784) ∗∀

(785) +& ∀

(786)

(787) +

(788)

(789)

(790) &

(791)

(792) &

(793)

(794) +∗&&∗

(795)

(796)

(797)

(798)

(799)

(800)

(801) &

(802)

(803)

(804)

(805) &∗&∗ !

(806) +∗ 456 7 !∀∀ 1 ∗ ∀ !

(807) ∗∀

(808) ∀

(809)

(810)

(811)

(812)

(813) &

(814)

(815) &

(816)

(817) ∗&&∗

(818)

(819)

(820)

(821)

(822)

(823)

(824) &

(825)

(826)

(827)

(828) &∗&∗ !

(829) ∗ 456 .

(830) ∗∀

(831) +& ∀

(832)

(833) +

(834)

(835)

(836) &

(837)

(838) &

(839)

(840) +∗&&∗

(841)

(842)

(843)

(844)

(845)

(846)

(847) &

(848)

(849)

(850)

(851) &∗&∗ !

(852) +∗ 456. 8 ( !9 ∀ :! ∃ +;< 4 =>/∀

(853) 7+&78 ∀

(854)

(855) ∃

(856)

(857) 99∃ 7+&:

(858)

(859) 8 ∀

(860)

(861) )

(862)

(863)

(864)

(865) ∀

(866)

(867) ∃

(868) )

(869)

(870) +&

(871)

(872) +& ∀

(873)

(874) +&∃

(875) )+

(876)

(877)

(878)

(879) + ∀

(880)

(881).

(882) + ∃

(883) )8 ∀

(884)

(885) 8 ∀

(886)

(887)

(888)

(889) 8 ∀

(890) +8∀0

(891)

(892)

(893) ++

(894)

(895)

(896)

(897)

(898) 8 ∀

(899)

(900) 9

(901)

(902)

(903) /

(904) )

(905)

(906) ;∗

(907)

(908)

(909)

(910)

(911) +8∀

(912)

(913) 9

(914) ∀

(915) 80

(916) 9

(917)

(918) 8 ∀

(919)

(920) ;0

(921)

(922)

(923) 8;0∗

(924)

(925)

(926) ∃

(927) 99

(928)

(929) ∃

(930)

(931) 7

(932) 99

(933)

(934) +/

(935)

(936) #< #

(937)

(938) ∃

(939) 5

(940)

(941) ∃

(942) : ∀

(943)

(944) 99

(945)

(946) +

(947)

(948)

(949)

(950)

(951)

(952)

(953)

(954)

(955)

(956)

(957)

(958)

(959)

(960)

(961)

(962)

(963)

(964)

(965)

(966)

(967)

(968)

(969)

(970)

(971)

(972)

(973)

(974)

(975)

(976)

(977)

(978)

(979)

(980)

(981)

(982)

(983)

(984)

(985)

(986)

(987)

(988)

(989)

(990)

(991)

(992)

(993)

(994)

(995)

(996)

(997)

(998)

(999)

(1000)

(1001)

(1002)

(1003)

(1004)

(1005)

(1006)

(1007)

(1008)

(1009)

(1010)

(1011)

(1012)

(1013)

(1014)

(1015)

(1016)

(1017)

(1018)

(1019)

(1020)

(1021)

(1022)

(1023)

(1024)

(1025)

(1026)

(1027)

(1028)

(1029)

(1030)

(1031)

(1032)

(1033)

(1034)

(1035)

(1036)

(1037)

(1038)

(1039)

(1040)

(1041)

(1042)

(1043)

(1044)

(1045)

(1046)

(1047)

(1048)

(1049)

(1050)

(1051) )

(1052)

(1053)

(1054)

(1055)

(1056)

(1057)

(1058)

(1059)

(1060)

(1061)

(1062)

(1063)

(1064)

(1065)

(1066)

(1067)

(1068)

(1069)

(1070)

(1071)

(1072)

(1073)

(1074)

(1075)

(1076)

(1077)

(1078)

(1079)

(1080)

(1081)

(1082)

(1083)

(1084)

(1085)

(1086)

(1087)

(1088)

(1089)

No results found