Re: [SQL] rule for update view that updates/inserts into 2 tables
Robins and Martijn,
Thanks for the help. My main objective here was to be able to perform both
an update and an insert in one call (wanted to reduce database roundtrips,
and speed up my program), and I am using the view solely to allow me to do
this.What both of you have pointed out is that the behavior I'm seeing
is a natural result of the way I've defined the view.
I don't need to define the view the way I did in my example. I do need the
view to have fields that map to fields in my_audit_table in order to do an
insert into that table with values from the insert statement.
Here's one solution that seems to work. Change my view to the following,
leave rules the same as before:
--Create View
CREATE OR REPLACE VIEW my_view AS
SELECT
t.my_table_id,
t.a,
t.b,
bigint '0' as audit_id,
varchar(255) '' as c
FROM
my_table t;
Does this seem reasonable to you? Do you problems with this approach?
Thanks again for your help,
Chad
From: Robins Tharakan [mailto:[EMAIL PROTECTED]
Sent: Monday, April 14, 2008 8:34 PM
To: Chad Showalter
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] rule for update view that updates/inserts into 2 tables
Hi Chad,
(Pardon me if I am shooting the stars here...)
Don't you think that on each update, you would be creating a new row that
satisfies that very given condition for the view ?
By that I mean that when you do a 'UPDATE... WHERE my_table_id=1' the RULE
now inserts another row with my_table_id = 1... which now makes two rows
that satisfy the criteria for the view.
The second time you run the update, the RULE inserts a row (in the
my_audit_table) for each row found (on the second run it'd be two rows) ...
and then so on .
Therefore, you probably want to use this CREATE RULE query instead...
CREATE OR REPLACE VIEW my_view AS
SELECT t.my_table_id, t.a, t.b, au.audit_id, au.c
FROM my_table t, my_audit_table au
WHERE t.my_table_id = au.my_table_id
AND au.audit_id = (SELECT max(audit_id) FROM my_audit_table WHERE
au.my_table_id = my_audit_table.my_table_id);
Of course this brings us into another problem that the INSERT / UPDATE
statements bomb because of the aggregate that is now there in the view...
and then I am drawing a blank here !
(Note: As mentioned in PG Docs, I have already tried creating a blanket DO
NOTHING rule coupled with Chad's rule as a DO ALSO rule ... but that doesn't
work either)
Anyone else with some ideas ?
Robins
On Mon, Apr 14, 2008 at 10:17 PM, Chad Showalter <[EMAIL PROTECTED]>
wrote:
I would like to create a rule that, by updating a view, allows me to update
one table and insert into another.
The following example illustrates what I'm trying to do:
--Create Tables
CREATE TABLE my_table
(
my_table_id serial,
a character varying(255),
b character varying(255),
CONSTRAINT my_table_id_pk PRIMARY KEY (my_table_id)
);
CREATE TABLE my_audit_table
(
audit_id serial,
my_table_id int,
c character varying(255),
CONSTRAINT audit_id_pk PRIMARY KEY (audit_id)
);
--Create View
CREATE OR REPLACE VIEW my_view AS
SELECT
t.my_table_id,
t.a,
t.b,
au.audit_id,
au.c
FROM
my_table t, my_audit_table au
WHERE
t.my_table_id = au.my_table_id;
--Create Rules
CREATE OR REPLACE RULE insert_to_my_view AS
ON INSERT TO my_view
DO INSTEAD(
INSERT INTO my_table (a,b)
VALUES(new.a, new.b);
INSERT INTO my_audit_table(my_table_id, c)
VALUES
(currval('my_table_my_table_id_seq'), new.c);
);
CREATE OR REPLACE RULE update_my_view AS
ON UPDATE TO my_view DO INSTEAD
( UPDATE my_table SET
a = new.a,
b = new.b
WHERE
my_table_id = old.my_table_id;
INSERT INTO my_audit_table
(my_table_id,
c)
VALUES
(new.my_table_id,
new.c);
);
--The insert statement below inserts one row into my_table, and one row into
my_audit_table
--(This works the way I would like)
insert into my_view(a,b,c) values('a contents','b contents', 'c contents');
--The update statement below doesn't work the way I want.
--What I would like this to do is to update one row in my_table, and insert
--one row into my_audit table. It does the update fine, but the insert to
my_audit_table
--doesn't work as I had anticipated.
update my_view set a = 'new a contents', b = 'new b contents', c = 'new c
contents' where my_table_id = 1;
If I execute the above update statement multiple times, multiple rows will
be
inserted with each call after the first call.
Specifically,
. after the first call, 1 row is inserted
. after the second call, 2 rows are inserted
. after the third call, 4 rows are inserted
. after the fourth call, 8 rows are
Re: [SQL] export CSV file through Java JDBC
At 07:20 AM 4/15/2008, you wrote:
Date: Mon, 14 Apr 2008 09:41:41 -0400
From: Emi Lu <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: export CSV file through Java JDBC
Message-ID: <[EMAIL PROTECTED]>
Good morning,
Running the following command from command line is ok, but cannot
export
a table into a csv file through java JDBC code.
Please help!
JAVA code:
===
public static void exec(String command)
{
try{
Process p = Runtime.getRuntime().exec(command);
p.waitFor();
p.destroy();
}catch(Exception e) {
System.err.println("exec command Error: " +
e.getMessage());
}
}
SQL Command:
=
psql -U username -d dbName -c "\copy tableName to 'result.csv' with
CSV "
When call exec(commands);
Nothing happens, result.csv was not created at all?
Thanks a lot!
A couple of thoughts. First, you aren't passing the password in, so
that seems like a problem. Of course, psql won't let you specify a
password on the command line but last I looked you can set an ENV var
before running psql: "PGPASSWORD=[your password here]"
Second, you don't specify a server/port, which means your Pg server is
localhost:5432?
Third, you are not specifying a path to pgsql, so you have to be sure
that it can be found in the path. Now this can be tricky: your Java
application may be running in a context DIFFERENT from your command
prompt. The user/env your Java app is running in will determine what
path vars are available to it - it may not be able to find psql. Try
running "which psql > /tmp/which.txt" in your code above and see what
happens (assuming you're on a box with "which" installed).
Fourth (minor), you don't specify column names in your export which
could result in variable results depending on the create statement -
it's better to specify to guarantee the same results every time.
Fifth, try capturing STDERR and STDOUT, so that if psql or command
shell generate errors you'll know what they are. Maybe Java gives you
that in e.getMessage or maybe you need to put it in your psql command
line.
I'm doing exactly the same thing you are doing but in Ruby/ActiveRecord
so I know this works. It works for me on Windows and Linux, fwiw.
I don't know enough Java to know if the command you are running is the
standard "shell execute" command in Java. If it's not, that's what you
want so change your code that way. You just want java to shell out to
the OS command processor. Be sure when you set your command shell env
var, that this env var persists long enough so that when you run your
psql command it's still in effect. For example this psuedo code might
not work b/c two different child shells are run:
system.exec("export PGPASSWORD=pass1234");
system.exec("psql my command here");
I think you want something more like this psuedo code:
system.set_environment("PGPASSWORD")="pass1234";
system.exec("psql my command here");
I hope this helps,
Steve
[SQL] How to find double entries
Hi, how can I find double entries in varchar columns where the content is not 100% identical because of a spelling error or the person considered it "looked nicer" that way? I'd like to identify and then merge records of e.g. 'google', 'gogle', 'guugle' Then I want to match abbrevations like 'A-Company Ltd.', 'a company ltd.', 'A-Company Limited' Is there a way to do this? It would be OK just to list candidats up to be manually checked afterwards. Regards Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find double entries
Andreas <[EMAIL PROTECTED]> writes: > I'd like to identify and then merge records of e.g. 'google', 'gogle', > 'guugle' > Then I want to match abbrevations like 'A-Company Ltd.', 'a company > ltd.', 'A-Company Limited' > Is there a way to do this? > It would be OK just to list candidats up to be manually checked afterwards. There are some functions in contrib/fuzzystrmatch that seem like they'd help you find candidate duplicates. contrib/pg_trgm and text search might also offer promising tools. What's really a duplicate sounds like a judgment call here, so you probably shouldn't even think of automating it completely. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find double entries
Andreas wrote: > Hi, > > how can I find double entries in varchar columns where the content is > not 100% identical because of a spelling error or the person considered > it "looked nicer" that way? When doing some near-duplicate elimination as part of converting a legacy data set to PostgreSQL I found the `fuzzystrmatch' contrib module immensely helpful. http://www.postgresql.org/docs/current/static/fuzzystrmatch.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find double entries
Hi, In a recent linux magazine article (http://www.linux-mag.com/id/5679) there was a mentioning of Full-Text Search Integration. Which I know nothing about, but sounded interesting to me. You might want to check it out. Regards, Tena Sakai [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] on behalf of Andreas Sent: Tue 4/15/2008 8:15 PM To: pgsql-sql@postgresql.org Subject: [SQL] How to find double entries Hi, how can I find double entries in varchar columns where the content is not 100% identical because of a spelling error or the person considered it "looked nicer" that way? I'd like to identify and then merge records of e.g. 'google', 'gogle', 'guugle' Then I want to match abbrevations like 'A-Company Ltd.', 'a company ltd.', 'A-Company Limited' Is there a way to do this? It would be OK just to list candidats up to be manually checked afterwards. Regards Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to find double entries
On Wed, 16 Apr 2008, Andreas <[EMAIL PROTECTED]> writes: > how can I find double entries in varchar columns where the content is > not 100% identical because of a spelling error or the person > considered it "looked nicer" that way? > > I'd like to identify and then merge records of e.g. 'google', > gogle', 'guugle' > > Then I want to match abbrevations like 'A-Company Ltd.', 'a company > ltd.', 'A-Company Limited' > > Is there a way to do this? > It would be OK just to list candidats up to be manually checked > afterwards. You can try something similar to below example. (levenshtein(text, text) function is supplied by fuzzystrmatch module.) SELECT T1.col, T2.col FROM tbl AS T1, INNER JOIN tbl AS T2 ON T1.col <> T2.col AND levenshtein(T1.col, T2.col) < (length(T1.col) * 0.5) Regards. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
