jooq select into

[pgo...@gmail.com]

Hi,

postgres has a select into which allows defining a new table from the results of a query. Is there any way i can do this in jooq? 

http://www.postgresql.org/docs/9.3/static/sql-selectinto.html

i have a use case where i create a complex query containing a number of joins and a number of conditions to get a result set.

On this result set i need to compute different aggregate values which are then used for plotting a graph.

I thought it would be more efficent to push the results of the first query into a temp table and then do the later computation on the temporary table.

Thanks,

Gokul

[Lukas Eder]

Hello,

This is currently not supported by jOOQ and we probably won't support it as PostgreSQL documentation recommends using the CREATE TABLE AS syntax instead of SELECT INTO.

Note that other databases including the SQL standard specify SELECT INTO as follows:

<select statement: single row> ::=

  SELECT [ <set quantifier> ] <select list>

    INTO <select target list>

    <table expression>

<select target list> ::=

  <target specification> [ { <comma> <target specification> }... ]

So, INTO expects column references or parameters, for instance, not table references.

Now, CREATE TABLE AS can be used with jOOQ using plain SQL as follows:

DSL.using(configuration)

   .execute("CREATE TABLE xx AS {0}", select);

Where select is your jOOQ SELECT statement.

In any case, the CREATE TABLE AS statement would be a useful addition to the newly introduced set of DDL statements in jOOQ. I have added it to the roadmap for jOOQ 3.5:

https://github.com/jOOQ/jOOQ/issues/3381

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[pgo...@gmail.com]

Thanks Lukas.

That fills the bill nicely.

 

Is there any way i can use the earlier jooq generated column names to refer to the columns names in the table  or should i use sql builder with strings to query this table.

Thanks,

Gokul

[pgo...@gmail.com]

Hi,

I figured out a way in which i call getName on the original generated column and do a select on the new table. That seems to work well for post gres.

I am very new to sql so i am not sure if this is a best practice of using only the column name without the schema and table names.

Thanks,

Gokul

[Lukas Eder]

Hi Gokul,

It's hard to say from your description. Maybe, share a little code to see where you're going and to see whether that's a good idea?

Cheers

Lukas

[pgo...@gmail.com]

Hi, 

A little background. The problem i am solving is a bit similar to this simplified version.

As an admin of a mooc site you want to track how many people have completed, started in enrolled in various course enrollments over the past 1 year and plot them in a single graph

for each user, course a record is present in a course status table which has user id,course id,enrolled date,started date, completed date etc

SelectConditionStep<Record> selectStmnt =  create.select(COURSE_STATUS.USER_ID,COURSE_STATUS.COURSE_ID,COURSE_STATUS.STARTED_DATE,COURSE_STATUS.COMPLETED_DATE,COURSE_STATUS.ENROLLED_DATE)

.from(COURSE_STATUS)

.where(extract(COURSE_STATUS.ENROLLED_DATE,DatePart.YEAR).eq(2014));

dslContext.execute("CREATE TEMP TABLE TempTable as {0}", selectStmnt);

dslContext.select(

                field(COURSE_STATUS.COURSE_ID.getName()), DSL.count())

               .from("TempTable")

               .where(field(COURSE_STATUS.COMPLETION_DATE.getName()).isNotNull())

               .groupBy(field(COURSE_STATUS.COURSE_ID.getName()))

               .fetch();

So on for started, enrolled etc.

The System has lot more filteration params like location,sex,age group etc and more data points can be viewed as passed the course,failed it, etc

Thanks,

Gokul

[Witold Szczerba]

Maybe it would be possible to create a view and query it instead?

Regards,
Witold Szczerba
---
Sent from my mobile phone.

[pgo...@gmail.com]

The view would not be able to cache the results of the first expensive query.

Thanks,

Gokul

[Lukas Eder]

Hi Gokul,

Maybe, Witold referred to materialized views, which might indeed be a viable option if you're using PostgreSQL 9.3+:

http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html

This materialized view approach is quite similar to your temp table approach. Both are fine, I think.

Now, I also understand your previous questions - I think that your column naming approach is fine. You also have an option to use runtime table mapping to map the original table to the temp table name in rendered SQL, though. This is documented here:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/runtime-schema-mapping/

However, looking at your second query, I suspect you might be able to avoid it completely and calculate that COUNT() value already in your first query using window functions:

COUNT(COURSE_STATUS.COMPLETION_DATE) OVER(PARTITION BY COURSE_STATUS.COURSE_ID)

I suggest reading this post for more details:

http://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/

Another thing that comes to mind is proper indexing. You're expressing a predicate like so:

extract(COURSE_STATUS.ENROLLED_DATE,DatePart.YEAR).eq(2014)

Do you have an function-based index on the relevant expression? If not, maybe you should add one, or pre-calculate that column using a trigger, and then filter on that pre-calculated (obviously, indexed) column. However, optimal indexing is non-trivial if you allow random predicates to be applied. But if you have a quite selective predicate that is always applied, you should make sure that it can profit from an index.

We usually recommend reading Markus Winand's excellent book on SQL Performance:

http://sql-performance-explained.com/l

You can use the "jOOQ" discount code to get a 10% discount on books ordered over this website.

That should be food for thought :-)

Cheers,

Lukas

[pgo...@gmail.com]

i am on redshift which is postgres 8.x. so materialized views are out.

i am reading up on window functions and have ordered the book as well. 

will take a stab at the problem again after finishing the reading part.

Thanks,

Gokul

[Lukas Eder]

Thanks for the follow-up. Hmm, yes, PostgreSQL 8.x was lacking quite a bit of functionality compared to more up-to-date versions.

Looking forward to learn how your analyses turn out!

Cheers

Lukas