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

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


closed as off-topic by billinkc, Max Vernon, RolandoMySQLDBA, Paul White, Kin Shah Jul 22 '14 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 Shah
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 '14 at 16:16

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

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