0

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.

  1. What should be the type that I should keep for the table while I am creating the table?
  2. How to retrieve the file name?
  3. 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.

Jihjohn
  • 398
  • 4
  • 19

1 Answers1

1

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.

https://www.postgresqltutorial.com/postgresql-integer/

juppys
  • 140
  • 5
  • Thank you. Actually, I am quite confused about this postgresql as I can't find a proper tutorial. Some commands I see online aren't working properly when I try to do them. Do you have any suggestions on where to start or anything? I have already installed postgresql in my pc, but I think it's a bit more harder than I thought. – Jihjohn Sep 30 '20 at 20:59
  • if you know how to do something in one sql dialect then figuring out how to do it in postgres isn't so hard. So try searching for the solution in mysql or sql server. Then once you know the name of what you're trying to do search for the postgres way to do. – juppys Oct 02 '20 at 02:09
  • I'll try. Thank you – Jihjohn Oct 03 '20 at 17:50