I am relativly new to SQL and I have a short question. I already searched for similar questions in stackOverflow but I couldn't find anything.I have created some Views. These Views change from one Version to antother. To make migration easier for the customer, I want to filter differences in data_types of columns between two versions. Currently I'm working PostgreSQL Version 11.16
I have table that looks like this:




versionsnummer
install_timestamp
column_name
data_type




D1
2023-06-02 06:42:14.531588
t0801_01
integer


D1
2023-06-02 06:42:14.531588
t0801_04
character varying


D2
2023-07-02 06:42:14.531588
t0801_01
integer


D2
2023-07-02 06:42:14.531588
t0801_04
integer




Now I want to find all rows where the value of the column data_type has changed between two versions.
So I'm expecting the following result:




versionsnummer
install_timestamp
column_name
data_type




D1
2023-06-02 06:42:14.531588
t0801_04
character varying


D2
2023-06-02 06:42:14.531588
t0801_04
integer




What I've tried is this:
SELECT DISTINCT ON (column_name, data_type) column_name, data_type FROM mytable WHERE versionsnummer = 'D1' OR versionsnummer = 'D2';
Unfortunately I didn't get the expectes Result with this query. Could you please tell me waht i'm doing wrong here?
Thank you very much :)

How can I filter differences of datasets in a table
I think you can achieve this via "SELF JOIN". Join the tables with itself on "column_name" column.
Here is the code:
SELCT t1.versionsnummer, t1.install_timestamp, t1.column_name, t1.data_type
FROM [your_table_name] t1
JOIN [your_table_name] t2 ON t1.column_name = t2.column_name
WHERE t1.data_type <> t2.data_type;

Example: If you select all columns from the table, you will get:




versionsnummer
install_timestamp
column_name
data_type




D1
2023-06-02 06:42:14.531
t0801_04
character_varing


D2
2023-07-02 06:42:14.531
t0801_04
integer




Tested here:

*Don't forget to change table name in this query!
