Thread: FOLLOW UP: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

FOLLOW UP: problems doing sub-selects on PostgreSQL 7.1.3 and Solaris 7

From
"Heather Johnson"
Date:
FYI--Here are the table creation statements for the tables involved in the sample query.
 
-- user_id_sequence
CREATE SEQUENCE user_id_sequence INCREMENT 1 START 100;
 
-- users
-- table for basic user data
CREATE TABLE users (
 
 id             INTEGER   NOT NULL PRIMARY KEY  -- unique id for each user
                DEFAULT nextval('user_id_sequence'),
 f_name         VARCHAR(128)  NOT NULL,         -- user's first name
 l_name         VARCHAR(192)  NOT NULL,         -- user's last name   
 email          VARCHAR(256)  UNIQUE NOT NULL,  -- user's email address
 password       VARCHAR(16)   NOT NULL,         -- user's password
 hint           VARCHAR(256),                   -- hint for password
 dt_registered  TIMESTAMP DEFAULT timestamp('now'),   --timestamp format is: YYYY-MM-DD HH:MM:SS-05
 user_generated_p CHAR(1)   NOT NULL            -- user-generated predicate
                  CHECK( user_generated_p IN('t', 'f')),
                                                -- true or false specified
 opt_in_p       CHAR(1)   NOT NULL              -- opt in value
                DEFAULT 't'                     -- true or false specified
                CHECK( opt_in_p IN('t', 'f'))          
);
 
-- user_demographics
-- table to hold user's demographics data
CREATE TABLE users_demographics (
 
 users_id         INTEGER    NOT NULL PRIMARY KEY  -- user's id
                  REFERENCES users(id),
 sal              VARCHAR(5) CHECK( sal IN('MR', 'MS', 'MISS', 'MRS', 'OTHER')), 
                                                    -- salutation
 addr1            VARCHAR(256),                     -- user's address line 1
 addr2            VARCHAR(256),                     -- user's address line 2
 city             VARCHAR(64),                      -- user's city
 state            CHAR(2)    NOT NULL,              -- state of residence
 zip              CHAR(5)    NOT NULL,              -- zip code
 area_code        VARCHAR(3)   NOT NULL,            -- area code
 phone            VARCHAR(15),                      -- phone number
 country          CHAR(2)    NOT NULL
                  REFERENCES country_codes(code)
                  DEFAULT 'US',  
                                                    -- country of residence
 income           VARCHAR(7)   REFERENCES income_codes(code),    
                                                    -- user's income level
 gender           CHAR(1)    NOT NULL               -- gender
                  CHECK( gender IN('m', 'f', 'u')), 
                                                    -- male or female or specified
 birth_year       INTEGER,                          -- birth year
 birth_month      INTEGER,                          -- birth month
 birth_day        INTEGER                           -- birth day
);