Advanced Security - Multiple Parents (PostgreSQL 13)

PHPMaker General Discussion (PHPMaker)
1

I’m testing Multiple Parents using PostgreSQL 13, My Users table uses the following fields:

  • UsersID > I have tried with Bigserial/Integer/BigInt as the type,
  • Username field
  • Password field,
  • ReportsTo > Character Varying (varchar works in MySQL)
  • other fields

What data type would work in PSQL? Any ideas?

2

WABez wrote:

  • UsersID > I have tried with Bigserial/Integer/BigInt as the type

Any one of them is fine.

  • ReportsTo > Character Varying (varchar works in MySQL)

If you don’t use multiple parent user ID, you should use the same type as the user ID field (i.e. integer or big integer). If you use multiple parent user ID, you should use varchar (with sufficient size) because comma separated user ID will be stored. (Assume v2023)

3

Thanks, discovered my mistake. My table is as follows:

CREATE TABLE Users (
    UsersID BIGSERIAL  NOT NULL,
    Username CHARACTER VARYING(30),
    Password CHARACTER VARYING(128),
    ParentID CHARACTER VARYING(512),
    Profile TEXT,
    PRIMARY KEY (UsersUID)
);

I have created a relationship from table Users to table Users (i.e. users_users relationship), and because of the relationship I got the following erros:ERROR: foreign key constraint “users_users” cannot be implemented
DETAIL: Key columns “parentid” and “usersid” are of incompatible types: character varying and bigint.
SQL state: 42804ERROR: foreign key constraint “users_users” cannot be implemented
DETAIL: Key columns “parentid” and “usersid” are of incompatible types: character varying and integer.
SQL state: 42804I removed the relationship and created a “normal” ParentID CHARACTER VARYING(512) field and that solved the issue.