Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Insert

When a table is created, it contains no data. The first thing to do before a database can be of much use is to insert data. Data is conceptually inserted one row at a time. Of course you can also insert more than one row, but there is no way to insert less than one row at a time. Even if you know only some column values, a complete row must be created.
Basic Synatx:
INSERT INTO table [ ( column [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) | query }
--To create a new table for use the INSERT command
postgres=# CREATE TABLE products (
product_no integer,
name text,
price numeric
);
--An example command to insert a row would be:
postgres=# INSERT INTO products VALUES (1, 'Cheese', 100);
INSERT 0 1
--view the products table for successfully inserted or not
postgres=# select * from products;
product_no | name | price
------------+--------+-------
1 | Cheese | 100
(1 row)
Both of the following commands have the same effect as the one below,Many users consider it good practice to always list the column names.
postgres=#
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

postgres=# select * from products;
product_no | name | price
------------+--------+-------
1 | Cheese | 100
1 | Cheese | 9.99
1 | Cheese | 9.99
(3 rows
--If you don't have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values. For example,
postgres=#
INSERT INTO products (product_no, name) VALUES (2, 'bargar');
INSERT INTO products VALUES (3, 'juice');
--see the inserted values
postgres=# select * from products;                                      
product_no | name | price
------------+--------+-------
1 | Cheese | 100
1 | Cheese | 9.99
1 | Cheese | 9.99
2 | bargar |
3 | juice |
(5 rows)
--The second form is a PostgreSQL extension. It fills the columns from the left with as many values as are given, and the rest will be defaulted.For clarity, you can also request default values explicitly, for individual columns or for the entire row:
INSERT INTO products (product_no, name, price) VALUES (4, 'cake', DEFAULT);
INSERT INTO products DEFAULT VALUES;

postgres=# select * from products;
product_no | name | price
------------+--------+-------
1 | Cheese | 100
1 | Cheese | 9.99
1 | Cheese | 9.99
2 | bargar |
3 | juice |
4 | cake |
| |
  
--This example inserts some rows into table "products" from a table "Item" with the same column layout as Products:
--item table list
postgres=# select * from item;
item_no | name | cost
---------+--------+------
1 | laptop | 20
2 | pen | 10
3 | paper | 8
(3 rows)
--Now insert into products from item table 
postgres=# INSERT INTO products SELECT * FROM Item WHERE cost > 8;
INSERT 0 2

postgres=# select * from products;
product_no | name | price
------------+--------+-------
1 | Cheese | 100
1 | Cheese | 9.99
1 | Cheese | 9.99
2 | bargar |
3 | juice |
4 | cake |
| |
1 | laptop | 20
2 | pen | 10
(9 rows)
postgres=# \d demo
Table "benz2.demo"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
name | character varying(20) |
--insert multiple rows
postgres=# INSERT INTO products (product_no, name, price) VALUES
(5, 'nijam', 1000),
(6, 'benz', 6000),
(7, 'jam', 3456);

postgres=# select * from products;
product_no | name | price
------------+--------+-------
1 | Cheese | 100
1 | Cheese | 9.99
1 | Cheese | 9.99
2 | bargar |
3 | juice |
4 | cake |
| |
1 | laptop | 20
2 | pen | 10
5 | nijam | 1000
6 | benz | 6000
7 | jam | 3456

(12 rows)
To do "bulk loads", that is, inserting a lot of data, take a look at the COPY command. It is not as flexible as the INSERT command




Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

vacuumlo - removing large objects orphans from a database PostgreSQL