Thread: sql window issues
Hi, I have always liked the fact that when I click on a table I get the code to (re)create the table. It just don't seem to work when I try it in the sql window though... Even under 1.4.2, I regularly have queries that I can't execute in the sql window - what gives? Today I had to copy and paste a create table into a psql console on my remote postgres server! Can someone give me some idea of what is happening here? Cheers Antoine -- This is where I should put some witty comment.
On 6/4/06 21:30, "Antoine" <melser.anton@gmail.com> wrote: > Hi, > I have always liked the fact that when I click on a table I get the > code to (re)create the table. It just don't seem to work when I try it > in the sql window though... Even under 1.4.2, I regularly have queries > that I can't execute in the sql window - what gives? Today I had to > copy and paste a create table into a psql console on my remote > postgres server! > Can someone give me some idea of what is happening here? Not without a better definition of "It just don't seem to work". What error message do you get? Regards, Dave.
Here is a typical error: CREATE TABLE etape ( n_etp_code numeric(6) NOT NULL, -- identifiant de la table t_etp_intitule varchar(50), -- nom en clair de l'étape CONSTRAINTpk_etape PRIMARY KEY (n_etp_code) ) WITHOUT OIDS; ALTER TABLE etape OWNER TO "PRODUCTION"; La requête a inséré une ligne d'OID 0. ERROR: syntax error at or near "numeric" at character 45 Could it be an encoding issue? That pgadmin doesn't like code from a copy paste? Cheers Antoine On 06/04/06, Dave Page <dpage@vale-housing.co.uk> wrote: > > > > On 6/4/06 21:30, "Antoine" <melser.anton@gmail.com> wrote: > > > Hi, > > I have always liked the fact that when I click on a table I get the > > code to (re)create the table. It just don't seem to work when I try it > > in the sql window though... Even under 1.4.2, I regularly have queries > > that I can't execute in the sql window - what gives? Today I had to > > copy and paste a create table into a psql console on my remote > > postgres server! > > Can someone give me some idea of what is happening here? > > Not without a better definition of "It just don't seem to work". What error > message do you get? > > Regards, Dave. > > -- This is where I should put some witty comment.
> -----Original Message----- > From: Antoine [mailto:melser.anton@gmail.com] > Sent: 07 April 2006 08:54 > To: Dave Page > Cc: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] sql window issues > > Here is a typical error: > > CREATE TABLE etape > ( > n_etp_code numeric(6) NOT NULL, -- identifiant de la table > t_etp_intitule varchar(50), -- nom en clair de l'étape > CONSTRAINT pk_etape PRIMARY KEY (n_etp_code) > ) > WITHOUT OIDS; > ALTER TABLE etape OWNER TO "PRODUCTION"; > > > La requête a inséré une ligne d'OID 0. > > ERROR: syntax error at or near "numeric" at character 45 > > Could it be an encoding issue? That pgadmin doesn't like code > from a copy paste? The code above works fine for me, copied'n'pasted from your email: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_etape" for table "etape" Query returned successfully with no result in 313 ms. What does: La requête a inséré une ligne d'OID 0. mean? Regards, Dave
> -----Original Message----- > From: Antoine [mailto:melser.anton@gmail.com] > Sent: 07 April 2006 19:21 > To: Dave Page > Subject: Re: [pgadmin-support] sql window issues > > > What does: La requête a inséré une ligne d'OID 0. mean? > > It means "The query inserted a line (row) with OID 0" > > We have this on (at least) several win 2000 pro machines. We > are running postgres 8.1.1 on an mdk 10.1 but I can't see how > that would make any difference... > btw, a number of these queries I run with the analyse button, > as I like to check what is going to happen beforehand. They > give me an error so I don't bother executing... > Any pointers welcome. > Cheers > Antoine > ps. we have been running 1.4.1 for some time now but same > deal when I upgraded to 1.4.2. Well the error comes from PostgreSQL, not pgAdmin - and now you mention you are using EXPLAIN ANALYZE it all makes sense.EXPLAINing a CREATE TABLE is pointless and unsupported by PostgreSQL. You will get the same error from psql. If you want to test one or more queries before executing them for real, try something like: BEGIN; CREATE TABLE etape ( n_etp_code numeric(6) NOT NULL, -- identifiant de la table t_etp_intitule varchar(50), -- nom en clair de l'étape CONSTRAINTpk_etape PRIMARY KEY (n_etp_code) ) WITHOUT OIDS; ALTER TABLE etape OWNER TO "PRODUCTION"; ROLLBACK; Then when you're happy, just change the ROLLBACK to COMMIT. Regards, Dave.
> Well the error comes from PostgreSQL, not pgAdmin - and now you mention you are using EXPLAIN ANALYZE it all makes sense.EXPLAINing a CREATE TABLE is pointless and unsupported by PostgreSQL. You will get the same error from psql. > > If you want to test one or more queries before executing them for real, try something like: > > BEGIN; > > CREATE TABLE etape > ( > n_etp_code numeric(6) NOT NULL, -- identifiant de la table > t_etp_intitule varchar(50), -- nom en clair de l'étape > CONSTRAINT pk_etape PRIMARY KEY (n_etp_code) > ) > WITHOUT OIDS; > ALTER TABLE etape OWNER TO "PRODUCTION"; > > ROLLBACK; > > Then when you're happy, just change the ROLLBACK to COMMIT. > > Regards, Dave. > I thought it had to be something silly like that - thanks for the advice! Cheers Antoine ps. I remember using a db frontend that had a syntax check function (sql server 2000 query analyser?) - that is what I have been using the explain analyse for I guess... could that be a functionality others might be interested in? -- This is where I should put some witty comment.
-----Original Message----- From: "Antoine"<melser.anton@gmail.com> Sent: 09/04/06 11:38:08 To: "Dave Page"<dpage@vale-housing.co.uk> Cc: "pgadmin-support@postgresql.org"<pgadmin-support@postgresql.org> Subject: Re: [pgadmin-support] sql window issues > ps. I remember using a db frontend that had a syntax check function > (sql server 2000 query analyser?) - that is what I have been using the > explain analyse for I guess... could that be a functionality others > might be interested in? We have it for the View Data filters - but the problem is in this case we'd need to wrap the script in begin/rollback behindthe scenes, but be *absolutely* sure the script won't mess that up in any way. Regards, Dave -----Unmodified Original Message----- > Well the error comes from PostgreSQL, not pgAdmin - and now you mention you are using EXPLAIN ANALYZE it all makes sense.EXPLAINing a CREATE TABLE is pointless and unsupported by PostgreSQL. You will get the same error from psql. > > If you want to test one or more queries before executing them for real, try something like: > > BEGIN; > > CREATE TABLE etape > ( > n_etp_code numeric(6) NOT NULL, -- identifiant de la table > t_etp_intitule varchar(50), -- nom en clair de l'étape > CONSTRAINT pk_etape PRIMARY KEY (n_etp_code) > ) > WITHOUT OIDS; > ALTER TABLE etape OWNER TO "PRODUCTION"; > > ROLLBACK; > > Then when you're happy, just change the ROLLBACK to COMMIT. > > Regards, Dave. > I thought it had to be something silly like that - thanks for the advice! Cheers Antoine ps. I remember using a db frontend that had a syntax check function (sql server 2000 query analyser?) - that is what I have been using the explain analyse for I guess... could that be a functionality others might be interested in? -- This is where I should put some witty comment.