Amazon Redshift like most of the PostgreSQL relation databases like Netezza or Vertica does not provide support for dual system table. You can simply use the SELECT clause with nothing to display the results. In this article, we will check what is Amazon Redshift dual table alternative and how to use it.
What is DUAL table?
The DUAL is special one row, one column table present by default in databases. The database like, oracle automatically create dual table and grant SELECT access to all users by default.
The dual table has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement.
Amazon Redshift DUAL Table Alternative
When migrating data from Oracle environments, you may have noticed that the user may have used the DUAL table to test the database connection or perform basic computations.
In the Redshift database, there is no DUAL table so you may want to implement an equivalent object that returns quickly (for JDBC connection pool testing) and avoids the normal overhead of a table selection.
How to Create DUAL Table in Redshift?
As mentioned earlier, the dual table has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X.
In Redshift database, you could simply create a view named DUAL with column as DUMMY which hold value ‘X’.
For example, below is the DUAL view example.
CREATE VIEW DUAL AS SELECT 'X' AS DUMMY;Note that, you have to create a dual view in all your schema’s or databases when you create them. You can add this table schema in your automated script that create schema or database.
Now, you can directly migrate scripts which is using DUAL view without modifying the source scripts.
For example, below is the sample example.
select now() from dual;
now
-------------------------------
2019-09-01 17:01:42.127935+00
(1 row)select sysdate from dual;
timestamp
----------------------------
2019-09-01 17:02:05.873244
(1 row)Related Articles,
How this helps 🙂