CODEWITHSUNDEEP
sqlpostgresql
ALSDV
ALSDV

Reputation: 1

CREATE TABLE using data from other tables

I am trying to create a table using all data from other tables despite one column. However, I am having trouble adding my new column of data. I have:

CREATE TABLE category_sales
AS (
SELECT store.store_id, category.category_id, category.name, inventory.inventory_id, film.film_id, film.title, total_rentals
FROM store, 
     category, 
     inventory, 
     film
);

This is coming up with the error total_rentals does not exist, which it does not because I am trying to create it with this statement. My question is how do I make it exist?

Upvotes: 0

Views: 147

Answers (1)

VBoka
VBoka

Reputation: 9083

You can use null or empty string '' as a value you are selecting and then total_rentals as an alias to name that column.

Like this:

CREATE TABLE category_sales
AS 
SELECT store.store_id
       , category.category_id
       , category.name
       , inventory.inventory_id
       , film.film_id
       , film.title
       , null as total_rentals
FROM store, category, inventory, film;

Here is a small demo

Now that you know how to create a table with a select statement and a column that does not exist in any of the tables you are selecting data from now you should check more info about JOIN and how to use implicit join. For example:

CREATE TABLE category_sales
AS 
SELECT store.store_id
       , category.category_id
       , category.name
       , inventory.inventory_id
       , film.film_id
       , film.title
       , null as total_rentals
FROM store
join category on store.store_id = category.store_id 
join inventory on category.inventory_id = inventory.inventory_id 
left join film on category.film_id = film.film_id;

This is just an example and you should know what is the exact and right way to join this tables.

As @Pred commented you can also define exactly what will be the column's data type by casting a null value :

CREATE TABLE category_sales
AS 
SELECT store.store_id
       , category.category_id
       , category.name
       , inventory.inventory_id
       , film.film_id
       , film.title
       , cast(null as int) as total_rentals
FROM store, category, inventory, film;

Upvotes: 1

Related Questions