current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

closed as off-topic by billinkc, Max Vernon, RolandoMySQLDBA, Paul White, Kin Jul 22 at 18:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – billinkc, Max Vernon, RolandoMySQLDBA, Paul White, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.
    
I believe the search term you are looking for would be scaffolding. –  billinkc Jul 8 at 16:16

1 Answer 1

up vote 2 down vote accepted

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
*/
share|improve this answer
    
Another option would be to use a (before insert) trigger to lookup the corresponding ID during automatically –  a_horse_with_no_name Jul 20 at 14:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.