 Hello, everybody. We are just starting now with Joe Conway giving a talk about getting into Postgres. And we're giving him one more applause, please. Thank you. So my name is Joe Conway, and I work for a company called Crunchy Data, mostly in the states, not here. I've been with the Postgres community. I've been using Postgres for like 20 years and started getting active with the community like 18 years ago, and I've been a committer for 15 years or so. These slides, there's a lot of detail on here. I'm going to go through them fairly quickly because that's the only way to do it in 50 minutes. They will be available if nowhere else on my own website, JoeConway.com. I'm not sure what the Postgres guys have coordinated for us to put slides somewhere centralized or not, but the wiki. So we still use the wiki? Okay. The wiki.postgres.sql.org, there's a page that has basically all the conferences you drill in and then on that each individual conference wiki page or links to the talks. So this talk is basically an introductory talk to help you get up and running on Postgres. If you're ready for me with Postgres, you probably don't need to be here. You might still learn something. I actually, even after all these years, whenever I sit down and prepare a talk like this, I learned something I didn't know. So it's, you know, it hopefully is worth your while. I'm going to start out with how you install Postgres. And one of the things you also see on these slides is that I'm trying to cover sort of the main ways to do things that are different between different distributions. So and with everything in Postgres, there's typically more than one way to do the same thing for the basic administrative type operations. So to start out when you install, you can get your RPMs or DEBS either from your own distribution or from the Postgres development group distribution. The RPMs managed by Debra, who's in the back of the room, and the DEBS are managed by Christoph Berg and some other folks who are wandering around, I'll pause down somewhere right now. There are also builds for Windows available. These blue on my slides, if you go download them later, are links to those spots on the website, so you can go find them. But there's at least a couple of different distributions of Windows binaries you can get. I don't necessarily recommend you run Postgres on Windows, but lots of people do for development purposes at least. Mac has options and Solaris and various BSDs have options. And I know I just said that this is kind of an introductory talk, but I'm going to spend just a couple of slides talking about how you might build and install Postgres from source, because that's kind of a useful skill to have. And it's actually surprisingly not that terribly difficult. It's, so I'm just getting started might sound scary, but it's not that hard. So my example for this is going to be on a Red Hat system. On a Red Hat or a variant system, you're going to need to install Git in order to get the Postgres source tree. And then you're going to need a bunch of build dependencies. And this statement here, this is a YAM install and a bunch of packages. These are what I find I need on a kind of a clean Red Hat system in order to be able to build Postgres. So I'm not going to try and go through these in one by one in detail. But if you download the slides, you can get them from there. The next thing you're going to want to do is clone the Postgres source. So you typically want to make a spot where you're going to land that tree, switch into it and then use Git clone. And then this is the URL that you would use with Git clone. The clone Postgres from the main repository. And then CD into that directory and check out a branch. So in Postgres, the branches are named like this. REL, and actually prior to Postgres 10, this would be REL 9, underscore 6, underscore stable, or 9, underscore 5, underscore stable. 10 is the latest stable branch. So if you wanted to build the latest Postgres, this is the one that you would check out. Now, I'm assuming that we're on a system where we already have the Postgres development group RPMs installed on the system and I want to compile Postgres for debug build so I can troubleshoot something. So I want it to be configured basically the same way as what I get from the RPM. So what I'm going to do is capture and modify a couple of flags from the existing build from the RPM. So this command here is just, first I'm exporting the path to my binaries. And then I'm going to run pgconfigconfigure, which will get me exactly the way the RPMs were built. And I'm going to replace the optimization level and the debug flag that was used by the RPM maintainer. In order to use these flags instead, which are going to give me a more debuggable build. And then you just, once you've done that configure step, you just do make and make install this dash j on make, if you're not aware of it. That just does a parallel make, so it makes a lot faster. Once you've done that, kind of the typical debug workflow is you're going to log into PCQ or some client, you find the back end process associated with that client, usually you do that just on the server that Postgres is running on, use PS, identify the back end that the database that you're logged into. Attach to it with GDB and set a break point. And then execute some statement from your PCQ session and then go on with your debugging. And again, someone was joking that the name of this talk was a crash course. So maybe we could do a whole course on this, but that's well beyond the scope of this thing. So, any questions about that before I move on? Okay, so the first step, now that you've got Postgres installed, you've probably installed it from a Debian package or an RPM as you want to configure it. So the files that are used for configuration are called postgresql.conf. And then there's also this postgresql.auto.conf. And that is actually generated when you do an ultra system command. So you wouldn't normally want to edit that one directly. The form of that file is normally a name of a configuration variable equals and then a value. And now, in order to activate the changes that you make, you're going to need to reload the Postgres service. And as I mentioned earlier, here's five different ways you can do that. Basically, there's a built-in command to Postgres called pgconf reload. If you select that, that's going to reload your configuration file. There's also a binary that comes with Postgres called pgcontrol. You can use that with a path to the data directory for the instance of Postgres you're interested in, and just the command reload. Or if you're using the service or the system control files, there's also equivalent ways to do that, service, Postgres reload, system control reload. I'm not sure why they switched these around, but that always bugs the heck out of me, I don't know about you. You can't have several services at the same time. Okay. And then finally, the really old school way to do this is you can send a SIGHUP signal to the Postmaster PID. So if you go find the actual Postmaster PID, Postgres spawns several processes when it starts up, even when nothing else is happening. But it's usually pretty easy to identify the one that is the Postmaster, because it's got the directory for the Postgres data directory in the command line. So if you find that one, you can actually send it a signal directly. Now if you want to make changes, you could basically have either persistent changes or you can have procession changes. The persistent changes are going to live across Postgres restarts, across Postgres session restarts, and so on. So the canonical way to do that is modifypostgresql.conf. You can also actually, the Postmaster itself, when it starts up, you can feed it options. And some of the startup scripts that are around do that. That's probably not something that you're going to mess with as someone new to Postgres, but that is something that you can do. And then in recent releases, I think it's been since about Postgres 9.4 or so. There's this command called alter system. So you can say alter system set or alter system reset with a particular configuration variable. And it'll make that change and it makes it persistent by using that postgresql.auto.conf that I was talking about earlier. This is something that a lot of people I think are not aware of, but there are other ways you can make persistent changes and bind them specifically. So you can alter a database and set a variable to be a certain value anytime someone logs into that database. Or you can do the same thing by altering a role. You can set a variable to be a certain value for a particular role anytime that role logs in. So for instance, if you've got a kind of a power user and you want to give them a higher work mem, you could do that. So every time they log in, they get a higher work mem, even than the one that's set for everyone else in postgresql.conf. The procession, you change configuration variables and procession using the set command. That's kind of the primary method. Reset will change it back to what it was originally when you started. Sure, we'll just show you what the value is. But again, there's five different ways here that you can see and change these things. There's a view called PG Settings, which will expose all of that. You can select from that view to look at the values. You can actually update that view in order to change the values. But just know that if you do that, that is just for the length of your session. As soon as you log out, that goes away. Nobody else sees the same changes. And that in and of itself can be very useful. There's also a function called CurrentSetting, which will give you a specific value for a specific variable. This can be really useful when you're doing very scripting. It can also be useful if you have a custom variable that you want to add to the environment and make use of in your own functions. And then the set config is a way that you can explicitly set a variable using a function. Okay, in terms of the actual settings for these configuration variables, there are people who do three hour tutorials on that topic alone. So there's no way to cover all that. I used to try and cover more of it in this talk and then I always ran out of time. So really, just keeping it to a couple of slides. These are kind of the ones that you'll almost certainly want to at least look at. The first one being listen addresses. Depending on the distribution you're on, that may be set to something like localhost. If it's set to localhost, no one can connect from off your box. So your client may not be able to connect until you've changed that to use either like asterisk which will mean all your interfaces or a specific interface on the host. So that's something you probably are going to want to change. You might not. You might only want to allow connections from localhost. Max connections defaults to 100. This one, the main thing I'll say about this is that don't make that any higher than you really need it. And if you find yourself wanting to make it like 500 or 1,000 then you should be looking at a connection pooler. Generally speaking, kind of rule of thumb, you don't want to have more than about two or two and a half times the number of CPUs in terms of actively running connections. Now you can have 100 open connections, 80 of which are doing nothing, and 20 that are active at any given time, that would be all right. But I still wouldn't leave 1,000 open connections even if only 20 of them were active because those all those connections use RAM to a certain degree. They use resources, they add, they increase the size of certain tables and share memory that Postgres uses. So generally make that setting as low as works for your environment. Shared buffers, this is the one that virtually I think everyone has probably heard of with Postgres shared buffers is what's used for caching data once it's been read from disk. There is a kind of a general thumb rule there that you use somewhere between a quarter and a half of RAM for shared buffers. But number one, that's a bit old guidance. And number two, it's very dependent on your situation. If you've got a Java app running on the same host as Postgres, maybe that's not a good thing to do because you need more room for Java. Kind of the best thing there is I could say is to test different settings for your workload and your data. If your database is fairly small or if you're working set, the things that are commonly being accessed, is small enough to fit in that value, then everything's cached all the time and that will make your Postgres run really fast. But if your database is large and there's no chance it's ever gonna fit in shared buffers, really making shared buffers big enough when it doesn't all fit anyway, it's probably not gonna help, it may even hurt. So that's something that you really wanna test with with your own workload. Workmem, now shared buffers is kind of a system wide. This is allocated once. And one of the things that actually confuses people is when you look at top and you see all these Postgres sessions, they each look like they're using that same amount of shared memory. While they're all using the same pool, so it's not actually duplicated. Workmem is something that is used potentially multiple times within a given query. Postgres will use workmem to decide to build hash tables, basically. So if you've got aggregation going on, you might have hash aggregates. If you've got hash joins going on, there may be hash tables and memory for that. If you've got sorts going on, there may be memory usage for that. So you can have multiple times workmem just within a single session. So what you don't wanna do is set that to like a gigabyte and you've got 20 active sessions and they're all doing big queries. And now suddenly you're denial of servicing yourself because you're driving the server into a swap, right? So four megabytes is the default, that's really small. Again, depending on your workload, that might be just fine if you're doing OLTP-type stuff, it might be just fine. For some workloads, you might benefit by increasing that. I would just be careful about doing that. And again, as I mentioned earlier, you might bind that change to a specific user or specific database and not have it be global. Effective cache size is kind of a hint for the planner. The general guidance on that is to set it basically equal to the amount of RAM that is used by the OS for buffering files plus shared buffers. Again, that tends to be something like a half or three quarters of RAM in a lot of cases, but your mileage may vary. And then finally, I'll talk about this random page cost. This number four, basically there's a sequential page cost and a random page cost and the sequential page cost defaults to one, random defaults to four. And the idea is on average, it's about four times as expensive to read randomly than it is sequentially. And that was kind of developed. I think Tom Lane actually came up with that number. But he probably came up with it about 15 years ago on hardware of the day. So I find often that number in real life workloads, that number is too high and it drives bad plans. I usually end up setting that down to two and sometimes a little lower. You wouldn't want it ever to be below sequential page cost because that wouldn't make any sense. I have seen people try and do that. And then a final bit of Postgres.conf, you probably want to change your log line prefix. The default does not give all that much information in the Postgres log. There's a lot of information available. At the very least, you're probably going to want to have a time stamp. So by changing this, you'll get these additional items added, appended to the, or prefix to the, to whatever gets logged in the Postgres log. Any questions about configuration settings before I move on? So you're asking if work mem will get released once whatever you're doing finishes. Memory structures that are using that memory definitely get released. They get, they get flushed. Postgres has kind of a, its own native garbage collection so that they will go away whether the OS actually releases it from the process immediately, I don't know. It'll shrink back, yeah. It's not, it's not holding on to that. So now I'm going to go on to the host-based authentication file. This is another critical file in the configuration of Postgres. It's usually to determine who can access the system. The way I like to think of this file is it's kind of like the set of firewall rules, almost. Basically, you're going to say, when a particular client tries to connect, if they match a set of rules on this line, this is the authentication scheme I'm going to use. Including you can have reject as one of those. And the first line that fires is the one that gets used. So if the person logging in fails to authenticate on that line, then they just won't be able to log into Postgres. It's not like it's going to move on, try another one. I'll show you an example of this in a minute. But basically, this is which hosts can connect from, how the client is authenticated, the user names that can be used, and which databases they can access. I didn't mention this earlier, but on a lot of my slides, you'll also see I put links right into the Postgres documentation for the topic that I'm talking about. So again, when you download the slides, you can drill in and get more details on these things. This file is read when the server is started, but it also needs to be reloaded for the changes to take effect if you edit it while Postgres is running. The first line matching the connection type, the address, the database, and the user is what gets used for the authentication. And if the line is picked and the authentication fails, the access is denied. And if no line matches, importantly, it fails to a deny. So this is what it looks like, the basic, this is right out of the file, the commented section that shows the examples. I'm going to cover a little bit what each of these things are in future slides. But this is the default on a Debian variant for Postgres 10. And this is the default for a Well7 install on Postgres 10. So you can see these local connections are basically for local host. And host means someone connecting not from local host. And no, excuse me, I should say these local is actually a local BSD socket connection. And host is a TCP connection, which can be from local host or it can be from another host. What you'll see here is on the Debian variants, when you're logged in using a local machine using a BSD socket, it actually uses peer. And the same thing on Red Hat, which effectively means it uses your OS authentication. So Postgres itself, by default, isn't going to ask for a password if you log in on one of these systems from the same machine. Something you might want to change. So first of all, the connection type. Local is the Unix domain socket. Host can either be a plain or an SSL encrypted socket. If you say host SSL, that means this particular line in HPAconf is requiring SSL. If you say host no SSL, it's requiring it not to be SSL. In terms of whether or not it matches. The database column, you can say all. So you can say that this rule applies to all databases. You can say the same user or same role, which basically means if the database name matches the user that's logging in. So it's kind of a shortcut. There's a replication. Because the all keyword does not actually work for the replication user, you have to specify a special role for the replication user. And then you can specify specific databases either as a list or as a list that's inside of a file that you include. User is very similar. You've got all is a wildcard for any user who should match this rule. You can have a list of user names. You can also have this plus and a group name, which means any roles that are in that group will match. And then you can have a list that's in a separate file that gets included. The addresses can either be an IP address with a CIDR mask or kind of a traditional IP address in a mask. You can also specify a host name. Or you can say these keywords same host or same net, which are, again, convenient shortcuts. If you only want to allow connections from the same host, you can use that keyword. Or if you only want to use allow connections from the same subnet, you can use the same net. The method is the authentication method that's going to be used when the rule is hit. So trust basically means just let the person in. Probably don't want that on other than development environments. The kind of built-in methods, starting in Postgres 10, there's something called Scram SHA-256. That's kind of not only meant to replace MD5. MD5 has been there essentially forever. But these days, in a lot of environments, MD5 is not considered to be secure. And so therefore not allowed. The password method is actually, I think, has been deprecated for a long time. And I think it's actually been removed in 10? Or is it 11 when it was removed? I think it was removed in 10. But in any case, for new installs, you would probably want to use Scram SHA-256. It's a much more secure mechanism for handling passwords. If you're not going to use that, you're going to use probably something like SIRT, which means I want to use SSL SIRT. So I can authenticate the server and the client of the server. I can also do peer, PAM, or IDENT. Don't necessarily recommend those in general. Probably more recommended if you can use an external system as something like Kobros or SSPPI Active Directory LDAP radius. Or you can explicitly say this rule should be rejected. And then each of those authentication methods might have some options associated with them. And they're just listed as name equals value. And it depends on which method, which options are allowed. So now here's an example. Again, here I'm saying from a Unix domain socket on the same machine, I'm just going to trust the connection. And from local hosts, I'm also going to trust the connection. But from someone on the same network, I'm going to require the Scram SHA-256. And someone from this specific network, I'm going to use LDAP. And so here's an example of options associated with LDAP. OK, any questions on that before I move on? No, that's all controlled through SQL commands. That's all in turn. I mean, I've seen people script that sort of thing where they reach out to LDAP and then have a cron job that goes out to LDAP, grabs some stuff, and then executes commands in the database. So you can kind of piece it together yourself, but that's not built in. OK, so now that we know how to set up all the configuration files for Postgres, the next thing you got to do is actually initialize the directory. Otherwise, you don't have a database to connect to. You can't even start. So again, multiple ways to do this. The process is called initDB. The binary that comes with Postgres, if you kind of do it yourself, is initDB. You give it this dash capital D in a data directory. And basically, it initializes that directory as your Postgres data directory. That has to be empty if it's not empty. You cannot exist, actually. It'll create it for you. But if it's there and it's not empty, you'll get an error. On Red Hat, they provide a script. Postgresql10 setup initDB is the command you would use. That creates a cluster and the config files all in a data directory in this location. So this is kind of the standard location on a Red Hat, CentOS system, varlib, PG SQL, version number, data. A Debian-based system, they also provide a script called create cluster, PG underscore create cluster. So in this case, PG underscore create cluster, 10 is my version and main is my instance. You can actually have multiple instances of Postgres of the same version on the same host, if you want, with the Debian packages. And so this is the way it's managed. That will create a cluster in varlib, Postgresql10 main, and that's the actual data directory, but it will put all the configuration files under etc. So it's etc, Postgresql10 main. And if you had another instance that wasn't called main, it was called base or something else, then that would just change. So once Postgres has been, the data directory has been initialized, the next thing you're gonna do is wanna start Postgres. Again, multiple ways to do that, depending on what system you're on. Just, this is a kind of a special note, if, again, this is kind of not something that you would do all the time, and you probably won't remember it, if the one thing you remember is that it exists when you need it, you'll know to go look for it. There is a single user mode, so if you use the Postgres binary dash dash single, point it to a data directory and a database name, it'll bring up Postgres in single user mode, and there are certain things that can really only be done in single user mode. Hopefully, you won't ever need that. It typically means something has gone wrong. So, yeah, I've seen people go years and years and years without ever needing that. However, as a developer at Postgres, sometimes I screw things up myself rather badly, so that's the way you can rescue a cluster in certain cases. Again, if you're gonna manually start Postgres, this PG control binary that comes with it, you give it the path to the data directory, you can optionally give it a log path and you tell it to start, and that'll start Postgres. What you probably wanna do if you're new to Postgres and you're probably running RPMs from your distribution or devs from your distribution is you're probably gonna use one of these methods. In Red Hat 6, it was still the old service style, so you would just say service Postgres QL start. Red Hat 7 is system D, so that's system control start Postgres QL dash 10. Debian comes with its own method for doing start, but the recent versions of Debian will also support the system D method. So you can use either the script that comes with the Debian distribution PG control cluster. So you say version 10, instance main, start. You can also use a command that's similar to this one. I think it's a little different in the name of the service. It's like Postgres QL at 10 dash main, I think, something like that. Stopping Postgres. If you're in single user mode, control D will get you out. If you do it manually, PG control, again, very similar to the start command, you're just gonna say stop. The difference here is you're gonna specify a method. I'll touch on what those methods are in another slide, but that is an important consideration. And that also may be a reason why you wanna use PG control instead of one of the services. In Red Hat 6, the old service model, it's just service Postgres stop. Red Hat 7 or system D, system control stop, service name, Debian base, same thing. PG control cluster, the version, the instance, and stop. Now, these shutdown modes are somewhat important. I think most of the distribution defaults, like the service files, use this fast method. If you use smart, basically Postgres is gonna sit there and wait until all of the existing sessions have closed. If you use fast, it's going to terminate all the existing sessions, but it's gonna do a graceful shutdown of Postgres. And what that means is it preserves things in a way when Postgres comes back up, it doesn't think that it crashed. Probably one of the more important aspects of that is that Postgres is constantly keeping stats about what activities are going on, but that staff file is not crash resistant. So if you do this last option immediate, basically, as far as Postgres is concerned, it just crashed or your server lost power or whatever. And when it comes back up, it has to go through a crash recovery and you're gonna lose your stats, which means now Postgres doesn't know when to auto vacuum your tables, at least not until the statistics start building up again. So you don't typically wanna do that. Another important aspect of that is recently ran into, if you're running Postgres, for instance, in a container, when the container goes down, if you don't arrange things just right, Postgres effectively gets killed. So now when it comes back up, let's say your database is on persistent storage, it's doing crash recovery just like I just described. So now let's talk about how to terminate a particular session. If you're in a terminal, you can find the process using PS or whatever, grep for maybe the database name. Once you find the PID, as in this case here, you can say kill sigterm with that PID and that will actually kill that back end process. There's a built-in way to do that. And this is more typical of what you'd probably wanna do on a running system. Let's say you run this SQL statement, you wanna look for the PID state and the clock time stamp minus the last time the state changed, which is an age. And the query from PGSTAT activity, this is one of the Postgres system views that's available that can show you. So in this case here, I've got this PID has been idle in transaction for 26 seconds and the command was begin. So someone ran it, started a transaction and never did anything. So I can identify what it is and identify the fact that I really wanna go kill that. So now I can say PG terminate back end with the PID number and kill it. Okay, it looks like I'm gonna have to speed up a little. I'm not gonna get through all this. You can do this more or less example, the same thing with cancel. So the difference between, terminate is actually gonna kill the session, cancel is just gonna kill the running query, but leave the session open. So you can do the same thing except it's sig end instead of sig term. And it's PG cancel back end instead of terminate back end. Okay, so now I'm onto the final section, just kind of miscellaneous hints that might help you get going. How many people in here have used PCQL before? Actually quite a few. So maybe this section isn't gonna be much of a surprise to anyone. So first of all, one thing to be aware of is you can specify host port and username when you run PCQL so you can connect to other systems. That's not always obvious to new users. I like to point that out. You can actually execute from, this is useful for dash scripts. If you run PCQL dash C, you can actually execute some SQL right from the command line. You can also do dash dash command. Or you can echo SQL into PCQL and execute that way. And there's actually subtle difference in the way these two are executed. When you do these two methods, if you've got multiple commands that are concatenated with a semi colon, they're basically sent to the Postgres all at once. And if one of them fails, kind of it just stops right there. Whereas if you do the echo into PCQL, it's more like you're executing from a file and it will go line by line. And so things like there's a option for PCQL called unerror stop. That works if you do the echo mode. It doesn't work if you do the other one. You can repeatedly execute a command using watch. This is really useful sometimes if you wanna keep an eye on something that's going on. So if you just use a watch command with one of these together, you can just see it refreshing on the screen. You can list all the databases using PCQL-L or dash dash list. This one is another one that's really useful. I'm continuing to be surprised how few people know this. This dash E or echo hidden, if you're only in PCQL when you do like slash D to describe a table and it shows you all about the table, PCQL executes a bunch of SQL in order to gather that information. If you ever wondered how to get that same information for yourself, start PCQL with the dash E and then do a slash D in a table name and you'll see all of the verbose SQL that's been run to gather that information. If you didn't already know it, basically PCQL as long as it's built with the read line supports, kind of normal read line things. So you can do up arrow for the last line in history. You can do control R and start typing the search history and you can do tab to autocomplete. Postgres community goes to great lengths to keep like that tab autocomplete stuff working with new features. These are some of the slash commands. Probably the most important one is slash question mark because that's gonna tell you what all the rest of them do. Slash H will actually give you help on a particular SQL command. DT will show you all the tables and you can optionally filter that including with wild codes. Slash D actually describes a table but you can also use wild cards there. Slash DF describes functions again with wild cards. Slash X is what's called expanded output and normally Postgres puts its output in a table that's wide, each column goes across. If that's too wide for your terminal slash X if you turn it on will actually cause the rows to be one, each column will be one under the other so it fits better on the screen. And in recent releases there's now an option to say auto which means Postgres, PCQL will actually just figure that out for you. Slash E lets you edit the query buffer and there's a built in now to PCQL slash watch which will repeatedly execute the same thing. Some general notes on syntax. With an identifier, Postgres if you don't quote your identifiers is gonna automatically downcase them so everything gets lower cased. If you want to preserve uppercase or if you wanna use keywords in the names of your objects or strange characters you can use quotes and then exactly whatever you type will be preserved. You can do embedded quotes using it by doubling them up. And you can actually do unicode identifiers using a kind of ugly syntax like this. There may be better ways to do it but it's not something I do all the time. So that was the best way I could come up with. String literals are similar so identifiers are things like table names, column names, other objects in the database. A string literal is something that you're storing in the table or comparing against or using in one of your functions. So the most simple form is basically just the single quotes around it. But there's something called dollar quoting I'm gonna cover in another slide which is very powerful and very useful. You can double up the quotes to have embedded quotes. And again you can do a literal unicode by appending this UAMP or SAM to the front. Comments in Postgres kind of centered SQL comments or dash dash. Postgres also supports C-style, multi-line comments, again very useful. Dollar quoting, so this is basically a method that you can have embedded quotes within string literals without driving yourself crazy doubling and quadrupling and years ago we sometimes had eight or 16 single quotes in a row. What you do is you have a dollar sign. It's gotta at least be two dollar signs but it can be two dollar signs with any tag in between. And as long as whatever you start with matches whatever you end with that's how Postgres will figure out everything in between is some kind of a string literal. And you can nest those things. So if you look at this example here I've got a function body which is delimited by dollar sign, underscore dollar sign. And inside of that function body I have a string literal which is now just double dollar, double dollar. And then inside of that string literal I can now use a single quotes and everything will just work. Okay, getting down to the end which is good since I'm now probably down to about five minutes. I'm just gonna cover the basic data types real quick. Character strings, there's text var car and car. These are kind of SQL standard, text is not. Bottom line here is they're, excuse me. As far as Postgres is concerned they're actually all stored exactly the same. So if you wanna maintain compatibility with other databases but I mean you use the SQL standard but there's really no particular benefit to doing that and so it's most people who use Postgres like all day long tend to use text. It might even be like probably not even measurably but arguably faster because these are doing extra checks that this one's not. Data types numbers, Postgres supports small ints, two bytes, four bytes, eight bytes. Serial is not a real data type. This is kind of a way to automatically add a sequence which can either be a four byte integer and eight byte integer. So serial and big serial is the eight byte integer. Real and double are both floating point. So these are kind of standard math library type things. Numeric is actually a arbitrary precision type of calculation. So if you need like exact precision you would wanna use numeric. That's gonna be take more room and be slower in general than using like double precision if you don't need it. So if you're doing, if you're collecting data from sensors you probably want double precision. If you're storing values that represent money you probably want numeric. If you're just creating a primary key column and you just want a number that's gonna sequence you want integer or big int. You probably want big int. There's not a lot of difference in performance actually if you use big ints versus integers that give you a whole lot more head room. So you could use big serial or just create a big int. Date time, we support date without time. We support time without date and with and without time zone. And then timestamp is basically a date and a time and we support those with and without time zone. And then we support intervals things like three months I could say today plus three months or today plus five minutes. You probably in general wanna use timestamp with time zone and you intervals are very useful for a lot of things. There are enumeration types. This is kind of a way that you can build your own. I wanna constrain the values to be red, green, and blue. So now I can create this table clothes and I say I want my column color to be of type color and now I can specify that it's red and it'll be constrained to be one of those acceptable values. There are other ways to do this but this could be very useful at times. There are geometric types built into Postgres. You know, generally if you're gonna do geometric types you probably want to use PostGIS. That's a special extension. Again, we could spend all day talking about PostGIS. I'm not gonna try and cover it. Just know that PostGIS exists and if you've got spatial data, geometric type calculations you need to do, that's what you wanna go find. Postgres supports JSON. The JSON support is actually really good these days. There's actually a JSON and a JSON-B. JSON was the original one. This one basically checks well form this and preserves the string exactly the way it was when Postgres got it. Whereas JSON-B, the string is actually parsed and stored as a binary but the advantage to that is we have much more rich support for operators and indexes with JSON-B. So most of the time you probably wanna be using JSON-B if you're storing JSON. The one thing that you do have to be aware of is that it will mutate your input so what you get out may not look exactly like the input. There's an XML type, checks well form this, it does not do DTD validation importantly. And then finally some other miscellaneous data types. There's data type called byte A which is for binary data. There's a true Boolean. You can do arrays of pretty much all of the types. Multi-dimension arrays. You can do composites, in other words you can build tuples. You can actually have those be a column and a table or being used. You can actually build arrays of tuples like this. There are range types which are really good if you're doing scheduling type applications. You can say I want anything in this range or does this range overlap with that range. There's tech search types or there's support for full tech search which is really good in Postgres. And then there are other miscellaneous types for network addresses. There's lots of extensions that create types for Postgres. UIDs and here's a link for that. So I think I'm just about out of time and I'm done with the slides so there's maybe time for one question. Hello, thank you. So this is when you've got a large buffer in. So when you insert in the P-SQL and you're missing some character or something in the SQL statement, you're missing a quarter of a parentheses. And so what is it you want to be able to do? So it's there in your text editor when you paste it in the P-SQL it's not there. What are you using like a terminal client? I mean that almost sounds like it might be a bug in the terminal's paste buffer. I'm not sure I've seen that. I think that's it. That's it. Thank you very much.