Re: [SQL] Trigger

It requires a bit more work in PostgreSQL to create a trigger. From the documentation: "It is not currently possible to write a SQL-language trigger function. Trigger functions can be written in C or in some of the available procedural languages." I guess you will prefer to write the trigger in

Re: [SQL] VIEW / ORDER BY + UNION

hi, WeiShang wrote, On 2/17/2005 16:46: Hi, I have created a view like this : CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where t1.orderno=t2.orderno); if I create a SQL statment: (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) UNION (SELECT orderno FROM v1 WHERE weekday

[SQL] VIEW / ORDER BY + UNION

Hi, I have created a view like this : CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where t1.orderno=t2.orderno); if I create a SQL statment: (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) UNION (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno) UNION (SELEC

[SQL] Comments on subquery performance

Hello all, I created a query that contains two subqueries and joins and would like some feedback on whether: 1) this approach is logical; and, 2) if this is an optimal approach (performance wise) to return the records I seek. Essentially, I am attempting to perform a 'lookup' on a value in ano

Re: [SQL] Trigger

Hi, Thank you ALL for the fast help it works fine with following code: CREATE OR REPLACE FUNCTION synchronize () RETURNS trigger AS 'BEGIN NEW.objectid := NEW.gid; RETURN NEW; END;' LANGUAGE plpgsql; CREATE TRIGGER syncl_holz_lager AFTER INSERT ON holz_lagerplatz FOR EACH ROW EXECUTE PROCEDURE

Re: [SQL] FW: Working with XML.

Theo Galanakis wrote: Hi, I have copied all the files manually from http ://developer.postgresql.org/docs/pgsql/contrib/ for the xml2 contribution. However I have the following issue when I attempt to compile with gmake: gcc -I/usr/includ

Re: [SQL] Pagination with Output Variables?

On Tue, Feb 22, 2005 at 04:29:30PM -0700, Steve - DND wrote: > Can a function return two type results? So that the first type would be just > the total number of records, and the second type would be the resultset of > customers? The function could return a set of cursors, one for the query that

[SQL] Pagination with Output Variables?

I have a MSSQL Stored Procedure that returns a particular section of a resultset depending on the desired "page". This procedure also has a parameter marked as OUTPUT which indicates the total number of records in the actual resultset. Is there a way to do something similar in PG or does TotalRecor

Re: [SQL] Working with XML.

