PostgreSQL Type
Introduction
Perhaps you have worked with a database previously and created an enumerated (ENUM) field. This is a field where you pre-set the possible values that the field can hold. When data is entered into the field, it will not allow any value if it does not exist in the list of possibilities. Unfortunately, PostgreSQL does not support this field type. Therefore, we must create it as a separate "type" definition in our database. It does give us an opportunity to start working with our database.
Assumptions
Before continuing, the following are assumed to be true:
- A successful connection exists between the database server and pgAdmin from the previous activities.
- You have completed the "Introduction to SQL" chapter reading assignment.
- You have completed the "PostgreSQL Crash Course" activity.
Video Overview
The video provides a general overview of the activity, but does not contain the detail needed to complete each process. Watch the video to obtain a general idea, but follow the written steps to complete the activity. This is the Transcript of the video.
Create the Type
- Open the connection to the Render.com database from pgAdmin.
- Expand the Servers list (if needed)
- Right-click on the Render connection
- Select "Connect to Server"
- Be prepared to supply the password from the database connection information panel in the Render dashboard.
- Expand the "Databases" item.
- Expand the 340 database item (you named this item).
- Expand the "Schemas" item.
- Expand the "public" schema item.
- Right-click on the "Types" item, select "Create > Type...".
- Type
account_type
in the "Name" text box. Leave the other settings as they are. - Click "Definition" in the top bar.
- Change the "Type" to "Enumeration".
- Click the "+" to the right of "Enumeration type".
- Type
Client
in the text box. - Click the "+" to the right of "Enumeration type".
- Type
Employee
in the text box. - Click the "+" to the right of "Enumeration type".
- Type
Admin
in the text box. - Click "SQL" in the top bar. The image is only an example. Your code should match the values you typed.
- You'll see the SQL code that pgAdmin wrote behind the scene that will execute when you click the "Save" button.
- You will want to save this SQL code.
- In VSC, click the database folder.
- Click the "New file..." icon.
- Name the file meaningfully (e.g. db-sql-code.sql)
- Save the file.
- Copy the Create - Type SQL code from the pgAdmin dialog box.
- Paste the code into the new file in VSC.
- Save and close the VSC file.
- Click "Save" in the Create - Type dialog box.
- If successful, the "account_type" type will now appear beneath the "Types" item in the database.
Conclusion
With the type item created, it can now be used when a table is created in the database.
Close the Connection
As stated in the previous activity, it is recommended that when you are done with your interaction between pgAdmin and the database server that the connection be closed. To do so:
- Right-click on the Render server object in the Servers panel of pgAdmin.
- Select "Disconnect from Server".
- You're done.