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 List All Views

ByAdmin

To list views in a PostgreSQL database, you can use SQL queries to query the system catalog tables. Specifically, you can query the pg_views catalog table to retrieve information about the views in the database. Here’s how you can do it: SELECT table_name FROM information_schema.views WHERE table_schema = ‘public’; where This query will return a…

PostgreSQL – How to Get Month From a Date

ByAdmin

To extract the month from a date in PostgreSQL, you can use the EXTRACT function. Here’s an example: SELECT EXTRACT(MONTH FROM your_date_column) AS month FROM your_table; where your_date_column is the name of your date column and your_table is the name of your table. In this query, EXTRACT(MONTH FROM your_date_column) is used to extract the month…

PostgreSQL – How to Get Month Name From a Date

ByAdmin

To get the month name from a date in PostgreSQL, you can use the TO_CHAR function with the appropriate format pattern. Here’s an example: SELECT TO_CHAR(your_date_column, ‘Month’) AS month_name FROM your_table; Replace your_date_column with the name of your date column and your_table with the name of your table. In this query, TO_CHAR is used to…

PostgreSQL – How to Update Multiple Columns

ByAdmin

To update multiple columns in a PostgreSQL table, you can use the UPDATE statement. Below is the basic syntax for updating multiple columns: UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; where Here’s an example of how to update multiple columns in a PostgreSQL table: Suppose you have a table named…