DATABASE QUERY TECHNIQUES

I am currently studying an open source Database Management System called “Postgre” Apparently; this is the required back-end of our applications as it runs with Java and using the UNIX platform. Its queries are quite straight and most of them conform to the SQL Query Standards. Though there are some additions that would return to a query error if you misused.

As I go along with the database’s documentation, I saw these two database techniques that could simply your life. These INSERT and CREATE techniques will change the conventional way of inserting and creating tables. Here it is:

INSERTING A WHOLE TABLE TO ANOTHER
To insert a whole table into another, you can also use INSERT. Both tables have to contain the same columns with the same name and datatype.

INSERT INTO products (SELECT product_no, name, price FROM products_old);

If the columns in both tables are in the same order you can also replace the column names by an asterisk:

INSERT INTO products (SELECT * FROM products_new);

This is very useful when you want to remerge a temporary modified table with the genuine table which was the base of the temporary one. Simply truncate the genuine table with TRUNCATE TABLE products; and than use the above INSERT statement.

CREATING TABLES FROM OTHER TABLES
The INTO TABLE clause may be used with any valid SELECT query in order to create a new table with the column structure and row data of the returned result set. The syntax for this is as follows:

SELECT select_targets
INTO [ TABLE ] new_table
FROM old_table;

This syntax performs an implicit CREATE TABLE command, creating a table with the same column names, value types, and row data as the result set from the original table. When the message SELECT is returned, you will know that the statement was successfully performed, and the new table created.

booktown=# SELECT * INTO stock_backup
booktown-# FROM stock;
SELECT

The table specified by the INTO clause must not exist, or else an error will be returned. Upon the error, the values of the query will not be inserted and the query will fail. Note that the TABLE keyword, in this query, is an optional noise term.

1 Comment(s)

Comments RSS TrackBack Identifier URI