1

column_name is of type int[]

SELECT unnest(column_name) FROM table_name

The above query works on postgresql but not on hsqldb, even with sql.syntax_pgs=true

Hsqldb versions tried : 2.2.9 and 2.3.0

The sql that works in hsqldb is SELECT x FROM table_name, unnest(column_name) y(x) x and y are NOT columns of this table.

Shravan
  • 223
  • 1
  • 5
  • 12
  • Do you get any exception? – Pavel Horal Jun 09 '14 at 14:06
  • Error : user lacks privilege or object not found: UNNEST [SQL State: 42501]. The format of query that works is in the lines of SELECT x FROM table_name, unnest(column_name) y(x). To clarify, I am trying to test an application with the above query with an in memory db. – Shravan Jun 09 '14 at 14:08

1 Answers1

1

HSQLDB tries to emulate PostgreSQL's syntax and features, but like most emulations it is imperfect.

IIRC, one of the things it has a hard time with is PostgreSQL's quirky use of set-returning functions in the SELECT clause.

Use of SRFs in the SELECT clause is a weird PostgreSQL extension that's deprecated in favour of SQL-standard LATERAL queries anyway. The alternate formulation you showed:

SELECT x FROM table_name, unnest(column_name) y(x);

is the correct and preferred form. So just use that.

In general, testing on one DB then deploying to another is a recipe for pain. I strongly suggest just setting up a local PostgreSQL instance for testing instead.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • The test is an automated integration test, that needs to complete in extremely short duration, hence the use of in memory database. I was under the impression specifying syntax_pgs would take care of formulating a LATERAL query for SRF in SELECT, apparently not. – Shravan Jun 10 '14 at 05:47
  • Using an in-memory DB shouldn't save you much time over a properly configured PostgreSQL anyway. http://stackoverflow.com/q/9407442/398670 – Craig Ringer Jun 10 '14 at 05:58
  • Great answer (+1), however I disagree with the last sentence. Being able to run application without any external dependencies is very convenient. – Pavel Horal Jun 10 '14 at 14:43
  • Oh, I agree that it's convenient, I just mean that the runtime won't be too different. I'm sometimes tempted to package PostgreSQL up as a kind of horrifying mega-JAR with the executables bundled. It'd have a control harness to unpack the executables to a temp dir (picking the right executable for the right platform) then initdb and start up. I probably would've done it by now if I was still working with Java. – Craig Ringer Jun 10 '14 at 23:28