Course Banner

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:

  1. A successful connection exists between the database server and pgAdmin from the previous activities.
  2. You have completed the "Introduction to SQL" chapter reading assignment.
  3. 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

  1. 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.
  2. Expand the "Databases" item.
  3. Expand the 340 database item (you named this item).
  4. Expand the "Schemas" item.
  5. Expand the "public" schema item.
  6. Right-click on the "Types" item, select "Create > Type...".
  7. Type account_type in the "Name" text box. Leave the other settings as they are.
    Screenshot of the pgAdmin Create Type dialog box
  8. Click "Definition" in the top bar.
    Screenshot of the pgAdmin Create Type definition dialog box
  9. Change the "Type" to "Enumeration".
  10. Click the "+" to the right of "Enumeration type".
  11. Type Client in the text box.
  12. Click the "+" to the right of "Enumeration type".
  13. Type Employee in the text box.
  14. Click the "+" to the right of "Enumeration type".
  15. Type Admin in the text box.
  16. Click "SQL" in the top bar. The image is only an example. Your code should match the values you typed.
    Screenshot of the pgAdmin Create Type SQL dialog box
  17. You'll see the SQL code that pgAdmin wrote behind the scene that will execute when you click the "Save" button.
  18. You will want to save this SQL code.
    1. In VSC, click the database folder.
    2. Click the "New file..." icon.
    3. Name the file meaningfully (e.g. db-sql-code.sql)
    4. Save the file.
    5. Copy the Create - Type SQL code from the pgAdmin dialog box.
    6. Paste the code into the new file in VSC.
    7. Save and close the VSC file.
  19. Click "Save" in the Create - Type dialog box.
  20. 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:

  1. Right-click on the Render server object in the Servers panel of pgAdmin.
  2. Select "Disconnect from Server".
  3. You're done.