pgAdmin reports a SQL error when attempting to add a bigserial column to
an existing table.


server: CentOS 4.1 and Postgre 7.4
client: XP Sp 2 and pgAdmin 1.2.2

Adding the column produces this SQL statement:

CREATE SEQUENCE public.tblenglish_in_trnas_id_seq;
ALTER TABLE tblenglish_in
ADD COLUMN trnas_id int8;
ALTER TABLE tblenglish_in
ALTER COLUMN trnas_id SET DEFAULT
nextval('public.tblenglish_in_trnas_id_seq'::text) ;
INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid,
refobjsubid, deptype)
SELECT cl.oid, seq.oid, 0, cl.oid, 17154:id, attnum, 'i'
FROM pg_class cl, pg_attribute, pg_class seq
JOIN pg_namespace sn ON sn.OID=seq.relnamespace
WHERE cl.relname='pg_class'
AND seq.rel

and results in this error:

ERROR: column seq.rel does not exist


If there is a comment, a differene error results:

CREATE SEQUENCE public.tblenglish_in_trans_id_seq;
ALTER TABLE tblenglish_in
ADD COLUMN trans_id int8;
ALTER TABLE tblenglish_in
ALTER COLUMN trans_id SET DEFAULT
nextval('public.tblenglish_in_trans_id_seq'::text) ;
INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid,
refobjsubid, deptype)
SELECT cl.oid, seq.oid, 0, cl.oid, 17154:id, attnum, 'i'
FROM pg_class cl, pg_attribute, pg_class seq
JOIN pg_namespace sn ON sn.OID=seq.relnamespace
WHERE cl.relname='pg_class'
AND seq.relCOMMENT ON COLUMN tblenglish_in.trans_id IS 'id of
transmission';


ERROR: syntax error at or near "ON" at character 530


It appears that the SQL that pgAdmin is building is cut off. Maybe the
buffer being written to is too small?

Here's the SQL for creating a table with a bigserial:
CREATE TABLE test
(
test bigserial
) WITHOUT OIDS;

This is much shorter than the otherer SQL statement.


Thank you,

Richard Morrison
Sr. Software Engineer
ATX II, LLC
"In Rich We Trust!"

rich.morrison@atxinc.com wrote:
>
> pgAdmin reports a SQL error when attempting to add a bigserial column to
> an existing table.

I checked this, it works.

>
> Adding the column produces this SQL statement:
>
> CREATE SEQUENCE public.tblenglish_in_trnas_id_seq;
> ALTER TABLE tblenglish_in
> ADD COLUMN trnas_id int8;
> ALTER TABLE tblenglish_in
> ALTER COLUMN trnas_id SET DEFAULT
> nextval('public.tblenglish_in_trnas_id_seq'::text) ;
> INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid,
> refobjsubid, deptype)
> SELECT cl.oid, seq.oid, 0, cl.oid, 17154:id, attnum, 'i'
> FROM pg_class cl, pg_attribute, pg_class seq
> JOIN pg_namespace sn ON sn.OID=seq.relnamespace
> WHERE cl.relname='pg_class'
> AND seq.rel

Not quite. The resulting sql query can be taken from the SQL page of the
dialog. What you present here is probably what's logged in pgadmin.log,
which might get truncated to 1k (AFAIR) for technical reasons.

Regards,
Andresa

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Thank you,

Richard Morrison
Sr. Software Engineer
ATX II, LLC
"In Rich We Trust!"

pgadmin-support-owner@postgresql.org wrote on 10/04/2005 01:33:06 PM:

> rich.morrison@atxinc.com wrote:
> >
> > pgAdmin reports a SQL error when attempting to add a bigserial column
to
> > an existing table.
>
> I checked this, it works.

Are you using the same program version that I am? It fails on my computer
with the previously stated error. I'm using the US English, Win32, verion
1.2.2.0 build installed with the msi installer.

>
> >
> > Adding the column produces this SQL statement:
> >
> > CREATE SEQUENCE public.tblenglish_in_trnas_id_seq;
> > ALTER TABLE tblenglish_in
> > ADD COLUMN trnas_id int8;
> > ALTER TABLE tblenglish_in
> > ALTER COLUMN trnas_id SET DEFAULT
> > nextval('public.tblenglish_in_trnas_id_seq'::text) ;
> > INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid,
> > refobjsubid, deptype)
> > SELECT cl.oid, seq.oid, 0, cl.oid, 17154:id, attnum, 'i'
> > FROM pg_class cl, pg_attribute, pg_class seq
> > JOIN pg_namespace sn ON sn.OID=seq.relnamespace
> > WHERE cl.relname='pg_class'
> > AND seq.rel
>
> Not quite. The resulting sql query can be taken from the SQL page of the

> dialog. What you present here is probably what's logged in pgadmin.log,
> which might get truncated to 1k (AFAIR) for technical reasons.

Nope. I copied the statement from the SQL tab from the properties dialog
of a table I created earlier.

I copied this one from the same dialog as well:

CREATE SEQUENCE public.tbltest_s_seq;
ALTER TABLE tbltest
ADD COLUMN s int8;
ALTER TABLE tbltest
ALTER COLUMN s SET DEFAULT nextval('public.tbltest_s_seq'::text);
INSERT INTO pg_depend(classid, objid, objsubid, refclassid, refobjid,
refobjsubid, deptype)
SELECT cl.oid, seq.oid, 0, cl.oid, 17474:id, attnum, 'i'
FROM pg_class cl, pg_attribute, pg_class seq
JOIN pg_namespace sn ON sn.OID=seq.relnamespace
WHERE cl.relname='pg_class'
AND seq.relname='tbltest_s_seq'
AND sn.nspname='public'
ANDALTER TABLE tbltest
ADD COLUMN test varchar(30);

Notice the 'ANDALTER'? Part of the previous query is definately missing.

Clicking ok on the dialog gives the error: ERROR: syntax error at or near
"ANDALTER" at character 509.

Running the SQL statement in the SQL window gives the error: ERROR: syntax
error at or near "ANDALTER" at character 521.



>
> Regards,
> Andresa
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

rich.morrison@atxinc.com wrote:
>
>
> >
> > I checked this, it works.
>
> Are you using the same program version that I am? It fails on my
> computer with the previously stated error. I'm using the US English,
> Win32, verion 1.2.2.0 build installed with the msi installer.

No, I used CVS head, the 1.4beta1-to-be.
That code didn't change for quite a while, so I don't know what should
be different. Anyway, please try the snapshot version. Somehow the sql
string seems to be truncated while it is constructed, that sounds really
weird.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly