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.
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,
- With data
- Without data
- 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:
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.”