If you have an existing database table and you want to copy the complete table (both the column structure, and the row data), you can use the SELECT INTO command:






This performs an implicit CREATE TABLE , which creates a table with the same column names, value types, and row data as the original table. Once the message SELECT is displayed, the statement was successfully performed and a new table was created. You can then perform the same operations on this new table as in any other table that you create.

After having inserted these values, the parser returns the message, INSERT. This means it was successfully inserted into the columns. If you want to view it, use the select * from book command. The table looks like this:

  isbn   |            title            | author_id | cpright_date
 8741205 | The Cat in the Hat          |       110 | 1972-04-29
 2567841 | Little Women                |       231 | 1980-02-15
  214872 | Franklin in the Dark        |       142 | 1986-09-22
  602071 | Goodnight Moon              |       136 | 1991-04-01
  355184 | Bartholomew and the Oobleck |       110 | 1997-02-01
  676883 | The Velveteen Rabbit        |       220 | 1958-02-06
(6 rows)

Note Identifier Values

If you inserted the date as month, day, and year (mm-dd-yyyy), then PostgreSQL changes the format to look like the set format in your system, so when you look at your table after having created it, the date can be formatted differently from the way you entered it in. Therefore, you must be careful when entering days with a value equal to and less than 12 as first in the string of dates. The parser may see it as the month, not as day, or vice versa. Of course, this depends upon the date format set on your system.

You must enter the values for title and cpright_date in quotes because they are string and date formated data types. On the other hand, isbn and author_id can be entered without quotes because they are numeric data types.

You may have also noticed that isbn is entered in lowercase, while it1 was originally created as ISBN. That is because when we created it, we didn't specify ISBN in quotes. If you originally used quotes upon creating the isbn column, then you must have the exact case structure as you first created it. See the identifiers and keywords section for more information.