Tables with the same names

Feature Suggestions
1

I would like to create few tables with the same name. I use ‘as’ keyword to give a reference uniqueness, however even with that, I’m getting error:

Table name “table name” already exists in schema public.

Would be nice to treat ‘as’ the same as in SQL and allows duplicates in display name of table

2

Hi @rplaza ,

The purpose of the as keyword is for shorter table name when creating foreign keys.
Take the following snippet as an example:

Table follows as f {
  following_user_id integer
  followed_user_id integer
  created_at timestamp
}

Table users as u {
  id integer [primary key]
  username varchar
  role varchar
  created_at timestamp
}

Table posts {
  id integer [primary key]
  title varchar
  body text [note: 'Content of the post']
  user_id integer [not null]
  status varchar
  created_at timestamp
}

Ref user_posts: posts.user_id > u.id

Ref: u.id < f.following_user_id

Ref: u.id < f.followed_user_id

Here, the tables users and follows are aliased as u and f for the foreign key definitions to just use 1 character for the table names.

Also, I tried the following snippet in PostgreSQL and it raise the error table name "e" specified more than once:

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  deptId INTEGER NOT NULL
);

INSERT INTO EMPLOYEE VALUES (1, 'John', 1);
INSERT INTO EMPLOYEE VALUES (2, 'Jane', 2);

CREATE TABLE DEPARTMENT (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

INSERT INTO DEPARTMENT VALUES (1, 'HR');
INSERT INTO DEPARTMENT VALUES (2, 'Sales');

SELECT * FROM EMPLOYEE as e, DEPARTMENT as e WHERE e.deptId = 1 AND e.id = 1;

If having same name for tables work for you, could you provide us a sample snippet for that case, and also the information about the system that you are using, including:

  • The SQL dialect and it’s version (PostgreSQL 16.6, MySQL 8.4, …)
  • Your operating system (Windows 10, Ubuntu 22.04, …)
3

I’m trying to do like this:

Table users as u1 {
  id integer [primary key]
  username varchar
  role varchar
  created_at timestamp
}

Table users as u2 {
  id integer [primary key]
  username varchar
  role varchar
  created_at timestamp
}

Table posts {
  id integer [primary key]
  title varchar
  body text [note: 'Content of the post']
  user_id integer [not null]
  status varchar
  created_at timestamp
}

Table follows as f {
  following_user_id integer
  followed_user_id integer
  created_at timestamp
}

Ref user_posts: posts.user_id > u1.id

Ref: u2.id < f.following_user_id

Ref: u2.id < f.followed_user_id
4

Hi @rplaza ,

Thank you for your reply.

DBML doesn’t allow defining duplicated table names. In your case, please use the schema name to distinguish them.

For example, you can add the schema name for the table users as u2

Table private.users as u2 {
  id integer [primary key]
  username varchar
  role varchar
  created_at timestamp
}

I hope this helps.