learn-postgresql

Tables and Schemas in PostgreSQL

Tables and schemas are fundamental components of a PostgreSQL database that allow you to organize and structure your data. This guide provides an overview of creating tables, dropping tables, and working with schemas in PostgreSQL.

Table of Contents

Create Table

To create a table in PostgreSQL, you can use the CREATE TABLE command followed by the table name and column definitions. Here’s an example:

CREATE TABLE table_name (
  column1 datatype1,
  column2 datatype2,
  ...
);

For instance, to create a table named “employees” with columns for “id”, “name”, and “age”, you can use the following command:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  age INTEGER
);

Drop Table

Note: Dropping a table permanently deletes the table and its associated data. Exercise caution while using this command.

To drop or delete a table in PostgreSQL, you can use the DROP TABLE command. This command removes the specified table from the database. Here’s an example:

DROP TABLE table_name;

For example, to drop the “employees” table:

DROP TABLE employees;

Schemas

Schemas provide a way to organize tables and other database objects into logical groups within a PostgreSQL database. You can think of a schema as a namespace that contains related database objects. Schemas are useful for managing and organizing large databases with multiple tables and objects.

To create a schema, you can use the CREATE SCHEMA command followed by the schema name. Here’s an example:

CREATE SCHEMA schema_name;

For example, to create a schema named “public”, you can use the following command:

CREATE SCHEMA public;

To create a table within a specific schema, you can include the schema name as a prefix when creating the table. For example:

CREATE TABLE schema_name.table_name (
  column1 datatype1,
  column2 datatype2,
  ...
);

To drop a schema, you can use the DROP SCHEMA command. This command removes the specified schema and all its contained objects. Here’s an example:

DROP SCHEMA schema_name;

Tables and schemas are essential components in PostgreSQL for organizing and structuring your data. With the ability to create tables, drop tables, and work with schemas, you can effectively manage your database objects and maintain a well-structured database.

This site is open source. Improve this page.