topic Re: SQL Syntax PostgreSQL in Talend Studio

SQL Syntax PostgreSQL

Mp_99 — Fri, 15 Nov 2024 23:54:04 GMT

Just getting started with Talend (TOS). I connected to a PostgreSQL server and used a simple query:

Select * from Table1

This query fails.

I used the guess query function and Talend added slashes in the query. Which worked. But when I edit the query in the SQL builder the slashes aren't' present.

I'm really confused as to the SQL syntax and why a simple query like SELECT * FROM table1 doesn't work.

Re: SQL Syntax PostgreSQL

Anonymous — Tue, 03 Aug 2021 03:47:29 GMT

What's the error messages? Have you defined the columns on schema to map the returned fields of the query.

Regards

Shong

Re: SQL Syntax PostgreSQL

Anonymous — Tue, 03 Aug 2021 04:08:52 GMT

Hello,

Did you fill in schema when setting up PostgreSQL DB connection? Make sure that you are able to retrieve schemas from PostgreSQL DB.

Here is online documentation about: TalendHelpCenter: Centralizing database metadata

Best regards

Sabrina

Re: SQL Syntax PostgreSQL

Mp_99 — Tue, 03 Aug 2021 15:18:07 GMT

I mapped the schema for the database and then filled in the schema for a single table...basically i wanted to create a simple query "Select * from Table" but query fails with error "Syntax error on token, Table, delete this token"

but when i use guess query, I get a strange query that looks like this:

SELECT \"ServerName\".\"schema\".\"_Table_\".\"column\"

FROM \"ServerName\".\"schema\".\"_Table_\""

I went back and defined my query in the repository but the schema appears that it's looking for a table schema, which makes no sens to me since I'm using a query with joins.

I tried using the "built-in" for the schema and using the query from the repository but it fails with "Bad value..."

The stored query in the repository runs fine in the SQL Builder but I can't get it run in the tDBInput component.

Re: SQL Syntax PostgreSQL

Anonymous — Wed, 04 Aug 2021 04:45:36 GMT

Hello,

Could you please clarify in which Talend version/edition you are? And what's PostgreSQL version you are trying to connect?

Best regards

Sabrina

Re: SQL Syntax PostgreSQL

Mp_99 — Wed, 04 Aug 2021 14:28:00 GMT

TOS 7.3.1

Postgre - I have the connection set to "V9 and later". The actual version of the DB is 12.5

Re: SQL Syntax PostgreSQL

Mp_99 — Thu, 05 Aug 2021 13:40:10 GMT

I did retrieve the schema for the database. But when I enter the query TOS appears to be looking for a table schema. Which makes no sense when using a query with several joins.

Re: SQL Syntax PostgreSQL

Anonymous — Fri, 06 Aug 2021 05:58:38 GMT

Hello,

A PostgreSQL database has at least one default Schema, which will be referenced in case you do not define a Schema.

For your join sql query, you are using same database, different schemas, SCHEMA -> TABLE?

Best regards

Sabrina

Re: SQL Syntax PostgreSQL

Mp_99 — Fri, 06 Aug 2021 14:21:00 GMT

Hi - thanks for the reply. When I change the schema from Built-in to Repository, I can only select a table schema from the Repository Content window that pops-up. Why would I want to use a table schema with a query that has multiple joins? I don't see how to select a schema that's appropriate for a query.

I have the query saved in the repository, but there's no way to reference it in the schema selection.

Do I have to create a schema from scratch and define every column?

Thanks!