2

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?

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Rick LaPointe
  • 73
  • 1
  • 2
  • 6
  • @CraigRinger http://www.asterdata.com/ – Peter Eisentraut Apr 12 '13 at 12:18
  • Thanks @PeterEisentraut - yes, "Aster" is indeed Terradata Aster, which is (theoretically) just an implementation of PostgreSQL. When looking for help for Aster, you can't google "aster question I have" as there are very few results; you need to google "postgres question I have". – Rick LaPointe Apr 12 '13 at 16:55
  • Ah, familiar to me as Terradata. Sorry. – Craig Ringer Apr 13 '13 at 00:59
  • Have a look in the data directory on the file system. (If you don't know where it is, it's possible that `SHOW data_directory` will tell you, but again it depends on how customised this PostgreSQL flavour is). In the datadir there might be a file named `PG_VERSION` ... again, unless they've removed that as part of their changes to PostgreSQL. – Craig Ringer Apr 13 '13 at 01:01

1 Answers1

2

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.

topchef
  • 19,091
  • 9
  • 63
  • 102