Are there ways to determine datatype for column in SQL?

Are there ways to determine datatype for a column in SQL?

I have a table called person. Person connects with a table called attribute. This table has columns called name and value. A value column can have text data that is integer, data or boolean. For example, height, eye color, etc.

I could give all data type text, and handle the data type on the client. But are there any better ways?

Yes, there are several ways to determine the unknown data type for a column in SQL. Here are a few methods:

Use the DESC command: You can use the DESC command to view the structure of a table, including the data types of each column. Simply run the following SQL command, replacing "table_name" with the name of the table you're interested in:

DESC table_name;

This will give you a list of columns in the table, along with their data types and any other relevant information.

Use the DATA_TYPE function: The DATA_TYPE function can be used to return the data type of a specific column in a table. Here's an example SQL command:

SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name';

This will return the data type of the specified column in the specified table.

Use the CAST or CONVERT function: If you're unsure about the data type of a column, you can use the CAST or CONVERT function to try to convert the data to a specific data type. Here's an example SQL command:

SELECT CAST(column_name AS VARCHAR(255)) FROM table_name;

This will attempt to convert the data in the specified column to a VARCHAR data type with a maximum length of 255 characters. If the conversion is successful, you can use the returned data type to determine the original data type of the column.

It's important to note that these methods may not always be foolproof, especially if the data in the column is inconsistent or contains null values. However, they can be useful tools for getting a better understanding of the structure of a table and the data types of its columns.

You are talking of an EVA table (entity/value/attribute table) aka key/value table. And you are addressing one of its main problems: How to handle different data types.

If you use a string column in your table, you can of course store every possible value. '123' is a number, '2023-02-27' a date and 'true' a boolean. You can convert this everytime you work with this column. But if your boolean value isn't 'true' or 'false' as expected, but 'yes' for instance, your conversion may fail. Same with numbers where you hit the decimal separator problem ('1.234' vs '1,234'), etc.

You can handle this by providing your EVA table with one column per data type and always only fill one value. Then your date resides in the date column, and if it is an integer instead, it is in the integer column.

EVA tables are a nuisance to work with. You'd only use them when common tables with common columns don't suffice, e.g. when dealing with products of very different types where one product has a voltage and the other a collar shape or the like.

Following this thought, you seem to want to deal with persons where "height" and "eye color" don't necessarily apply. Aliens changing their form every other minute, maybe?

My advice: Don't use EVA, if you can avoid this. Use normal columns: person.height, person.eye_color.