PostgreSQL CREATE TABLE AS with examples.

I need to export this data to a file, make a new table, then import that data into the new table… Boring. Let’s use CREATE TABLE AS syntax in PostgreSQL to easily knock out tasks like this.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

igor-ovsyannykov-223090-unsplash

OS and Database:

  • Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
  • PostgreSQL 10.4

Suppose we want to copy this table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
hr=> SELECT * FROM jobs;
   job_id   |            job_title            | min_salary | max_salary
------------+---------------------------------+------------+------------
 AD_PRES    | President                       |      20000 |      40000
 AD_VP      | Administration Vice President   |      15000 |      30000
 AD_ASST    | Administration Assistant        |       3000 |       6000
 FI_MGR     | Finance Manager                 |       8200 |      16000
 FI_ACCOUNT | Accountant                      |       4200 |       9000
 AC_MGR     | Accounting Manager              |       8200 |      16000
 AC_ACCOUNT | Public Accountant               |       4200 |       9000
 SA_MAN     | Sales Manager                   |      10000 |      20000
 SA_REP     | Sales Representative            |       6000 |      12000
 PU_MAN     | Purchasing Manager              |       8000 |      15000
 PU_CLERK   | Purchasing Clerk                |       2500 |       5500
 ST_MAN     | Stock Manager                   |       5500 |       8500
 ST_CLERK   | Stock Clerk                     |       2000 |       5000
 SH_CLERK   | Shipping Clerk                  |       2500 |       5500
 IT_PROG    | Programmer                      |       4000 |      10000
 MK_MAN     | Marketing Manager               |       9000 |      15000
 MK_REP     | Marketing Representative        |       4000 |       9000
 HR_REP     | Human Resources Representative  |       4000 |       9000
 PR_REP     | Public Relations Representative |       4500 |      10500
(19 rows)

Three variations we could choose,

  1. With data
  2. Without data
  3. Specific query results.

I’ll cover all three.
Let’s dig in shall we?


Table Only, No Data

Copying just the table structure without data is relatively straight-forward.

1
2
3
4
hr=> CREATE TABLE jobs_structure
     AS TABLE jobs
     WITH NO DATA;
CREATE TABLE AS

We are returned the CREATE TABLE AS tag upon completion. Here’s the structure of the newly created jobs_structure table:

1
2
3
4
5
6
7
8
hr=> \d jobs_structure;
                    Table "public.jobs_structure"
   Column   |         Type          | Collation | Nullable | Default
------------+-----------------------+-----------+----------+---------
 job_id     | character varying(10) |           |          |
 job_title  | character varying(35) |           |          |
 min_salary | numeric(6,0)          |           |          |
 max_salary | numeric(6,0)          |           |          |

And a count of present records:

hr=> SELECT COUNT(*) FROM jobs_structure;
 count
-------
     0
(1 row)

No records present. Easy peasy.


How About Some Data With That Table?

To create and populate a table with data, basically all you need to do is remove (or not include) the WITH NO DATA clause.

Here’s a look at the regions table to recreate:

1
2
3
4
5
6
7
8
hr=> table regions;
 region_id |      region_name      
-----------+------------------------
         1 | Europe
         2 | Americas
         3 | Asia
         4 | Middle East and Africa
(4 rows)

Now, to make a similar table, with data, we use the command:

1
2
3
hr=> CREATE TABLE regions_data
     AS TABLE regions;
SELECT 4

* Note: You can include,

1
WITH DATA

clause at the end of the command for readability if desired. However, the default is to copy data over to the new table so it is not necessary.

Table regions_data description,

1
2
3
4
5
6
hr=> \d regions_data;
                     Table "public.regions_data"
   Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
 region_id   | integer               |           |          |
 region_name | character varying(25) |           |          |

Let’s check the data too:

1
2
3
4
5
6
7
8
hr=> SELECT * FROM regions_data;
 region_id |      region_name      
-----------+------------------------
         1 | Europe
         2 | Americas
         3 | Asia
         4 | Middle East and Africa
(4 rows)

Sweet. The table structure and data was copied over from the original regions table.


To receive email notifications (Never spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.


Just A Few Columns

Here’s the countries table we are interested in.

1
2
3
4
5
6
7
hr=> \d countries;
                       Table "public.countries"
    Column    |         Type          | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
 country_id   | character(2)          |           | not null |
 country_name | character varying(40) |           |          |
 region_id    | integer               |           |          |

Suppose you know first-hand the region_id value of 1 is of interest and you want just the countries and that id in a table all its own.

No problem.

1
2
3
4
hr=> CREATE TABLE countries_reg_one AS
     SELECT country_name, region_id
     FROM countries WHERE region_id = 1;
SELECT 8

Looking at the new countries_reg_one table:

1
2
3
4
5
6
7
8
9
10
11
12
hr=> SELECT * FROM countries_reg_one;
  country_name  | region_id
----------------+-----------
 Italy          |         1
 United Kingdom |         1
 France         |         1
 Germany        |         1
 Switzerland    |         1
 Netherlands    |         1
 Denmark        |         1
 Belgium        |         1
(8 rows)

Only columns country_name and region_id along with data qualified by the WHERE clause made it over to the new table.


Alternate Column Names

Custom column naming is available should you need it.
Have a look.

1
2
3
4
5
hr=> CREATE TABLE countries_reg1_cust (some_name,some_region)
     AS
     SELECT country_name, region_id FROM countries
     WHERE region_id = 1;
SELECT 8

Here I named the two columns some_name and some_region yet they are of the same data type as the original columns, which is the default.
Unless you override them, giving an explicit list of new column names as in this example.
So did that work?

Check out the column names here,

1
2
3
4
5
6
hr=> \d countries_reg1_cust;
                  Table "public.countries_reg1_cust"
   Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
 some_name   | character varying(40) |           |          |
 some_region | integer               |           |          |

And the data therein,

1
2
3
4
5
6
7
8
9
10
11
12
hr=> SELECT * FROM countries_reg1_cust;
   some_name    | some_region
----------------+-------------
 Italy          |           1
 United Kingdom |           1
 France         |           1
 Germany        |           1
 Switzerland    |           1
 Netherlands    |           1
 Denmark        |           1
 Belgium        |           1
(8 rows)

Custom named columns and data present. Just as needed.


Closing Thoughts.

I hope through this blog post, you can discover uses for CREATE TABLE AS syntax where needed. However, it should be noted, these tables are static. Meaning that any changes occurring in the source table, will not be applied in these tables. They are merely a snapshot.

As I continue my journey on the path of SQL Developer, I would love to hear any thoughts and suggestions from those experienced with CREATE TABLE AS syntax and uses you have found beneficial.
Feel free to leave any comments below.
Be sure and visit the stellar online PostgreSQL 10 Documentation for any related questions.


A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.


Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.


Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

One thought on “PostgreSQL CREATE TABLE AS with examples.

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.