Why does \dt *. show me a list of relations in psql but \dt not show me a list of tables in my postgreSQL database?

Question

I am working on PostgreSQL version 8.4.20. I cannot upgrade it because of a work issue and existing databases running on it.

I have created a schema that looks like this:

--
-- Name: Bug Tracking; Type: COMMENT; Schema: bugtrackingdb; Owner: aa2_role
--

COMMENT ON DATABASE bugtrackingdb IS 'CCDD Viewer Web Application Bug Tracking DB.';

--- CREATE DATABASE bugtrackingdb;

CREATE SCHEMA bugtracking AUTHORIZATION aa2;

CREATE TABLE bugtracking.issues (
    issueID BIGSERIAL PRIMARY KEY,
    issue VARCHAR(50) NOT NULL 
);

CREATE TABLE bugtracking.users (
    id BIGSERIAL PRIMARY KEY,
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    userName VARCHAR(25) NOT NULL
);

CREATE TABLE bugtracking.openDate (
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    dateOpened DATE NOT NULL
);

CREATE TABLE bugtracking.closeDate (
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    dateClosed DATE NOT NULL
);

CREATE TABLE bugtracking.priority (
    priorityID BIGSERIAL PRIMARY KEY,
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    priority VARCHAR(15) NOT NULL
);

CREATE TABLE bugtracking.status (
    statusID BIGSERIAL PRIMARY KEY,
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    status VARCHAR(20) NOT NULL
);

CREATE TABLE bugtracking.comments (
    commentID BIGSERIAL PRIMARY KEY,
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    description TEXT NOT NULL
);

I am still new to postgreSQL and I am trying to figure it out, although I am familiar with how databases work, I am trying to figure out if I am doing this correctly.

I have connected to the database, and I have created the schema, successfully I believe.

After creating my SCHEMA in the database psql returned a series of notices like:

bugtrackingdb.sql:14: NOTICE:  CREATE TABLE will create implicit sequence "issues_issueid_seq" for serial column "issues.issueid"

bugtrackingdb.sql:14: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "issues_pkey" for table "issues"
CREATE TABLE

bugtrackingdb.sql:20: NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for serial column "users.id"

bugtrackingdb.sql:20: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"

bugtrackingdb.sql:20: NOTICE:  CREATE TABLE / UNIQUE will create implicit index "users_issueid_key" for table "users"
CREATE TABLE

My issue is that when i run the psql comand \dt to view the list of tables I get a No relations found message.

However, when I run \dt *. I get:

                        List of relations
       Schema       |          Name           | Type  |  Owner   
--------------------+-------------------------+-------+----------
 bugtracking        | closedate               | table | aa2
 bugtracking        | comments                | table | aa2
 bugtracking        | issues                  | table | aa2
 bugtracking        | opendate                | table | aa2
 bugtracking        | priority                | table | aa2
 bugtracking        | status                  | table | aa2
 bugtracking        | users                   | table | aa2

Does this mean I have created everything successfully and I am now ready to run my INSERT INTO commands and SELECT statements???

If the tables are not created, then what do I have to do to get this working properly?

Also, Is my SCHEMA written correctly or is there a better way to do it?

Why do I get No relations found when I run \dt???

Answer 1

This is how I would create the data model:

CREATE SCHEMA bugtracking AUTHORIZATION aa2;

CREATE TABLE bugtracking.issues (
    issueID BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    issue VARCHAR(50) NOT NULL, 
    dateOpened DATE NOT NULL,
    dateClosed DATE,
    type VARCHAR(25) NOT NULL,
    priority VARCHAR(15) NOT NULL,
    status VARCHAR(20) NOT NULL,
    comments TEXT NOT NULL
);

CREATE TABLE bugtracking.users (
    id BIGSERIAL NOT NULL UNIQUE PRIMARY KEY,
    issueID BIGINT NOT NULL UNIQUE REFERENCES bugtracking.issues(issueID),
    userName VARCHAR(25) NOT NULL
);

This is the output I am getting.

bugtrackingdb=# INSERT INTO bugtracking.issues (issue, dateOpened, type, priority, status, comments) VALUES ('Test issue data', '20 Jul 2017', 'Logic Error', 'low', 'Not Started', 'Enter key needs to acctuate login button when pressed.');
INSERT 0 1
bugtrackingdb=# SELECT * FROM bugtracking.issues AS issueID; issueid |      issue      | dateopened | dateclosed |    type     | priority |   status    |                        comments                        
---------+-----------------+------------+------------+-------------+----------+-------------+--------------------------------------------------------
       1 | Test issue data | 2017-07-20 |            | Logic Error | low      | Not Started | Enter key needs to acctuate login button when pressed.
(1 row)

Think about it, One user will have many issues to report but each issue only has one instance of each of those columns.

Just my 2 cents. You're going to kill yourself writing a million queries doing it your way.