In Aster, how do I use CREATE TABLE IF EXISTS and what version of postgres am I using?

Question

We're using Aster for some of our web session logs. I've been told internally that Aster uses PostgreSQL, and in fact for a lot of my queries, I look on this site and follow the guidelines for PostgreSQL code, which always work.

Now, I'm trying to use a simple query that keeps failing (Syntax error at or near "NOT"):

create table IF NOT EXISTS scratchdb.test_table
(partition key(uvn)) as
select distinct date(created_dt) as full_date,uvn,user_id from db.db_table

I keep getting errors on IF NOT EXISTS. So I found another article that says I need at least PostgreSQL-9.1 for IF NOT EXISTS to work. In order to find out what version I'm on, that article recommends:

select version();

But that returns an error :

function version is not supported

So, Aster must not be 100% PostgreSQL. My two questions are, how do I use IF EXISTS when creating a table in Aster, and how do I tell what version of PostgreSQL I am using?

Answer 1

Use two SQL statements when dropping / creating table in Aster:

DROP TABLE IF EXISTS scratchdb.test_table;
CREATE TABLE scratchdb.test_table ...

Also, I don't recommend consulting Postgres documentation newer than version 8.3 and even then it's no guarantee that Aster supports function or feature found there. Aster does indeed have a lot of similarities with Postgres but it is not the same.

So, there is no version of PosgreSQL - it's only version of Aster that matters. One way to check it is via admin console (AMC): point your browser to https://server_name_or_ip_address

After logging in click Admin: Cluster Management: Nodes tab. It displays all the nodes present in the cluster, along with software version information.