I am very new to postgresql. I don’t plan to study it fully in-depth right now as I have other priority topics to finish. But now I need to know some basics of postgresql for the perfect completion of a personal project.
I need to save a file location in a column in a table in postgresql.
- What should be the type that I should keep for the table while I am creating the table?
- How to retrieve the file name?
- I have to store a number that’s 10 digits long? Is it gonna be okay if I give the type as int.
CREATE TABLE "Test" (
"test_id" uuid,
"user_id" int,
"test_name" text,
"saved_location" Type,
PRIMARY KEY ("test_id")
);
I want to give user_id as FK, but no idea how to do it. I would have studied SQL in detail and did this, but I am on a tight schedule on another course. Please help me. I am using python. Thanks in advance.
Advertisement
Answer
I don’t understand question 1. Tables don’t have types, they are just tables unless you are using a temporary table. For file names and save locations and things, generally with any strings varchar will solve all your problems. You want to set varchar datatype to whatever the max length of the string could possibly be in your use case, as that is how much storage the column will require no matter how short the strings it contains are.
For a file name and save location I would think varchar(100) would be adequate, but that’s up to you. If you set it too short and try to insert data you will get an error.
To get file names, starting in this stack overflow thread is probably going to set you in the right direction? I’m not entirely sure what you need. How do I get the full path of the current file’s directory?
10 digit number…
The int data type can carry any number in this range -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). If your 10 digit number needs to go higher or lower then use BIGINT datatype (which uses quite a bit more storage but I doubt you care much about that.