{"id":112449,"date":"2026-10-14T12:00:00","date_gmt":"2026-10-14T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=112449"},"modified":"2026-09-29T15:12:11","modified_gmt":"2026-09-29T15:12:11","slug":"how-to-create-roles-databases-and-tables-in-postgresql-getting-started-with-postgresql-part-two","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/how-to-create-roles-databases-and-tables-in-postgresql-getting-started-with-postgresql-part-two\/","title":{"rendered":"How to create roles, databases and tables in PostgreSQL (getting started with PostgreSQL, part two)"},"content":{"rendered":"\n

Setting up PostgreSQL the right way starts long before your first table exists. In this guide, you’ll learn how to create a dedicated role instead of relying on the postgres<\/code> superuser, spin up a database with proper ownership, organize it with schemas, and build out your first tables with primary and foreign keys. <\/strong><\/p>\n\n\n\n

Every step includes the exact psql<\/code> commands and the real terminal output you should expect to see, so you can follow along in your own container and know immediately if something’s off.<\/strong><\/p>\n\n\n\n

\n
\n\n

Important note before you continue…<\/strong><\/p>\n\n\n\n

This is the second part in Grant Fritchey’s complete set of step-by-step walkthroughs (guides) for getting started with PostgreSQL, designed for any IT professional or hobbyist who wants to add PostgreSQL to their existing skillset. Click here for the full series<\/a>.<\/p>\n\n\n\n

Originally intended to be proctored classes given in a live, in-person environment, Grant has aimed to make the documentation and scripts clear enough to walk through them independently from home.<\/p>\n\n\n\n

Before getting started, please check that you have everything you need.<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

How to create a role in PostgreSQL<\/h2>\n\n\n\n

One of the more important aspects of PostgreSQL to understand is ownership<\/em>. The ownership of any object – whether a schema inside a database, or the database itself<\/em> – is fundamental to PostgreSQL security<\/a> and behaviors. <\/strong><\/p>\n\n\n\n

With that in mind, instead of first creating a database to work on within PostgreSQL, we’ll start by creating a role<\/a>. With the role created, we can then log in to that role and validate that it works. Once it’s working, we can move on to creating a database to work on within PostgreSQL.<\/p>\n\n\n\n

\n
\n\n

Important note<\/strong><\/p>\n\n\n\n

Older versions of PostgreSQL have users<\/em> instead of roles<\/em>. That’s why the CREATE USER<\/code> command is still a part of PostgreSQL, but it simply now acts as an alias to create a role.<\/p>\n\n<\/div>\n<\/div> \n\n\n

How to run a script in PostgreSQL with psql<\/h2>\n\n\n\n

<\/a>Instead of typing in psql<\/code> or copying and pasting into it, we’ll let psql<\/code> run a script for us this time. Externally, in your operating system, we’re going to copy the file creatingrole.sql<\/code> into the volume we created with the container. <\/p>\n\n\n\n

In Linux, you can use the Files GUI (graphical user interface), or<\/em> issue a command like
cp \"05. Creating a Role\/creatingrole.sql\" \/home\/user\/bu<\/code><\/p>\n\n\n\n

If you’re in Windows and not<\/em> running through the Windows Subsystem for Linux (WSL)<\/a>, use either File Explorer or<\/em> a command similar to Copy-Item \"05. Creating a Role\\creatingrole.sql\" C:\\Users\\grant\\bu<\/code>.<\/p>\n\n\n\n

Of course, you’ll have to modify these commands for the paths on your own system. What this does is place the file into the local folder visible inside the container as a volume<\/em>.<\/p>\n\n\n\n

Do note that the container must be running. You’ll want to open a Bash shell<\/a> to the container if you’ve closed it (see earlier in the guide INSERT LINK)<\/mark>. Once connected to Bash in the container, you’ll see something like root@543bcc6b5270:\/#<\/code>.<\/p>\n\n\n\n

We can then validate that the file exists by running the ls \/var\/lib\/postgresql\/bu<\/code> command within the Bash shell, adjusting the path as appropriate to your system. The output will look something like this:<\/p>\n\n\n\n

root@bc83314d37ff:\/# ls \/var\/lib\/postgresql\/bu\ncreatingrole.sql<\/pre><\/div>\n\n\n\n
How to execute a script in PostgreSQL with psql<\/code><\/h2>\n\n\n\n
Now that we know the script is available, we can execute it through psql<\/code> using the command
psql -d postgres -h localhost -U postgres -f \"\/var\/lib\/postgresql\/bu\/creatingrole.sql\"<\/code>.<\/p>\n\n\n\n
This should run successfully to create a role called radiodev<\/code> with a hard-coded password (*securepassword*<\/code>). While still in Bash, you could also run a simple query to see if the role actually exists, and if it has the permission to create a database. The command we issued through the script is:<\/p>\n\n\n\n
CREATE ROLE radiodev WITH\n    LOGIN\n    CREATEDB\n    PASSWORD '*securepassword*';<\/pre><\/div>\n\n\n\n
Now, let’s break this down a little. The first part should be straightforward enough: CREATE ROLE radiodev<\/code> means we’re creating a role called ‘radiodev<\/code>‘. The WITH<\/code> command then allows us to add some additional behaviors to the role. We’re also creating this role as one that can connect to the cluster through the LOGIN<\/code> process. <\/p>\n\n\n\n
Then, we’re giving it an explicit permission<\/a> – the ability to create databases (CREATEDB<\/code>) – and we’re supplying a password for the role. We won’t need the password in this guide, since we’ll be doing the work through psql<\/code> running as a local connection (therefore, a trusted one.) <\/p>\n\n\n\n
\n    
\n\n
Important note<\/strong><\/p>\n\n\n\n
There are, of course, additional options available for CREATE ROLE<\/code>. Read more on Simple Talk here<\/a>, or take a look in the PostgreSQL documentation<\/a>.<\/p>\n\n<\/div>\n<\/div> \n\n\n
How to validate the creation of a role in PostgreSQL<\/h2>\n\n\n\n
I’ll need to leave Bash to operate within my OS. I’ll then use the appropriate command to copy the file into the volume folder, before heading back into Bash to execute the query:<\/p>\n\n\n\n
## leave bash\nexit\n\n## copy the file\ncp \"5. Creating a Role\/creatingrolevalidation.sql\" \/home\/user\/bu\n\n## go back into bash\ndocker exec -it StartingPostgreSQL \"bash\"\n\n## run the query\npsql -d postgres -h localhost -U postgres -f \"\/var\/lib\/postgresql\/bu\/creatingrolevalidation.sql\"<\/pre><\/div>\n\n\n\n
The results should come back like this:<\/p>\n\n\n\n
 rolname  | rolcanlogin | rolcreaterole | rolcreatedb \n----------+-------------+---------------+-------------\n radiodev | t           | f             | t<\/pre><\/div>\n\n\n\n
Going forward, we’ll copy any (or all) files at the same time, rather than going in and out of Bash to do it. Of course, you can use other mechanisms as well. Later in the series (INSERT LINK HERE)<\/mark>, I’ll introduce additional tools for querying PostgreSQL on top of psql<\/code> that can make these operations easier.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Get started with PostgreSQL – free book download<\/h3>\n                
\n                                            ‘Introduction to PostgreSQL for the data professional’, written by Grant Fritchey and Ryan Booz, covers all the basics of how to get started with PostgreSQL.                                    <\/div>\n            <\/div>\n                                            Download your free copy<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
How to test a role in PostgreSQL<\/h2>\n\n\n\n
We can now use the new role with our PostgreSQL database. To see it in action, we can use the
psql -d postgres -h localhost -U radiodev -c \"\\conninfo\"<\/code> script.<\/p>\n\n\n\n
A couple of points here. In our initial use of psql<\/code>, we typed in commands directly. Then, in the beginning of this part of the guide, we executed the scripts from within files. Now, we’re simply passing a command through the -c<\/code> flag. Putting SQL (or, in this case, a meta command) inside double quotes will allow the command to be executed. <\/p>\n\n\n\n
The output from my machine is:<\/p>\n\n\n\n
      Connection Information\n      Parameter       |   Value   \n----------------------+-----------\n Database             | postgres\n Client User          | radiodev\n Host                 | localhost\n Host Address         | ::1\n Server Port          | 5432\n Options              | \n Protocol Version     | 3.0\n Password Used        | false\n GSSAPI Authenticated | false\n Backend PID          | 441\n SSL Connection       | false\n Superuser            | off\n Hot Standby          | off<\/pre><\/div>\n\n\n\n
Importantly here, ‘Superuser’<\/em> is set to the value ‘off’<\/em>. Put simply, this is a different role on the cluster – not the principal administrator (postgres<\/code>).<\/p>\n\n\n\n
We’re now ready to create our first database in PostgreSQL!<\/p>\n\n\n\n
How to create a database in PostgreSQL<\/h2>\n\n\n\n
Now that we have a role with the permission to create databases in PostgreSQL, we could immediately create one as<\/em> that role. However, first, I want to show some other ways of creating a database in PostgreSQL.<\/p>\n\n\n\n
\n    
\n\n
You may also be interested in:<\/strong><\/p>\n\n\n\n
Creating a Database and Tables in PostgreSQL: Learning PostgreSQL with Grant<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
How to create a database in PostgreSQL as a superuser (and should you?)<\/h3>\n\n\n\n
If we were to issue a CREATE DATABASE<\/code> command like this, that database would be owned by postgres<\/code> (the superuser<\/a>): psql -d postgres -h localhost -U postgres -c \"CREATE DATABASE my_first_db;\"<\/code>. Not only would this create a database owned by postgres<\/code> but, since we gave it no other option, it’s just using the defaults. <\/p>\n\n\n\n
PostgreSQL has two template databases: template0<\/code> and template1<\/code>. Template0<\/code> is for the system and should be left alone, while Template1<\/code> is the default template for creating databases.<\/strong><\/p>\n\n\n\n
Since this is a brand new cluster with no other databases or templates created, this method is perfectly OK for our purposes. You can<\/em> create custom templates or even use other databases as a template when creating a new database, but that’s beyond the (simple) scope of what we’re doing here.<\/p>\n\n\n\n
Why you shouldn’t<\/em> create a database in PostgreSQL as a superuser<\/h3>\n\n\n\n
On the other hand, there are a number of reasons we don’t<\/em> want to use the superuser (postgres<\/code>) to create databases in PostgreSQL. First, and most importantly, it violates the principal of least privilege<\/a>.<\/strong> <\/p>\n\n\n\n
This is because the superuser bypasses a lot of security constraints, so we’re potentially exposing things in the system. Remember: ownership and permissions in PostgreSQL cascade from the role that created the object, and even a database in PostgreSQL is an object! <\/p>\n\n\n\n
You want a clear, easily-to-audit ownership chain, which is why it’s best to start with defining ownership through roles other<\/em> than the superuser.<\/strong><\/p>\n\n\n\n
We can look at the owner of a database using a simple query:<\/p>\n\n\n\n
psql -d postgres -h localhost -U postgres -c \"SELECT\n\tpd.datname,\n\tpd.datdba,\n\tpg_catalog.pg_get_userbyid(pd.datdba) AS db_owner\nFROM\n\tpg_catalog.pg_database AS pd\nWHERE\n\tpd.datname = 'my_first_db';\"<\/pre><\/div>\n\n\n\n
We don’t have to use the login to create the database. It’s perfectly possible to assign ownership on creation with
psql -d postgres -h localhost -U postgres -c \"CREATE DATABASE my_second_db OWNER radiodev;\"<\/code>.<\/p>\n\n\n\n
We then see the following results:<\/p>\n\n\n\n
   datname   | datdba | db_owner \n-------------+--------+----------\n my_first_db |     10 | postgres<\/pre><\/div>\n\n\n\n
Altering the script as follows:<\/p>\n\n\n\n
psql -d postgres -h localhost -U postgres -c \"SELECT\n\tpd.datname,\n\tpd.datdba,\n\tpg_catalog.pg_get_userbyid(pd.datdba) AS db_owner\nFROM\n\tpg_catalog.pg_database AS pd\nWHERE\n\tpd.datname = 'my_second_db';\"<\/pre><\/div>\n\n\n\n
We then get the following results:<\/p>\n\n\n\n
   datname    | datdba | db_owner \n--------------+--------+----------\n my_second_db |  16388 | radiodev\n<\/pre><\/div>\n\n\n\n
How to create a database in PostgreSQL (the simple way)<\/h3>\n\n\n\n
For the rest of the guide, we’ll be using a database called ham_shack<\/code>. And, since we’re going to let the database owner also<\/em> own the other objects we create within ham_shack<\/code>, we now need to actually create the database. <\/p>\n\n\n\n
To do this, we can just let the defaults handle things for us:
psql -d postgres -h localhost -U radiodev -c \"CREATE DATABASE ham_shack;\"<\/code><\/p>\n\n\n\n
We can do it like this because, other than the owner, there are no database options we need to worry about right now.<\/p>\n\n\n\n
With the database created, let’s connect to it through interactive psql<\/code> instead of issuing commands all the time: 
psql -d ham_shack -h localhost -U radiodev<\/code><\/p>\n\n\n\n
Since we’re connected to a database other<\/em> than postgres<\/code>, the resulting connection will look different than what we’ve seen in other parts of the guide: ham_shack=><\/code><\/p>\n\n\n\n
Let’s also issue a few commands and queries to satisfy ourselves that we are indeed in a new database. First, we could run the tried and tested \\conninfo<\/code> meta command, giving us:<\/p>\n\n\n\n
      Connection Information\n      Parameter       |   Value   \n----------------------+-----------\n Database             | ham_shack\n Client User          | radiodev\n Host                 | localhost\n Host Address         | ::1\n Server Port          | 5432\n Options              | \n Protocol Version     | 3.0\n Password Used        | false\n GSSAPI Authenticated | false\n Backend PID          | 937\n SSL Connection       | false\n Superuser            | off\n Hot Standby          | off<\/pre><\/div>\n\n\n\n
Another way would be to query using a built-in function:<\/p>\n\n\n\n
SELECT\n\tpd.datname,\n\tpg_catalog.pg_get_userbyid(pd.datdba) AS db_owner\nFROM\n\tpg_catalog.pg_database AS pd\nWHERE\n\tdatname = current_database();<\/pre><\/div>\n\n\n\n
You can simply copy and paste this command into psql<\/code>. Since nothing is executed until the statement terminator is reached – the semi-colon (;) – you can easily have multi-line commands. The function in use is current_database()<\/code>, which returns the database we’re connected to. The results are:<\/p>\n\n\n\n
  datname  | db_owner \n-----------+----------\n ham_shack | radiodev<\/pre><\/div>\n\n\n\n
As you can see, we’re now in a new database owned by the role we just created. Let’s exit psql<\/code> for the moment, using \/q<\/code> – but we will still be connected to the container through Bash. <\/p>\n\n\n\n
With our role and database in place, it’s time to start to work within the database. Specifically, creating a schema.<\/p>\n\n\n\n
How to create a schema in PostgreSQL<\/h2>\n\n\n\n
Similar to other data platforms, PostgreSQL uses the concept of a schema<\/a> as a mechanism for organizing a database. A schema can also be used for security<\/a>, data isolation, and as a means of logically storing related objects.<\/strong><\/p>\n\n\n\n
By default, each database (including the postgres<\/code> database) comes with the public<\/code> schema. While many databases simply store all tables in this schema, it’s better to store your tables, views, etc., within defined<\/em> schema. This is partly for security, but it’s mainly just good management practice.<\/p>\n\n\n\n
\n    
\n\n
Other essential reading<\/strong><\/p>\n\n\n\n
PostgreSQL Schema: Learning PostgreSQL with Grant<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
Let’s start by connecting directly to psql<\/code> and seeing what schema are already in the database, if any: 
psql -d ham_shack -h localhost -U radiodev<\/code><\/p>\n\n\n\n
Once connected to psql<\/code>, we can take a look at the schema in the ham_shack<\/code> database using the \/dn<\/code> meta command. The results are:<\/p>\n\n\n\n
      List of schemas\n  Name  |       Owner       \n--------+-------------------\n public | pg_database_owner<\/pre><\/div>\n\n\n\n
So, yes, this confirms that it’s only the default schema (public<\/code>). However, if we want more information, the meta commands can actually show you more than just a simple list. We can make the call with \\dn+<\/code>, which gives us additional information (mainly on the privileges for the schema in question):<\/p>\n\n\n\n
                                       List of schemas\n  Name  |       Owner       |           Access privileges            |      Description       \n--------+-------------------+----------------------------------------+------------------------\n public | pg_database_owner | pg_database_owner=UC\/pg_database_owner+| standard public schema\n        |                   | =U\/pg_database_owner                   | <\/pre><\/div>\n\n\n\n
How to create your first schema in PostgreSQL<\/h3>\n\n\n\n
Let’s create a schema called gear<\/code> from within psql<\/code>: 
CREATE SCHEMA gear;<\/code><\/p>\n\n\n\n
Using the \\dn<\/code> meta command, we can now see our new schema:<\/p>\n\n\n\n
  Name  |       Owner       \n--------+-------------------\n gear   | radiodev\n public | pg_database_owner<\/pre><\/div>\n\n\n\n
However, let’s now focus on the search_path<\/code>. First, let’s see what’s currently in it by running the SHOW search_path;<\/code> command.<\/p>\n\n\n\n
On a completely new database such as this, it should return:<\/p>\n\n\n\n
   search_path   \n-----------------\n \"$user\", public<\/pre><\/div>\n\n\n\n
What is the PostgreSQL SHOW<\/code> command?<\/h3>\n\n\n\n
The PostgreSQL SHOW<\/code> command is used to return runtime configuration<\/a> parameters. This could be settings such as timezone<\/code> or max_connections<\/code> but, in our case, we’re going for the search_path<\/code>. <\/strong><\/p>\n\n\n\n
What is the PostgreSQL search_path<\/code>?<\/h3>\n\n\n\n
The search_path<\/code> is where the role you’re operating within looks through<\/em> the database for objects. If you don’t have a schema in the search path, you won’t be able to see objects within that schema in order to query them. <\/strong><\/p>\n\n\n\n
The order of the search_path<\/code> matters as well, as PostgreSQL will look within the first schema in the search path for an object. If it finds it there, it won’t continue to look through the other schema, assuming you haven’t used the schema when referencing the object. <\/p>\n\n\n\n
If you do<\/em> use the schema in the object name (such as gear.radio<\/code> for a table name), and you have access to that schema, the search_path<\/code> doesn’t matter. <\/p>\n\n\n\n
However, if you simply reference the table radio<\/code> with the search_path<\/code> we currently have, you won’t find that table – even if you have the right access.<\/p>\n\n\n\n
The \"$user\"<\/code> is a token that resolves to the currently connected role. This is where you could create a schema called radiodev<\/code> and it would automatically be referenced by our login. <\/p>\n\n\n\n
Thankfully, modifying the search_path<\/code> is easy:
SET search_path TO \"$user\", gear, public;<\/code><\/p>\n\n\n\n
If you don’t include \"$user\"<\/code> or public<\/code> when you change the search_path<\/code>, they’ll be removed – so be careful. However, using the SET<\/code> command will only change the search_path<\/code> for the duration of the current connection.<\/p>\n\n\n\n
How to add the gear<\/code> schema to the role<\/h4>\n\n\n\n
In order to change the PostgreSQL search_path<\/code> permanently, you have to either ALTER<\/code> the role itself, or the database. <\/strong><\/p>\n\n\n\n
Here’s how to add the gear<\/code> schema to the role:<\/p>\n\n\n\n
ALTER ROLE radiodev SET search_path TO \"$user\", gear, public;<\/pre><\/div>\n\n\n\n
This change will only take affect on new<\/em> connections for this role. You would either have to use the SET<\/code> command along with the ALTER ROLE<\/code>, or, disconnect and reconnect to the database. However, before we test the new and improved search_path<\/code>, let’s add one more schema:<\/p>\n\n\n\n
CREATE SCHEMA people;\nALTER ROLE radiodev SET search_path TO \"$user\", gear, people, public;\n\\q<\/pre><\/div>\n\n\n\n
Now, if you connect again – using the Docker command to Bash (from part 1 of the series) to connect to psql<\/code> – and run the SHOW<\/code> command, you’ll see the following:<\/p>\n\n\n\n
          search_path          \n-------------------------------\n \"$user\", gear, people, public<\/pre><\/div>\n\n\n\n
One point to add: there’s no way to simply ALTER<\/code> the search_path<\/code> to add a schema. You must<\/em> use the SET<\/code> or ALTER ROLE<\/code> commands, as laid out, with the full list of items you want in the search_path<\/code>. If you leave items off<\/em> the search_path<\/code>, they simply won’t be there.<\/p>\n\n\n\n
Up next: creating tables!<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Move fast. Govern at scale.<\/h3>\n                
\n                                            Redgate Flyway Enterprise embeds guardrails in the database layer, so every change is policy-checked, deterministic, and traceable.                                    <\/div>\n            <\/div>\n                                            Try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
How to create a table in PostgreSQL<\/h2>\n\n\n\n
With a database and a schema in place, we can begin to create tables in PostgreSQL. I’m not going to fully explore all the possible mechanisms around table creation and management in this guide – however, we will both CREATE<\/code> and<\/em> ALTER<\/code> tables so that you get a real sense of the syntax within PostgreSQL.<\/p>\n\n\n\n
This will include all the basic PostgreSQL behaviors such as data types<\/a>, nullability<\/a>, and primary and foreign keys<\/a>. <\/p>\n\n\n\n
\n    
\n\n
You may also be interested in:<\/strong><\/p>\n\n\n\n
Creating a Database and Tables in PostgreSQL: Learning PostgreSQL with Grant<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
The CREATE TABLE<\/code> command in PostgreSQL<\/h3>\n\n\n\n
The essential command to add a table to a database in PostgreSQL is CREATE TABLE<\/code>. Much of this command’s behavior is similar to other data platforms, but not everything<\/em>, as I’ll explain.<\/strong><\/p>\n\n\n\n
To begin, here’s an example of the basic syntax to create a new table:<\/p>\n\n\n\n
CREATE TABLE antenna\n(antenna_id int GENERATED BY DEFAULT AS IDENTITY,\nantenna_name text NOT NULL\n);<\/pre><\/div>\n\n\n\n
The basic syntax is clear. You supply the name of the table at the start, and the parenthesis are then opened and closed to contain the columns being created. <\/p>\n\n\n\n
\n    
\n\n
Other essential reading:<\/strong><\/p>\n\n\n\n
PostgreSQL Basics: Essential psql Tips and Tricks<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n
In this simple example, we’re creating two parenthesis’: antenna_id<\/code> and antenna_name<\/code>, both within the antenna<\/code> table.  Do note that, with PostgreSQL syntax, all objects are stored as lower case<\/em>. There’s no mixed case (or spaces) unless you use double quotes around the object name at all times – for example, \"AntennaName\"<\/code>. <\/p>\n\n\n\n
However, do avoid using double quotes. We can instead implement “snake case”, which uses underscores to separate words in object names, making them clearer to understand. Just bear in mind that the object name length limit is 63 bytes (63 characters) unless<\/em> you’re using UTF-8<\/a>, in which case it’s 15-31 chars.<\/p>\n\n\n\n
We’re referencing two data types for these two columns: int<\/code> and text<\/code>. You don’t need to define a size for int<\/code> (integer) because it’s fixed-length, but you do<\/em> for text<\/code>. <\/p>\n\n\n\n
A note on using TOAST<\/code> in PostgreSQL<\/h4>\n\n\n\n
While PostgreSQL does supports fixed-length string columns (such as VARCHAR(50)<\/code> for a variable length column limited to 50 characters), common practice is to simply use text<\/code>, and then let TOAST<\/code> (The Oversized-Attribute Storage Technique)<\/a> deal with the data. <\/strong><\/p>\n\n\n\n
Arguments could be had that this is a poor design choice. However, since it’s so common, I’ve chosen to follow that standard in this guide.<\/p>\n\n\n\n
Back to the walkthrough, and we’ve set up our first column – an IDENTITY<\/code> column. This will be auto-incremented and generated on the fly. I chose to set it as BY DEFAULT<\/code> instead of ALWAYS<\/code> to allow for the need for an IDENTITY INSERT<\/code> in the future.<\/p>\n\n\n\n
To run the query, connect to psql<\/code> and enter psql -d ham_shack -h localhost -U radiodev<\/code> into the command line. Execute the query and then use the \/dt<\/code> meta command to open up a list of tables in the database.<\/p>\n\n\n\n
The output should be:<\/p>\n\n\n\n
           List of tables\n Schema |  Name   | Type  |  Owner   \n--------+---------+-------+----------\n gear   | antenna | table | radiodev<\/pre><\/div>\n\n\n\n
Why schemas are so important<\/h4>\n\n\n\n
Note that, despite not supply a schema name to the table, the table is in the gear<\/code> schema. This is because every table must<\/em> belong to a schema – including all objects, functions, views, etc., within a database. <\/strong><\/p>\n\n\n\n
If a schema is not<\/em> supplied, search_path<\/code> is used to determine where the object is created. Remember from earlier, when we left off with this search_path<\/code>?<\/p>\n\n\n\n
          search_path          \n-------------------------------\n \"$user\", gear, people, public<\/pre><\/div>\n\n\n\n
How this works is similar to trying to find an object: when you create an object, the search_path<\/code> is used. It’ll try to find both a valid schema and<\/em> one you have permissions to create an object for. <\/p>\n\n\n\n
You may also recall that the “$user<\/code>” schema is a default, and is linking to a schema based on the role. However, that schema doesn’t actually exist in our current configuration, so it went to the gear<\/code> schema next. <\/p>\n\n\n\n
And, since radiodev<\/code> is the owner of the database, the role had permission to place the table there. This illustrates how important it is to reference the schema when creating objects within PostgreSQL.<\/p>\n\n\n\n
A more appropriate example would be to create a radio<\/code> table like this:<\/p>\n\n\n\n
CREATE TABLE gear.radio\n(radio_id int GENERATED BY DEFAULT AS IDENTITY,\nradio_name text NOT NULL,\nbrand text,\nPRIMARY KEY (radio_id));<\/pre><\/div>\n\n\n\n
To explain: we<\/em> are supplying the schema now, so there’s no more ambiguity. You’ll note that the column brand<\/code> doesn’t specify if it’s nullable or not. If it’s not<\/em> defined, the column can<\/em> contain NULL<\/code> values. We’ve also created a primary key<\/a> constraint. <\/p>\n\n\n\n
We can leave the name off because PostgreSQL defaults to <table name>_pkey<\/code>, but it can be added if necessary.<\/em><\/p>\n\n\n\n
Let’s create one more table before we move on to ALTER<\/code>:<\/p>\n\n\n\n
CREATE TABLE people.operator\n(operator_id int GENERATED BY DEFAULT AS IDENTITY,\nfirst_name varchar(50),\nlast_name varchar(50),\ncallsign varchar(14) NOT NULL,\nPRIMARY KEY (operator_id));<\/pre><\/div>\n\n\n\n
Nothing of significance to note in this table. I did however decide to illustrate the use of constrained length columns.<\/p>\n\n\n\n
The ALTER TABLE<\/code> command in PostgreSQL<\/h3>\n\n\n\n
Let’s start with adding a primary key to the antenna<\/code> table, since one was left off:<\/p>\n\n\n\n
ALTER TABLE gear.antenna \nADD CONSTRAINT PRIMARY KEY (antenna_id);<\/pre><\/div>\n\n\n\n
The syntax should be straightforward, since it’s the same as what we’ve used when creating tables. Finally, let’s add two more tables to the database:<\/p>\n\n\n\n
-- QSOs, radio contacts\nCREATE TABLE people.contact\n(call_operator_id int REFERENCES people.operator,\nlogged_operator_id int REFERENCES people.operator,\nfreq_mhz NUMERIC(9,6) NOT NULL,\ncontact_date TIMESTAMP NOT NULL,\nradio_id int REFERENCES gear.radio,\nPRIMARY KEY (call_operator_id,logged_operator_id,freq_mhz,contact_date)\n);\n\n--Antennas on radios\nCREATE TABLE IF NOT EXISTS gear.radio_antenna\n(radio_id int REFERENCES gear.radio,\nantenna_id int REFERENCES gear.antenna,\nPRIMARY KEY (radio_id,antenna_id));<\/pre><\/div>\n\n\n\n
These tables show how foreign keys are created on tables in PostgreSQL. You can also use ALTER TABLE<\/code> to add a foreign key. <\/p>\n\n\n\n
Note that we’re able to cross schema for one of the foreign keys – radio_id<\/code> -in the people.contact<\/code> table, as long as we use the appropriate schema<\/em>. We can also create compound primary keys. <\/p>\n\n\n\n
Then, for the last table, I included the very common IF NOT EXISTS<\/code> command. This is way for you to validate that there isn’t already a table in existence when you attempt to create one.<\/p>\n\n\n\n
The DROP TABLE command in PostgreSQL<\/h3>\n\n\n\n
Just as we can CREATE<\/code> and ALTER<\/code> tables in PostgreSQL, we can drop<\/a> them as well. <\/strong><\/p>\n\n\n\n
For example, let’s test this script: DROP TABLE IF EXISTS gear.radio;<\/code> (while keeping the tables we’ve already created).<\/p>\n\n\n\n
Once run, you should receive this error:<\/p>\n\n\n\n
ERROR:  cannot drop table radio because other objects depend on it\nDETAIL:  constraint contact_radio_id_fkey on table contacts depends on table radio\nconstraint radio_antenna_radio_id_fkey on table radio_antenna depends on table radio\nHINT:  Use DROP ... CASCADE to drop the dependent objects too.<\/pre><\/div>\n\n\n\n
<\/a>PostgreSQL protects the data integrity of the database by ensuring you can’t simply drop objects with dependencies. CASCADE<\/code> will drop dependent objects, so exercise extreme caution in its use. You’ll also note that I used IF EXISTS<\/code> to validate that there is a table prior to running the DROP<\/code> command.<\/p>\n\n\n\n
With that, we now have enough tables in place. Next up: adding data!<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Simple Talk is brought to you by Redgate Software<\/h3>\n                
\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                                            Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs<\/h2>\n\n                        
1. Why shouldn't I create a PostgreSQL database as the superuser?<\/h3>\n            
\n                
Using the postgres<\/code> superuser to create databases violates the principle of least privilege and bypasses security constraints. It’s better to create a dedicated role (like radiodev<\/code>) with CREATEDB<\/code> permission so ownership and object permissions cascade cleanly and stay easy to audit.<\/p>\n            <\/div>\n                    
2. What's the difference between a role and a user in PostgreSQL?<\/h3>\n            
\n                
Modern PostgreSQL uses roles for everything. Older versions used the term “users,” which is why CREATE USER<\/code> still exists \u2014 it’s simply an alias for CREATE ROLE<\/code>.<\/p>\n            <\/div>\n                    
3. How does the search_path work in PostgreSQL?<\/h3>\n            
\n                
The search_path<\/code> tells PostgreSQL which schemas to check, in order, when you reference an object without specifying its schema. If a schema isn’t included in the search_path<\/code>, you won’t be able to find or query objects inside it unless you explicitly reference the schema name (e.g., gear.radio<\/code>).<\/p>\n            <\/div>\n                    
4. Can I change a role's search_path permanently?<\/h3>\n            
\n                
Yes. Use ALTER ROLE rolename SET search_path TO \"$user\", schema1, schema2, public;<\/code>. This takes effect on new connections. A plain SET search_path<\/code> only lasts for the current session.<\/p>\n            <\/div>\n                    
5. Why did PostgreSQL block my DROP TABLE command?<\/h3>\n            
\n                
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
PostgreSQL prevents dropping tables that other objects depend on (e.g., foreign keys) to protect data integrity. You’ll need to remove the dependent objects first, or use DROP TABLE ... CASCADE<\/code> \u2014 with caution, since it removes all dependent objects too.<\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
Learn how to create roles, databases, schemas, and tables in PostgreSQL using psql. Step-by-step commands, real output examples, and best practices included.…<\/p>\n","protected":false},"author":221792,"featured_media":105920,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143534],"tags":[4168,4170,159410,158978],"coauthors":[6785],"class_list":["post-112449","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-postgresql","tag-database","tag-database-administration","tag-gettingstartedpostgresgrant","tag-postgresql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112449","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=112449"}],"version-history":[{"count":15,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112449\/revisions"}],"predecessor-version":[{"id":112607,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/112449\/revisions\/112607"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105920"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=112449"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=112449"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=112449"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=112449"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}