CODEWITHSUNDEEP
sqlpostgresql
taway0282
taway0282

Reputation: 197

Create table using attributes from other tables, as well as new attribute

I have an ER Diagram where I have 2 entities connected by a relationship that has an attribute, that looks a little like this: ERD

I can create a new table with:

CREATE TABLE participate AS (
  SELECT user.id, group.group_id 
  FROM user, group
)

I am unsure how to also add the date attribute.

Upvotes: 1

Views: 856

Answers (2)

user330315
user330315

Reputation:

You can select a dummy value:

CREATE TABLE participate 
AS 
SELECT user.id as user_id, 
       group.group_id, 
       current_date as "date"
FROM "user"
  CROSS JOIN "group";

Note that user and group are reserved keywords. You should avoid using them as table names. But if you do, you have to enclose them in double quotes every time you use that table

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269853

I recommend being explicit with a cross join rather than ,.

You can just add the date:

CREATE TABLE participate AS (
  SELECT u.id, g.group_id, current_datetime as date 
  FROM user u CROSS JOIN
       group g;

USER and GROUP are really bad choices for table names, because the are SQL keywords and reserved words. I usually name tables in the plural, so I would recommend USERS and GROUPS.

Upvotes: 1

Related Questions