Page 1 of 1
Subqueries in PostGreSql
Posted: Wed 31 Mar 2010 08:22
by rc
With a postgresql database, When I load in a TUniQuery a query containing a subquery, this by example :
Code: Select all
SELECT LOCAL.*,
(SELECT N_COMPTE FROM COMPTE WHERE ID_COMPTE = LOCAL.ID_COMPTE) as N_COMPTE
FROM LOCAL
it interprets N_COMPTE field as a
TMemoField instead of a TStringField, which causes a lot a problem of application compatibility between DBMS.
Is this a known problem of your postgresql provider, or is there another explication ?
Thank you for your answer
Posted: Thu 01 Apr 2010 08:58
by bork
Hello
Please send us the DDL script to create the COMPTE and LOCAL tables.
Posted: Thu 01 Apr 2010 14:13
by rc
Here it is :
Code: Select all
/* Table: COMPTE */
CREATE TABLE COMPTE (ID_COMPTE SERIAL NOT NULL,
N_COMMUNE INTEGER NOT NULL,
N_COMPTE VARCHAR(6) NOT NULL,
CONSTRAINT COMPTE_UNIQUE UNIQUE (N_COMMUNE, N_COMPTE),
CONSTRAINT COMPTE_PK PRIMARY KEY (ID_COMPTE));
/* Table: LOCAL */
CREATE TABLE LOCAL (ID_LOCAL SERIAL NOT NULL,
N_COMMUNE INTEGER NOT NULL,
N_INVARIANT VARCHAR(10) NOT NULL,
ID_PARCELLE INTEGER NOT NULL,
ID_COMPTE INTEGER,
CONSTRAINT LOCAL_PK PRIMARY KEY (ID_LOCAL),
CONSTRAINT LOCAL_UNIQUE UNIQUE (N_COMMUNE, N_INVARIANT));
Posted: Fri 02 Apr 2010 11:50
by bork
PgDAC create fields with types depending on the types that PostgreSQL server returns. PostgreSQL server before version 8.4 returns type that is eqal to Memo and PgDAC creates TMemoField. PostgreSQL server version 8.4 returns type that is eqal to String and PgDAC creates TStringField.
If you don't want to upgrade your PostgreSQL server you can use CAST in your query:
SELECT LOCAL.*,
cast((SELECT N_COMPTE FROM COMPTE WHERE ID_COMPTE = LOCAL.ID_COMPTE) as character varying(6)) as N_COMPTE
FROM LOCAL
For this query TStringField will be created on any PostgreSQL server version.