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 Escape an apostrophe(single quote) within a String

ByAdmin

You can escape a single quote (also known as an apostrophe) within a string by using another single quote. This is a common practice in SQL to handle single quotes within string literals. Here’s how you can do it: To represent a single quote within a string, you can double the single quote character (”)….

PostgreSQL – How to Generate a Random Number within a Range

ByAdmin

You can use the RANDOM() function to generate a random floating-point number between 0 and 1 in PostgreSQL database. Here’s the basic syntax for using the RANDOM() function: SELECT RANDOM(); This will return a random number in the range [0, 1), meaning it can be any decimal value between 0 (inclusive) and 1 (exclusive). If…

PostgreSQL – JSON_OBJECT_KEYS Function

ByAdmin

You can use the json_object_keys function to extract the keys (field names) from a JSON object in PostgreSQL database. This function takes a JSON object as its argument and returns a set of text values representing the keys in the JSON object. Here is the basic syntax for the json_object_keys function: sqlCopy code json_object_keys(json_object) where…

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…