PostgreSQL – How to Create a Table

ByAdmin

Below is the syntax to create a table in PostgreSQL.

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

where table_name is the name you want to give to the new table.

and column1, column2, etc.: specifies the names of the columns you want to create in the table.

and datatype1, datatype2, etc.: specifies the data types for each column.

Let’s say you want to create a simple table named employees with columns for id, first_name, last_name, and email:

CREATE TABLE employees 
(id SERIAL PRIMARY KEY, 
first_name VARCHAR(50), 
last_name VARCHAR(50), 
email VARCHAR(100) );

  • The id column is given the data type SERIAL, which is an auto-incrementing integer.
  • The first_name and last_name columns are given the data type VARCHAR (variable-length character strings) with a maximum length of 50 characters.
  • The email column is also given the data type VARCHAR, with a maximum length of 100 characters.

you can create a table with constraints:

CREATE TABLE orders 
( order_id SERIAL PRIMARY KEY, 
   customer_id INT, 
   order_date DATE, 
   total_amount DECIMAL(10, 2), 
   CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) 
);

Here,

  • The order_id column is the primary key of the table.
  • The customer_id column is a foreign key that references the customer_id column in the customers table.
  • The order_date column holds dates.
  • The total_amount column holds decimal numbers with a precision of 10 and a scale of 2.

Similar Posts

PostgreSQL – How to Grant Superuser Privileges

ByAdmin

Superusers have full control over the database server and can perform any administrative task. Granting superuser privileges to a user should be done carefully, as it comes with significant power and responsibility. To grant superuser privileges to a user in PostgreSQL, follow these steps: Option 1: During PostgreSQL Installation Option 2: Using an Existing Superuser…

PostgreSQL – How to Rename a Sequence

ByAdmin

You can rename a sequence using the ALTER SEQUENCE statement with the RENAME TO clause in PostgreSQL database. Here’s the basic syntax for renaming a sequence: ALTER SEQUENCE old_sequence_name RENAME TO new_sequence_name; where old_sequence_name is the current name of the sequence you want to rename, and new_sequence_name is the new name you want to assign…

PostgreSQL – How to Alter Default Privileges of A Schema

ByAdmin

You can use the ALTER DEFAULT PRIVILEGES statement in PostgreSQL database to set default privileges that will be applied to newly created objects in a schema. This is a powerful way to control access and permissions for objects created by a specific user or role in a particular schema. Here’s the basic syntax for using…

PostgreSQL – How to Revoke Superuser Privileges

ByAdmin

To revoke superuser privileges from a user in PostgreSQL, you’ll need to connect to the PostgreSQL database server as a superuser, typically the “postgres” user. Here are the steps to revoke superuser privileges: Log in as the PostgreSQL superuser: Open a terminal and log in as the PostgreSQL superuser, usually named “postgres.” You can do…