postgresqlpostgresql-11

How can I filter differences of datasets in a table

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 :)

Solution

  • 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: enter image description here

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