CREATING A NEW TABLE
You can create a new table by specifying the table name, along with all column names and their types.
Syntax for table creation:
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
columnN datatype,
PRIMARY KEY (one or more columns)
);
An example for creating table on weather report.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
college=# CREATE TABLE weather ( college (# city varchar(80), college (# temp_lo int, -- low temperature college (# temp_hi int, -- high temperature college (# prcp real, -- precipitation college (# date date college(# ); CREATE TABLE college=# \dt List of relations Schema | Name | Type | Owner ------------------------------------- public | students | table | postgres public | weather | table | postgres (2 rows) |
Two dashes (“–”) introduce comments. Whatever follows them is ignored up to the end of the line.
DATA TYPES:
varchar (80) specifies a data type that can store arbitrary character strings up to 80 characters in length.
int is the normal integer type.
real is a type for storing single precision floating-point numbers.
date should be self-explanatory. (Yes, the column of type date is also named date. This might be convenient or confusing — you choose.)
PostgreSQL supports the standard SQL types int, small int, real, double precision, char(N), varchar(N), date, time, timestamp, and interval, as well as other types of general utility and a rich set of geometric types.
PostgreSQL can be customized with an arbitrary number of user-defined data types. Consequently, type names are not key words in the syntax, except where required to support special cases in the SQL standard.
Describe a table:
To know the information of a particular table i.e., meta data. Run the describe table command.
|
1 2 3 4 5 6 7 8 9 |
college=# \d weather; Table "public.weather" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ city | character varying (80) | | | temp_lo | integer | | | temp_hi | integer | | | prcp | real | | | date | date | | | |
Inserting values into table:
To add rows to a table we use insert command Here we can add single row or multiple rows at a time to a particular table.
|
1 2 3 4 |
college=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Hyd', 33, 45, 24, current_date); INSERT 0 1 college=# INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES ('Bza', 38, 42, 25, current_date); INSERT 0 1 |
Selecting data from the Table:
To display contents in the table run the select command. To display all the contents in a table use *. Here * select all the rows in the table.
|
1 2 3 4 5 6 |
college=# select * from weather; city | temp_lo | temp_hi | prcp | date ------------------------------------------------ Hyd | 33 | 45 | 24 | 2023-05-06 Bza | 38 |42 | 25 | 2023-05-06 (2 rows) |
Altering a table:
- Adding a column:
To add a new column to a particular table we use ALTER command. Run the following command to ADD column.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
college=# ALTER TABLE weather college-# ADD COLUMN Humidity int; ALTER TABLE college=# \d weather; Table "public.weather" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ city | character varying (80) | | | temp_lo | integer | | | temp_hi | integer | | | prcp | real | | | date | date | | | humidity | integer | | | |
Modifying column:
To change the Data type of column Run the following ALTER command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
college=# ALTER TABLE weather college-# ALTER COLUMN humidity TYPE real; ALTER TABLE college=# \d weather; Table "public.weather" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ city |character varying (80) | | | temp_lo | integer | | | temp_hi | integer | | | prcp | real | | | date | date | | | humidity | real | | | |
Rename column:
To change the name of a column Run the following command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
college=# ALTER TABLE weather college-# RENAME COLUMN city TO place; ALTER TABLE college=# \d weather; Table "public.weather" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ city | character varying (80) | | | temp_lo | integer | | | temp_hi | integer | | | prcp | real | | | date | date | | | humidity | real | | | |
Dropping a column:
To DROP a particular column in table Run the following command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
college=# ALTER TABLE weather college-# DROP COLUMN prcp; ALTER TABLE college=# \d weather; Column place Table "public.weather" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ city | character varying (80) | | | temp_lo | integer | | | temp_hi | integer | | | date | date | | | humidity | real | | | |
Copying a table:
To create a copy of an existing table Run the following command to create a table copy.
|
1 2 3 4 5 6 7 8 9 10 11 |
college=# CREATE TABLE report AS TABLE weather; SELECT 4 college=# \d report; Table "public.report" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------ city | character varying (80) | | | temp_lo | integer | | | temp_hi | integer | | | date | date | | | humidity | real | | | |
Drop, Delete and Truncate:
Dropping a table:
To DROP a table i.e., permanently remove table from the database. Run the following command.
|
1 2 3 4 5 6 7 |
college=# DROP TABLE students; DROP TABLE college=# \dt List of relations Schema | Name | Type | Owner --------------------------------------------- public | weather | table | postgres (1 row) |
Delete a table:
To remove particular object or row we use DELETE command.
|
1 2 3 4 5 6 7 |
college=# DELETE FROM weather WHERE place ='Bza'; DELETE 1 college=# select * from weather; place | temp_lo | temp_hi | date | humidity ------------------------------------------------- Hyd | 33 | 45 |2023-05-06 (3 rows) |
Truncate:
To remove all the records in a table we use TRUNCATE command.
|
1 2 3 4 5 6 |
college=# TRUNCATE TABLE report; TRUNCATE TABLE college=# select * from report; place temp_lo | temp_hi | date | humidity ------------------------------------------- (0 rows) |
| Author : Prudhvi Teja |
LinkedIn : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
