Re: [Postgres-xc-developers] ERROR: unexpected varno 6 in JOIN RTE 5

[Abbas B. <abb...@en...>]

I had to change the insert query a little bit and had to do some dummy
inserts in projects and changes too, but the query worked fine for me. BTW
I tried on current master, I am not sure which version you are using to
test.

Here are the queries I tried.

After creating the three tables, I did

insert into projects values('?', '?', DEFAULT, '?', '?');

insert into changes values('?', '?', '?', '?', DEFAULT,  '?', '?',
clock_timestamp(), '?', '?');

INSERT INTO tags (tag_id, tag, project, change_id, note, committer_name,
committer_email, planned_at, planner_name, planner_email)
SELECT tid, tg, proj, chid, n, name, email, at, pname, pemail FROM ( VALUES
('?', '?', '?', '?', '?', '?', '?', clock_timestamp()::timestamptz, '?',
'?')) i(tid, tg, proj, chid, n, name, email, at, pname, pemail) LEFT JOIN
tags ON i.tid = tags.tag_id WHERE tags.tag_id IS NULL;

Best Regards



On Wed, Feb 5, 2014 at 10:47 AM, David E. Wheeler <da...@ju...>wrote:

> On Feb 4, 2014, at 9:24 PM, Abbas Butt <abb...@en...>
> wrote:
>
> > Could you please send the table definition of tag? A CREATE TABLE
> statement would do.
>
> CREATE TABLE tags (
>     tag_id          TEXT        PRIMARY KEY,
>     tag             TEXT        NOT NULL,
>     project         TEXT        NOT NULL REFERENCES projects(project) ON
> UPDATE CASCADE,
>     change_id       TEXT        NOT NULL REFERENCES changes(change_id) ON
> UPDATE CASCADE,
>     note            TEXT        NOT NULL DEFAULT '',
>     committed_at    TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
>     committer_name  TEXT        NOT NULL,
>     committer_email TEXT        NOT NULL,
>     planned_at      TIMESTAMPTZ NOT NULL,
>     planner_name    TEXT        NOT NULL,
>     planner_email   TEXT        NOT NULL,
>     UNIQUE(project, tag)
> ) DISTRIBUTE BY REPLICATION;
>
> Want projects and changes, too?
>
> CREATE TABLE projects (
>     project         TEXT        PRIMARY KEY,
>     uri             TEXT            NULL UNIQUE,
>     created_at      TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
>     creator_name    TEXT        NOT NULL,
>     creator_email   TEXT        NOT NULL
> ) DISTRIBUTE BY REPLICATION;
>
> CREATE TABLE changes (
>     change_id       TEXT        PRIMARY KEY,
>     change          TEXT        NOT NULL,
>     project         TEXT        NOT NULL REFERENCES projects(project) ON
> UPDATE CASCADE,
>     note            TEXT        NOT NULL DEFAULT '',
>     committed_at    TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
>     committer_name  TEXT        NOT NULL,
>     committer_email TEXT        NOT NULL,
>     planned_at      TIMESTAMPTZ NOT NULL,
>     planner_name    TEXT        NOT NULL,
>     planner_email   TEXT        NOT NULL
> ) DISTRIBUTE BY REPLICATION;
>
> Best,
>
> David
>
>
>


-- 
-- 
*Abbas*
 Architect

Ph: 92.334.5100153
 Skype ID: gabbasb
www.enterprisedb.co
<http://www.enterprisedb.com/>m<http://www.enterprisedb.com/>

*Follow us on Twitter*
@EnterpriseDB

Visit EnterpriseDB for tutorials, webinars,
whitepapers<http://www.enterprisedb.com/resources-community>and
more<http://www.enterprisedb.com/resources-community>