Trying to create a transaction group, OPC -> DB. The database is AWS Redshift which is based on Postgres but has some key differences, one of which I found when trying to auto create a table for my transaction group.
Here is part of the error - java.lang.Exception: Error running query: CREATE TABLE ignition.downtime_test ("downtime_test_ndx" SERIAL NOT NULL,"Downtime_Test_Tag"
Caused by: com.amazon.redshift.util.RedshiftException: ERROR: Column "downtime_test.downtime_test_ndx" has unsupported type "serial".
Any work around for this, I would like to be able to create tables automatically? My default translator is set to postgres, does anyone have parameters for a Redshift translator they would be willing to share?
I've not done this myself, but you could start with trying to use the redshift JDBC driver
Download the Amazon Redshift JDBC driver, version 2.1 - Amazon Redshift
1 Like
I'm already using this, had to create the driver in ignition to set up the connection. Connection is working fine, Redshift just does not support the create table syntax used by the auto generate table option from a transaction group.
You are not going to find a work-around. This column type keyword is needed for Ignition to make a primary key.
That's what its seeming like, was hoping someone might have done it successfully before I get to deep into the weeds
EDIT: Doh! forgot the link...
This page shows the unsupported datatypes. Try in the translator:
bigint identity(0, 1)
I just reviewed the unsupported features list (separate from Jordan's link):
So it doesn't support indexes of any kind, doesn't support any constraints, and doesn't support sequences (the identity generation part of PostgreSQL). I don't see how this can possibly be a good choice for front-end data collection.
Ya. I have an MSSQL database on site that I can use but some folks wanted live downtime data queryable from redshift since its where all our other production data is housed.