Title: Message George,       I have run this SP in Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same results. We are currently using Redhat ES3 and Postgres 7.4.5.   Theo   -Original Message-From: George Weaver [mailto:[EMAIL PROTECTED] Sent: Wednesday, 23 February

[SQL] Speeds using a transaction vrs not

I wrote a .net program to move my data to postgres (works great on SQ_ASCII). In fiddling around I tried it using the odbc driver and a transaction originally, but converted it to using the .net connectivity but no transaction. What I found was it moved my database (4 gig in MSSQL) in 2 hours usi

Re: [SQL] how can I query for unset timestamps

Well, thank you very much. works perfectly, I guess I have not done much NULL value stuff. Bret On Tue, 2005-02-22 at 15:19, Gregory S. Williamson wrote: > Bret -- > A test for NULL uses the IS statement, e.g.: > > SELECT * FROM tablefoo WHERE last_timestamp IS NULL; > > HTH, > > Greg Willi

Re: [SQL] how can I query for unset timestamps

Bret -- A test for NULL uses the IS statement, e.g.: SELECT * FROM tablefoo WHERE last_timestamp IS NULL; HTH, Greg Williamson DBA GlobeXPlorer LLC -Original Message- From: Bret Hughes [mailto:[EMAIL PROTECTED] Sent: Tue 2/22/2005 1:09 PM To: postgresql sql list Cc: Subject

[SQL] how can I query for unset timestamps

I have a table that I recently altered by adding several timestamp columns. I would like to query the table for those rows for which the timestamp has not been set but am unable to find a way to do so. any tips ? in psql the column appears empty and char_length returns nothing. Some of what I h

[SQL] aggregate / group by question

Hello, I must apologize for not coming up with a more descriptive subject line. I am struggling with the following query and I am not even sure whether what I want to achieve is possible at all: The problem in real-world terms: The DB stores TRANSAKTIONS - which are either sales or refunds: each

Re: [SQL] schemas and paths with the alter statement

Wow...in less than 5 minutes Thanks Ted --- Tom Lane <[EMAIL PROTECTED]> wrote: > Theodore Petrosky <[EMAIL PROTECTED]> writes: > > ALTER TABLE public.test ADD CONSTRAINT > public.test_PK > > PRIMARY KEY (test); > > Constraints don't have schema-qualified names. Try > > ALTER TABLE publ

Re: [SQL] schemas and paths with the alter statement

On Tue, 2005-02-22 at 11:06 -0800, Theodore Petrosky wrote: > I have to first admit that I am very green at this. I > thought that one could refer to a table in a fully > qualified path... public.testtable > ... > ALTER TABLE public.test ADD CONSTRAINT public.test_PK > PRIMARY KEY (test); > ... > a

Re: [SQL] schemas and paths with the alter statement

Theodore Petrosky wrote: I assumed that it would work. Did I miss something in the docs that say you can not do this? Does "ALTER TABLE" not work with 'public.' before the table name? on 7.4.6 aswebtest=# ALTER TABLE public.test ADD CONSTRAINT public.test_PK PRIMARY KEY (test); ERROR: syntax error

Re: [SQL] schemas and paths with the alter statement

Theodore Petrosky <[EMAIL PROTECTED]> writes: > ALTER TABLE public.test ADD CONSTRAINT public.test_PK > PRIMARY KEY (test); Constraints don't have schema-qualified names. Try ALTER TABLE public.test ADD CONSTRAINT test_PK PRIMARY KEY (test); The error message was pointing to the correct place B

[SQL] schemas and paths with the alter statement

I have to first admit that I am very green at this. I thought that one could refer to a table in a fully qualified path... public.testtable I am fooling around with webobjects and EOModeler. at one point it creates the sql to create my table structure and it does something like: ALTER TABLE publi

Re: [SQL] pg primary key bug?

Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: I will to ask if it will be possible to start some querys (I do not know the query) exactly before running vacuum full and to save the results in some log file. If it is possible, we will be able to post the results to the lis

Re: [SQL] pg primary key bug?

pginfo <[EMAIL PROTECTED]> writes: > I will to ask if it will be possible to start some querys (I do not know > the query) exactly before running vacuum full and to save the results in > some log file. If it is possible, we will be able to post the results to > the list in case of ne problem an

Re: [SQL] pg primary key bug?

Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: You idea was that we have "vacuum full" + update or select for update in the same time. I think it is not the case, because we start vacuum full at 1:00 AM and no one is working in this time. Hmm. AFAICT the duplica

Re: [SQL] pg primary key bug?

pginfo <[EMAIL PROTECTED]> writes: > You idea was that we have "vacuum full" + update or select for update in > the same time. > I think it is not the case, because we start vacuum full at 1:00 AM and > no one is working in this time. Hmm. AFAICT the duplicate row copies could only be produced

Re: [SQL] problem inserting local characters ...

I had a similar problem myself and found if I used SQL_ASCHII for the data base I could insert my data. I understand however that it basically means the database does not know anything about encoding and therefore stuff like upper does not work on the extended chars. I ended up changing to sql_as

Re: [SQL] Working with XML.

Title: Message Hi Theo,   I am not able to duplicate the problem you experienced.  I tried the query you provided below using pgAdmin and psql on a 7.3.2 and an 8.0 database with success.  I then copied all the rows and pasted them to the end so that I would have 100 rows, and the query work

Re: [SQL] pg primary key bug?

Hi, Ragnar, Ragnar Hafstað schrieb: >>We are using jdbc (jdbc driver from pg) + jboss (java based >>application server) + connection pool (biult in jboss). >>... >>Will vacuum full generate this problem if we have locked table in this >>time? (It is possible to have locked table in theory) > I

Re: [SQL] problem inserting local characters ...

Hi, Garry, Garry schrieb: > Trying to do this insert, I get an error on both the values > "TelefonGeschäft" and "Firmenstraße": > > FEHLER: ungültige Byte-Sequenz für Kodierung »UNICODE«: 0xe165 This sounds as your database was created in unicode (utf-8). > (the 0xe165 differs between the

[SQL] problem inserting local characters ...

Hi, I'm running PostGreSql 7.4.6 on SuSE 9.1. Everything as such is working fine. In order to import some data from an old system, I create an SQL script that does something like: insert into prg_contacts values ('2005-02-22 13:45:36+01','NETHINKS Converter',NULL,NULL,'admin','s', NULL,NULL,NUL

Re: [SQL] pg primary key bug?

Ragnar Hafstað wrote: On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote: We are using jdbc (jdbc driver from pg) + jboss (java based application server) + connection pool (biult in jboss). ... Will vacuum full generate this problem if we have locked table in this time? (It is

Re: [SQL] pg primary key bug?

On Tue, 2005-02-22 at 10:33 +0100, pginfo wrote: > > > We are using jdbc (jdbc driver from pg) + jboss (java based > application server) + connection pool (biult in jboss). > ... > Will vacuum full generate this problem if we have locked table in this > time? (It is possible to have locked ta

Re: [SQL] pg primary key bug?

Tom Lane wrote: pginfo <[EMAIL PROTECTED]> writes: In this table we store the last value for the ID of part from other tables. For each table we have one constant in this table. We are using the table as sequence. For Example if we nee to insert the next record in some table we

Re: [SQL] pg primary key bug?

pginfo <[EMAIL PROTECTED]> writes: > In this table we store the last value for the ID of part from other tables. > For each table we have one constant in this table. We are using the > table as sequence. > For Example if we nee to insert the next record in some table we make: > select constvalue f