Part-22 Data Types : CHAR | VARCHAR | TEXT | NUMERIC | NaN | Integer | SERIAL (Auto-increment)

Boolen Type : When we required only single bite data like true or false so we can use 0 for false and 1 true .
 

Ex:1

#create table stock_availbel
(
producat_id int primary key,
avaible boolean not null
)

#insert into stock_availbel (producat_id,avaible)
values (,'Yes'),(2,'True'),(3,'False'),(4,'True'),(5,'0'),(6,'1')  

 Need to change default vale in column use it :

#alter table stock_availbel ALTER COLUMN avaible set default true;

#insert into stock_availbel (producat_id)  values (7)  

Note :- it will take default value true
 

PostgreSQL Character Types: CHAR, VARCHAR, and TEXT

 

• CHAR is fixed-length character type while the VARCHAR and TEXT are varying length character types.
• VARCHAR(n) if you want to validate the length of the string (n) before inserting into or updating to a column.
• VARCHAR (without the length specifier) and TEXT are equivalent.

 

#CHAR(n) and VARCHAR(n) can store up to n characters.

 CREATE TABLE character_test (
    id serial PRIMARY KEY,
    char_val CHAR (1),
    var_val VARCHAR (10),
    text_val TEXT
);

insert into character_test (char_val,var_val,text_val) 

values  ('A','ABCDEFGIJK','ABCDEFGHIJKLMNOPQRSTUVWXYZ')

select * from character_test ;

 

NUMERIC TYPE :

DROP TABLE IF EXISTS items;

CREATE TABLE items (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price NUMERIC(5,2)
);

INSERT INTO items (name, price)
VALUES ('Phone',600.215), ('Tablet',600.214);
 

#select * from items ;
NOTE :We have create table declare price value column is 2, PostgreSQL automatically rounds of value 600.215 up to 600.22 and rounds the value 600.214 down to 600.21 .

NUMERIC type and NaN :

UPDATE items SET price = 'NaN'  WHERE id = 1 ;

2;"Tablet";500.21
1;"Phone";NaN
 

Typically, the NaN is not equal to any number including itself. It means that the expression NaN = NaN returns false.

NOTE :However, two NaN values are equal and NaN is greater than other numbers. This implementation allows PostgreSQL to sort NUMERIC values and use them in tree-based indexes.

Integer Data Types :

 You can use one of the following integer types: SMALLINT :- 2 Byte, INTEGER :4 Byte, and BIGINT :8 Byte.

CREATE TABLE Page_details (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR (255) NOT NULL,
    pages SMALLINT NOT NULL CHECK (pages > 0)
);

#insert into Page_details (title,pages)values ('Hindi',200)  ;

#select * from Page_details ;

SERIAL - It is Auto-increment Column

SMALLSERIAL : 2 byte| SERIAL : 4 byte| BIGSERIAL : 8 bytes

drop table if exists books;
CREATE TABLE books(
   id SERIAL PRIMARY KEY,
   name VARCHAR NOT NULL
);

INSERT INTO books (name) VALUES ('English');

select * from books

Output : 1;"English"