How do I go about easy table input without creating an application? I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something in a reference table rather than typing an arbitrary ID number.

I want to create the databases in postgresql properly (with ID numbers as primary key) - but don't want to have to look up the ID all the time when manually entering info into the database.

Is there a way I can use an INSERT statement (or something similar) and use a name field, rather than the auto ID?

Example of a record table and reference table:

Record Table:

CREATE TABLE testreport.records
(
  recordid serial NOT NULL,
  userid integer,
  CONSTRAINT records_pkey PRIMARY KEY (recordid),
  CONSTRAINT records_r_user_fkey FOREIGN KEY (userid)
      REFERENCES testreport.r_user (userid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)


Reference Table:

CREATE TABLE testreport.r_user
(
  userid serial NOT NULL,
  username text,
  CONSTRAINT r_user_pkey PRIMARY KEY (userid)
)




I want to be able to insert data by typing in the username, rather than having to type in the userid. Is there a way I can do this?

Note: All fields in username are unique

Thanks
If I am not missing anything... Assuming you always specify the user name in such a way as to exclude possibility of multiple matches (if e.g. you decided to look the user up by a mask using LIKE rather than by a complete name), you could try inserting data into the record table using an INSERT...SELECT statement like below:

INSERT INTO testreport.records (userid)
SELECT userid
FROM testreport.r_user
WHERE username = 'specific user name';
/* or:
WHERE username LIKE 'some pattern'
- just make sure it matches the single row you meant to reference
*/