In PostgreSQL, “ALTER” and “UPDATE” are two different SQL commands used to modify data in a database.
“ALTER” is used to modify the structure of the database, while “UPDATE” is used to modify the data stored within the database.
Update:
The UPDATE command plays a crucial role in modifying and updating data within PostgreSQL tables.
|
1 2 3 4 5 6 7 8 9 |
college=# UPDATE REPORT SET DATE = DEFAULT, HUMIDITY= 21 WHERE PLACE='VZG'; UPDATE 1 college=# SELECT * FROM REPORT; place | temp_lo | temp_hi | date | humidity ------------------------------------------------------ HYD | 32 | 43 | 2020-07-19 | 23 BZA | 32 | 43 | 2023-05-09 | 23 VZG | 32 | 43 | 2023-05-10 | 21 (3 rows) |
Views:
They are particularly useful for creating reusable query components and presenting data in a more convenient format.
|
1 2 3 4 5 6 7 8 9 10 |
college=# CREATE VIEW place_humidity AS college-# SELECT place, humidity FROM report; CREATE VIEW college=# select * from place_humidity; place | humidity -------------------- BZA | 23 VZG | 21 HYD | 24 (3 rows) |
View can be DROPPED by using following command.
|
1 2 3 4 5 6 7 8 9 10 |
college=# DROP VIEW place_humidity; DROP VIEW college=# \d List of relations Schema | Name | Type | Owner ------------------------------------------------------ public | report | table | postgres public | temp | table | postgres public | weather | table | postgres (3 rows) |
Temporary Table:
Temporary tables provide a way to store and manipulate data within a session or a transaction.
|
1 2 3 4 5 6 |
college=# CREATE TEMP TABLE student1(stu_name varchar); CREATE TABLE college=# select * from student1; stu_name ------------ (0 rows) |
A Temporary table is created in college database as student1.
Let’s exit and reconnect to the database to verify still the table exists.
|
1 2 3 4 5 |
postgres=# \c college You are now connected to database "college" as user "postgres". college=# select * from student1; ERROR: relation "student1" does not exist LINE 1: select * from student1; |
Temporary tables are only visible to the session that creates them and are automatically dropped at the end of the session or transaction.
Column Alias:
Column aliases provide a way to assign alternative names to the columns returned in a query result.
|
1 2 3 4 5 6 7 |
college=# select place AS city, date, humidity from report; city | date | humidity -------------------------------------- BZA | 2023-05-09 | 23 VZG | 2023-05-10 | 21 HYD | 2020-07-19 | 24 (3 rows) |
Schema:
A schema in PostgreSQL is a named container that holds a collection of database objects, such as tables, views, Indexes, functions, and more. It acts as a logical unit that helps organize and categorize these objects within a database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
college=# CREATE SCHEMA myschema; CREATE SCHEMA college=# create table myschema.company( college (# ID INT NOT NULL, college (# NAME VARCHAR (20) NOT NULL, college (# AGE INT NOT NULL, college (# ADDRESS CHAR (25), college (# SALARY DECIMAL (18, 2), college (# PRIMARY KEY (ID) college (# ); CREATE TABLE college=# SELECT * FROM myschema.company; id | name | age | address | salary --------------------------------------- (0 rows) |
When you create a table without specifying a schema, it is automatically created in the public schema. It is accessible to all users by default.
|
1 2 3 4 5 6 7 8 9 10 11 |
college=# \d report Table "public.report" Column | Type | Collation | Nullable | Default ------------------------------------------------------------------------ place | character varying (80) | | not null | temp_lo | integer | | | temp_hi | integer | | | date | date | | | CURRENT_DATE humidity | real Indexes: "place_pk" PRIMARY KEY, btree (place) |
Table Space:
Creating a Tablespace:
To create a tablespace named “my_tablespace” at the location ‘C:\pg_tbsp2’, you Can use the following SQL command:
|
1 2 3 4 5 6 7 8 9 10 |
postgres=# CREATE TABLESPACE my_tablespace LOCATION 'C:\pg_tbsp2'; CREATE TABLESPACE postgres=# \db List of tablespaces Name | Owner | Location ------------------------------------------- my_tablespace | postgres | C:\pg_tbsp2 pg_default | postgres | pg_global | postgres | (3 rows) |
To list the tablespaces in that database use command: ‘\db’.
pg.default – The “pg.default” tablespace is the default location where user data is stored. When no tablespace is created.
pg.global – It contains system catalogs that are shared among multiple databases, such as system tables and Indexes.
Creating a Table in the Tablespace:
To create a table named “my_table” within the “my_tablespace” tablespace. Run the following command:
|
1 2 3 4 5 |
postgres=# CREATE TABLE my_table ( postgres(# id SERIAL PRIMARY KEY, postgres(# name VARCHAR(50) postgres(# ) TABLESPACE my_tablespace; CREATE TABLE |
If you want to move the “my table” table to a different tablespace, you can use the “ALTER TABLE” statement.
ALTER TABLE my_table SET TABLESPACE new_tablespace;
| 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
Note: Please test scripts in Non Prod before trying in Production.
Loading...
