1 00:00:01,130 --> 00:00:04,910 To see these three character types in action. 2 00:00:04,910 --> 00:00:06,950 We will code some script here. 3 00:00:06,950 --> 00:00:13,550 So firstly, we will create the table and let's name it character data types, right? 4 00:00:13,550 --> 00:00:22,430 So create table and name it MyCar or yeah, character data types. 5 00:00:22,700 --> 00:00:23,810 Character. 6 00:00:24,590 --> 00:00:26,240 Data archives. 7 00:00:26,240 --> 00:00:28,100 And here we will add character. 8 00:00:28,100 --> 00:00:32,300 I will explain all of this with column Character is going to be ten. 9 00:00:33,860 --> 00:00:36,950 And the character after that is Comma. 10 00:00:37,220 --> 00:00:39,140 So our character. 11 00:00:40,970 --> 00:00:49,220 Var character column is going to be var character type, also ten and we will also add text. 12 00:00:49,220 --> 00:00:54,050 So text column column is going to be text. 13 00:00:54,500 --> 00:01:03,770 Just a text here and we will not define size because as we told, as I as you learned in previous lecture, 14 00:01:03,770 --> 00:01:11,330 that variable length column of text is has unlimited length. 15 00:01:11,330 --> 00:01:15,680 So that's why we didn't wrote anything onto this. 16 00:01:16,040 --> 00:01:26,260 And here we will use after that we will insert here, insert into character, oops, insert into car 17 00:01:27,080 --> 00:01:34,880 card data types, types and values insert into values of. 18 00:01:35,590 --> 00:01:37,630 A, B, C, for example. 19 00:01:37,660 --> 00:01:38,350 That's it. 20 00:01:38,380 --> 00:01:39,040 A, B, C. 21 00:01:39,400 --> 00:01:42,490 And after that, we will also enter ABC again. 22 00:01:42,520 --> 00:01:44,830 ABC again and ABC again. 23 00:01:47,440 --> 00:01:52,960 And that's actually due in part to my pen ink here. 24 00:01:53,930 --> 00:02:00,800 And here we will also after that we will add a comma, and after that we will also add the DFG. 25 00:02:01,790 --> 00:02:04,170 The a f g def. 26 00:02:06,680 --> 00:02:07,820 We'll copy that. 27 00:02:09,730 --> 00:02:10,870 Yes, again. 28 00:02:12,320 --> 00:02:12,680 That's it. 29 00:02:13,840 --> 00:02:14,350 That's it. 30 00:02:14,350 --> 00:02:14,680 Here. 31 00:02:14,680 --> 00:02:16,360 And we will copy. 32 00:02:16,630 --> 00:02:17,470 Copy. 33 00:02:18,620 --> 00:02:21,800 Uh, character data types. 34 00:02:23,680 --> 00:02:26,940 To and we will set the directory here. 35 00:02:26,950 --> 00:02:34,090 In this case, we can also set our directory into the desktop I guess. 36 00:02:34,090 --> 00:02:36,160 Yeah, we can use the desktop here. 37 00:02:36,160 --> 00:02:36,790 We will. 38 00:02:38,570 --> 00:02:43,490 Instead of this, we can also go to this and users Oxley. 39 00:02:45,190 --> 00:02:48,850 And after that, we'll add desktop here. 40 00:02:49,360 --> 00:02:49,930 That's it. 41 00:02:49,930 --> 00:02:55,060 And copy this to our desktop and after that. 42 00:02:55,060 --> 00:02:57,970 So of course we need to write this in quotes. 43 00:02:58,920 --> 00:02:59,770 Like this. 44 00:03:01,710 --> 00:03:02,670 Because of the stick. 45 00:03:02,670 --> 00:03:11,040 And here we will add width format csv, csv either. 46 00:03:12,270 --> 00:03:14,640 And we will also add the limiter. 47 00:03:14,670 --> 00:03:16,650 The limiter is going to be this. 48 00:03:18,080 --> 00:03:18,360 Pipe. 49 00:03:18,890 --> 00:03:19,650 That's it. 50 00:03:19,670 --> 00:03:21,050 So here. 51 00:03:21,950 --> 00:03:25,730 We define three character columns in this. 52 00:03:26,580 --> 00:03:27,930 Yeah, actually, that's me. 53 00:03:29,310 --> 00:03:30,270 Leather here. 54 00:03:30,270 --> 00:03:30,990 Leather here. 55 00:03:30,990 --> 00:03:32,330 So, see more? 56 00:03:32,730 --> 00:03:33,100 Yes. 57 00:03:33,120 --> 00:03:33,900 Perfect. 58 00:03:34,060 --> 00:03:34,560 Right. 59 00:03:35,720 --> 00:03:39,030 And open the can also jinxx. 60 00:03:39,230 --> 00:03:39,890 Perfect. 61 00:03:39,980 --> 00:03:44,360 So here we define three character columns here. 62 00:03:47,510 --> 00:03:53,960 Um, and this we define the three character columns of different types and insert two rows of the same 63 00:03:53,960 --> 00:03:58,040 string into, into each year after that. 64 00:03:58,160 --> 00:03:59,060 That's it. 65 00:04:00,290 --> 00:04:06,770 And unlike the insert into statement you learned in previous lectures here, we are not specifying the 66 00:04:06,770 --> 00:04:07,850 names of the columns. 67 00:04:07,850 --> 00:04:13,670 So if the value statements match the number of the columns in the table, the database will assume you 68 00:04:13,670 --> 00:04:17,330 are inserting the values in order the column definitions here. 69 00:04:17,330 --> 00:04:23,660 So in this case the ABC first ABC goes to the character column, the second goes to VARCHAR and the 70 00:04:23,660 --> 00:04:26,030 third goes to text here. 71 00:04:26,030 --> 00:04:37,280 So next we use the PostgreSQL copy, keyword copy keyword to export the data to a text file named desk. 72 00:04:37,820 --> 00:04:49,190 We didn't actually use the task text file here, so my new lecture dot text file and here we will add. 73 00:04:49,280 --> 00:04:58,820 So we are exporting the data to a text file named my new lecture in a directory of desktop here. 74 00:04:59,060 --> 00:04:59,870 So. 75 00:05:00,830 --> 00:05:06,800 And you will need to replace your directory with the full path to the directory on your computer where 76 00:05:06,800 --> 00:05:08,660 you want to save that file. 77 00:05:08,660 --> 00:05:15,230 And the examples in this course, use the Windows formats, which is use backslash between the folders 78 00:05:15,230 --> 00:05:21,500 and file names and the path directory on the C drive. 79 00:05:21,500 --> 00:05:24,470 So if you are using Linux, you should write something like this. 80 00:05:24,470 --> 00:05:33,110 For example, Home Oxley desktop and my text file dot txt like this. 81 00:05:33,140 --> 00:05:38,600 In this case we are on Windows, so that's why we write the backslash and see this here. 82 00:05:39,020 --> 00:05:46,100 So Windows user must set permissions for the destination folder according here and now. 83 00:05:46,100 --> 00:05:47,690 Let's run this here. 84 00:05:48,460 --> 00:05:55,180 And as you can see here, we got an error because of the syntax at or near here. 85 00:05:55,360 --> 00:05:57,880 So that's because of the reduce. 86 00:05:57,910 --> 00:05:59,740 We need to use this. 87 00:06:01,220 --> 00:06:02,440 Parentheses instead of this. 88 00:06:03,760 --> 00:06:03,900 I. 89 00:06:04,600 --> 00:06:06,040 And we also have syntax errors. 90 00:06:06,070 --> 00:06:07,900 Of course, we we don't need that. 91 00:06:10,430 --> 00:06:15,130 Insert into character syntax error at once. 92 00:06:16,210 --> 00:06:18,020 So could not open the file. 93 00:06:18,040 --> 00:06:19,150 Permission denied. 94 00:06:19,180 --> 00:06:22,930 That's the error that I wanted to show you here. 95 00:06:22,930 --> 00:06:28,000 So now we're going to fix that error via our permission is denied now. 96 00:06:29,820 --> 00:06:33,220 And here, as you can see here, we got this error here. 97 00:06:33,240 --> 00:06:37,530 So in order to fix that, you will go to your the destination folder. 98 00:06:37,530 --> 00:06:40,920 In this case, it's a desktop, my new lecture. 99 00:06:40,920 --> 00:06:46,740 And here we will right click on that, click on properties and click on security. 100 00:06:47,100 --> 00:06:50,190 And here we will use edit. 101 00:06:52,440 --> 00:06:55,320 And here we will write everyone. 102 00:06:55,440 --> 00:06:57,120 Everyone click on. 103 00:06:57,120 --> 00:06:57,870 Okay. 104 00:06:57,960 --> 00:07:01,560 And here you will have this options here. 105 00:07:01,740 --> 00:07:06,210 So you will allow the modify and click on apply. 106 00:07:06,210 --> 00:07:10,050 In this case, this might ask some questions here. 107 00:07:10,260 --> 00:07:10,740 Just click on. 108 00:07:10,740 --> 00:07:12,750 Continue, continue. 109 00:07:13,110 --> 00:07:13,980 Continue. 110 00:07:14,400 --> 00:07:14,850 Continue. 111 00:07:14,850 --> 00:07:18,480 Again 4 or 5 times it might ask you. 112 00:07:27,280 --> 00:07:27,880 That's it. 113 00:07:28,760 --> 00:07:29,540 Here now. 114 00:07:29,540 --> 00:07:34,100 Our error will be fixed now and click on this. 115 00:07:35,210 --> 00:07:40,930 As you can see here, we got successfully returned from our query. 116 00:07:40,940 --> 00:07:44,780 So now we're going to go to as you can see, our file is here, right? 117 00:07:44,780 --> 00:07:45,920 So click on that. 118 00:07:45,920 --> 00:07:50,390 And as you can see, we are brought to that here. 119 00:07:50,390 --> 00:07:57,050 So in PostgreSQL here, I will also explain this. 120 00:07:57,810 --> 00:08:01,200 Let's actually open with the Visual Studio. 121 00:08:01,800 --> 00:08:03,630 Now Visual Visual Studio code. 122 00:08:05,580 --> 00:08:05,850 Here. 123 00:08:05,850 --> 00:08:10,080 We will right click on that open with Visual Studio code. 124 00:08:11,160 --> 00:08:11,630 That's it. 125 00:08:11,640 --> 00:08:22,290 So in PostgreSQL, the copy table from here, the copy table name copy card data types to. 126 00:08:23,580 --> 00:08:25,560 And copy from. 127 00:08:27,000 --> 00:08:30,580 Is the input function and the copy table name character. 128 00:08:31,110 --> 00:08:36,690 Two is the export function, so I will cover them in depth in next lectures. 129 00:08:36,690 --> 00:08:43,470 For now, all you need to know that is that here we have character table insert into copy to. 130 00:08:44,420 --> 00:08:45,860 And here and width here. 131 00:08:45,860 --> 00:08:54,320 So you will also make sure that so this width keyword options will format the data in the file with 132 00:08:54,320 --> 00:08:59,180 each column separated by this pipe character. 133 00:08:59,180 --> 00:09:07,370 So that way you can easily see where spaces fill out the unused part of the column. 134 00:09:07,370 --> 00:09:16,820 So to see the output you open the our mining lecture that takes or whatever your file name in that case. 135 00:09:16,820 --> 00:09:22,520 So here, as you can see here, one, two, three, four, five, six, seven, eight, nine, ten. 136 00:09:22,550 --> 00:09:30,020 We have ten characters and almost one, two, three, four spaces and here we have ten characters as 137 00:09:30,020 --> 00:09:30,380 well. 138 00:09:30,380 --> 00:09:32,420 And here we have seven spaces. 139 00:09:32,510 --> 00:09:39,680 So even though you specify ten characters for both of the char character and var character columns only 140 00:09:39,680 --> 00:09:45,240 the car columns outputs ten characters in both rows. 141 00:09:45,240 --> 00:09:47,100 Here you can see here. 142 00:09:47,970 --> 00:09:51,090 So padding and use characters with spaces, as you can see here. 143 00:09:51,090 --> 00:09:55,410 But in other cases there is no such practice, right? 144 00:09:55,500 --> 00:10:01,140 So again, there is no real performance difference among the three types, although this example shows 145 00:10:01,140 --> 00:10:05,730 the character car can potentially consume more storage. 146 00:10:06,900 --> 00:10:10,050 This can potentially consume more storage. 147 00:10:11,710 --> 00:10:15,880 Then, um, other, uh, characters here. 148 00:10:15,880 --> 00:10:20,380 So then actually that's not needed in most cases. 149 00:10:20,380 --> 00:10:27,220 So if you use spaces in each column might seem negligible, but multiplying that over millions of rows 150 00:10:27,220 --> 00:10:31,900 in dozens of tables and you will soon wish you had the more economical. 151 00:10:31,900 --> 00:10:38,230 So I need I tend to use text on all my character columns. 152 00:10:38,650 --> 00:10:45,250 This text here that saves me from having to configure maximum length for multiple character columns 153 00:10:45,250 --> 00:10:53,410 and means I won't need to modify a table later if the requirements for a character column change.