Absolute beginner needing some help with PostgreSQL and Python I am primarily a developer, with very little experience around databases, and this is my first time using PostgreSQL. I am using Python 3.6 and PostgreSQL with Psycopg2.

After a user enters a membership_number, 3 things should happen:


Save the membership_number with the time and date, and the location of the store, in a table, let's say the search
table.
Retrieve the customer_name associated with that membership_number, if it is already in the users' table.
If customer_name is not present, ask the user for it and save it on the users' table.


Both tables have the customer_id so I can cross check all the times that someone has searched for a particular membership_number, get their name and  other records with a LEFT JOIN. 

I plan to have several devices linked to it, so my main concern is race conditions if I write a query that does something like this:


check if membership number is present, if not, ask the user for the customer_name, then save it and then retrieve the fields needed and make some left join to get aditional info.


Even if this is the right way to do it, I don't get how that query would be constructed.

Also, I've read something about temp tables, but should I:


send the membership_number
save to a temp table
update search table
return results if present in users' table
if not, ask store for customer_name
save to temp table
update users' table
return results (from left join)


This doesn't seem right.

Also read something about IF NOT EXISTS, but again, never used it and don't know how to.

Could it possibly be done with just one table? Am I overthinking this? What should be the best way to achieve this?
DATABASE

The primary function of a database is to ensure that you always have valid data.

Defining valid data is done with CONSTRAINTS (including NOT NULL) and Data Types.

Your application design seems to be based on the idea: "a database is a bit bucket to store data".

This isn't how you treat a database.

A lot of your requirements can be handled by the database if and only if you properly define what is valid data.

Your applications should:
 1. Try to modify the data.
 2.  If the modification fails, handle it within your application.

Constraints

Here is how constraints can throw an error for your application to handle your specified situations.

Adding row to Search table (scan entry)

After you type in the membership ID, you attempt to INSERT a row into the Search table.

The Foreign Key Constraint will automagically lookup the membership_id in the User's table.  If the membership_id is not found, the INSERT throws an error.

Adding row to User's Table (membership info)

After you ask for the person's name for the given membership_id, you attempt to INSERT a row into the User's table.

A Primary Key Constraint will ensure that one row and only one row exists in the User's table with that membership_id.

If that membership_id already exists, an error is thrown for your INSERT statement.

Updating User's information

You are going through a "update customer info" set of pages.  You attempt to UPDATE a row in the User's table.

If that membership_id does not exists, an error is thrown for your UPDATE statement.

If row exists, UPDATE; if not, INSERT

Some databases have a MERGE or UPSERT statement that allows you to UPDATE a row.  If it doesn't exist, it will INSERT the row.

Lookup Member's Name from User's Table (membership info)

You SELECT some data from the Users table.

I'm not 100% sure if you'll receive a "no data found" error for this one.

However, you can always check if you have received 0, 1, or more rows within your application.

Race Condition

A lot of your "Race Condition" concerns should be handled by an ACID Compliant database (eg PostgreSQL)