{"id":93116,"date":"2022-01-10T15:54:37","date_gmt":"2022-01-10T15:54:37","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93116"},"modified":"2024-04-16T18:59:07","modified_gmt":"2024-04-16T18:59:07","slug":"creating-a-database-and-tables-in-postgresql-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/creating-a-database-and-tables-in-postgresql-learning-postgresql-with-grant\/","title":{"rendered":"Creating a Database and Tables in PostgreSQL: Learning PostgreSQL with Grant"},"content":{"rendered":"

This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n

You have an instance of PostgreSQL running locally (or you\u2019re connected to the cloud). Now, what to do with it? In the first article in this series, I said I would start by learning about backups. However, I realized I had another thing I needed to do before I could begin learning \u2013 and teaching — backups. I needed to create a database and a couple of tables. I could then back them up.<\/p>\n

As with any data management system, there are many ways to get this done. You can do everything from a command line or use a graphical user interface (GUI) that lets you work with PostgreSQL. Finally, the SQL commands are available once you\u2019re connected to PostgreSQL. I\u2019m going to focus there, working primarily within Azure Data Studio (ADS). I\u2019m doing this because:<\/p>\n

    \n
  1. I like Azure Data Studio. I find it easy to work within. It\u2019s fast. It\u2019s clean. It has plugins to do all sorts of things<\/li>\n
  2. For example, it plugs in very nicely to GitHub, so you can easily keep the code as you develop it up on GitHub (go here<\/a> if you want to see my VERY basic PostgreSQL code: )<\/li>\n
  3. I\u2019m old, and I\u2019m just more comfortable working on code within a dedicated coding tool as opposed to simply running it from the command line.<\/li>\n
  4. I don\u2019t want to document every single possible method, so I had to pick one. This is it.<\/li>\n<\/ol>\n

    That said, let\u2019s get started.<\/p>\n

    CREATE DATABASE<\/h2>\n

    The command to create a database is quite simple:<\/p>\n

    CREATE\u00a0DATABASE\u00a0postgrelearning;<\/pre>\n
    Well, that was easy.<\/p>\n
    Let\u2019s talk about this just a little bit. Since I know SQL Server, I\u2019m going to compare what\u2019s going on here, to what\u2019s going on there. In SQL Server, you have a system database called model<\/em> used as a template when you create a new database. In PostgreSQL, the same thing happens, but the database is called template1<\/em>. The table template1<\/em> operates very similarly to model. You can add objects to template1<\/em>, and then they will automatically exist in any other new database you create.<\/p>\n
    However, there\u2019s more going on in PostgreSQL. Let\u2019s look at my list of databases:<\/p>\n
    \"Graphical<\/p>\n
    There\u2019s also a template0<\/em> database. What\u2019s that? Well, in the event you completely mess up template1<\/em>, template0<\/em> acts as a baseline. Also, when you restore a database, PostgreSQL uses template0<\/em> to start that process (I\u2019ll explain how and why once I learn).<\/p>\n
    And what about that postgres database? Well, that\u2019s a default built into PostgreSQL so that tools always connect to a default database.<\/p>\n
    The CREATE DATABASE <\/code>command<\/a> has a number of options, as you can see in the documentation. One of the more interesting to me relates right back to those template databases. You can run the CREATE DATABASE<\/code> command like this:<\/p>\n
    CREATE\u00a0DATABASE\u00a0templatetest\u00a0WITH\u00a0template\u00a0=\u00a0postgrelearning;<\/pre>\n
    That\u2019s right. You can specify another database as a template. However:<\/p>\n
    \"An<\/p>\n
    You can only use a database as a template when there are no connections to that database at all.<\/p>\n
    There are several other options, from defining the database owner, its locale and collation, and more. I\u2019m not going to cover them all now.<\/p>\n
    If you need to get rid of a database, the command again is simple:<\/p>\n
    DROP DATABASE templatetest;<\/pre>\n
    That will remove the entire database. However, you can\u2019t have a connection to it when you drop the database. As with CREATE<\/code>, DROP<\/code> includes some options. For example, you could write the command like this:<\/p>\n
    DROP DATABASE IF EXISTS templatetest;<\/pre>\n
    That will eliminate an error if you try to drop a database that isn\u2019t there. Also, you can delete a database that is in use:<\/p>\n
    DROP DATABASE IF EXISTS templatetest WITH (FORCE);<\/pre>\n
    Now, that\u2019s dangerous, of course. Always exercise extreme caution using this command. In fact, I\u2019d shy away from using it because all it takes is one slip-up, and you\u2019ve dropped the production database.<\/p>\n
    One more thing. Using ADS, you can quickly and easily switch between databases in the query window. However, there doesn\u2019t seem to be a way to do this strictly from SQL. You control the connection with the tool you are using.<\/p>\n
    CREATE TABLE<\/h2>\n
    With a database in place, to have something to work with, start by using the CREATE TABLE<\/code><\/a> <\/code>syntax<\/a> to get a table in place. If you\u2019re already familiar with an RDBMS, you know that this is a positively huge topic (heck, follow the link and look at the documentation). I\u2019m going to keep it simple for the moment and not go too far down the rabbit hole. There will be plenty to expand on later as we work through this series.<\/p>\n
    Here\u2019s the first test:<\/p>\n
    CREATE\u00a0TABLE\u00a0TableTest1\u00a0\r\n\u00a0\u00a0\u00a0\u00a0(ID\u00a0int\u00a0NOT\u00a0NULL,\u00a0\r\n\u00a0\u00a0\u00a0\u00a0SomeValue\u00a0varchar(50)\u00a0NOT\u00a0NULL,\u00a0\r\n\u00a0\u00a0\u00a0\u00a0AnotherValue\u00a0varchar(30)\u00a0NULL);<\/pre>\n
    The core syntax is recognizable if you\u2019re coming from SQL Server. The results look like this in ADS:<\/p>\n
    \"An<\/p>\n
    The code doesn\u2019t include the schema, so the default schema, public, was used. I much prefer to define a schema and then assign tables to that rather than run the default. Having everything in a defined schema or schemas makes it much easier when it comes time to begin to lock things down.<\/p>\n
    Of course, to really create tables for testing, the example will need to be a bit more sophisticated than the above test. Let\u2019s start with the schema:<\/p>\n
    CREATE\u00a0SCHEMA\u00a0hsr;<\/pre>\n
    From there, create a couple of tables with primary keys and foreign keys:<\/p>\n
    CREATE\u00a0TABLE\u00a0hsr.radiobrand\u00a0\r\n\u00a0\u00a0\u00a0\u00a0(radiobrandid\u00a0int\u00a0PRIMARY\u00a0KEY\u00a0GENERATED\u00a0BY\u00a0DEFAULT\u00a0AS\u00a0IDENTITY,\r\n\u00a0\u00a0\u00a0\u00a0radiobranddesc\u00a0varchar(50)\u00a0NOT\u00a0NULL);\r\nCREATE\u00a0TABLE\u00a0hsr.radio\r\n\u00a0\u00a0\u00a0\u00a0(radioid\u00a0int\u00a0PRIMARY\u00a0KEY\u00a0GENERATED\u00a0BY\u00a0DEFAULT\u00a0AS\u00a0IDENTITY,\r\n\u00a0\u00a0\u00a0\u00a0radioname\u00a0varchar(50)\u00a0NOT\u00a0NULL,\r\n\u00a0\u00a0\u00a0\u00a0radiobrandid\u00a0int\u00a0REFERENCES\u00a0hsr.RadioBrand\u00a0NOT\u00a0NULL);<\/pre>\n
    As with SQL Server, there\u2019s syntax to support creating the keys using ALTER<\/code> as well as CREATE<\/code>. There\u2019s also syntax for creating a table constraint, which, if you were creating a compound key or consuming one as a foreign key, you\u2019d have to use instead. You could create the hsr.radio table this way:<\/p>\n
    CREATE\u00a0TABLE\u00a0hsr.radio\r\n\u00a0\u00a0\u00a0\u00a0(radioid\u00a0int\u00a0NOT\u00a0NULL\u00a0GENERATED\u00a0BY\u00a0DEFAULT\u00a0AS\u00a0IDENTITY,\r\n\u00a0\u00a0\u00a0\u00a0radioname\u00a0varchar(50)\u00a0NOT\u00a0NULL,\r\n\u00a0\u00a0\u00a0\u00a0radiobrandid\u00a0int\u00a0NOT\u00a0NULL,\r\n\u00a0\u00a0\u00a0\u00a0CONSTRAINT\u00a0radiopk\u00a0PRIMARY\u00a0KEY\u00a0(radioid),\r\n\u00a0\u00a0\u00a0\u00a0FOREIGN\u00a0KEY\u00a0(radiobrandid)\u00a0REFERENCES\u00a0hsr.RadioBrand(radiobrandid));<\/pre>\n
    The IDENTITY<\/code> definition on the column must stay in place since it\u2019s a column property. However, you could create the primary and foreign keys separately from the column definitions. For the sake of consistency, I\u2019ll probably keep that second syntax instead of the first. That way, all the code looks the same, not just some constraints that have compound keys.<\/p>\n
    That\u2019s the basics.<\/p>\n
    You can test it out just a little:<\/p>\n
    insert\u00a0into\u00a0hsr.radiobrand\r\n(radiobranddesc)\r\nVALUES\r\n('Icom');\r\nSELECT\u00a0radiobrandid,\r\n\u00a0\u00a0\u00a0\u00a0radiobranddesc\r\nFROM\u00a0hsr.radiobrand;<\/pre>\n
    The results are as follows:<\/p>\n
    \"An<\/p>\n
    In short, all the basics are working.<\/p>\n
    Conclusion<\/h2>\n
    We now have a database, a couple of tables, and some data. At its core, PostgreSQL is very much the same as SQL Server. Not identical, of course, but very similar. We\u2019ll start to see more significant differences as we dive deeper. The ability to define more than one template database is a neat trick. The rest of the basics are what I\u2019d expect.<\/p>\n
    Next time, we really are going to look into backup and restore.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    The syntax for creating databases and tables in PostgreSQL is similar to other RDBMS. In this article, Grant Fritchey shows how to create databases and tables.…<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[95506,158977,159066],"coauthors":[6785],"class_list":["post-93116","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-automate","tag-learningpostgresqlwithgrant","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93116","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93116"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93116\/revisions"}],"predecessor-version":[{"id":93215,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93116\/revisions\/93215"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93116"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93116"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93116"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93116"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}