Re: [SQL] Characters that needs escape characters when inserting to database
On Tue, Jan 17, 2006 at 04:20:23PM +0900, Michael Glaesemann wrote: > On Jan 18, 2006, at 7:21 , Christian Paul B. Cosinas wrote: > >Can anyone give me a list of characters that needs to be preceded > >by an escape character before inserting to database. > > Take a look at this documentation on string constants. It should > answer the questions you have. > http://www.postgresql.org/docs/current/interactive/sql-syntax.html#SQL-SYNTAX-STRINGS Out of curiosity, why are you asking? Unless you're implementing some low-level interface to the database you shouldn't need to worry about escaping strings; just use your API's quote/escape (or whatever) function or its placeholder mechanism (if it has one). If you're using an interface that doesn't have any of these capabilities, what is it? Some people might want to avoid it ;-) -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] non-equi self-join optimization
I have a table of names with two subsets of entities. I want to find those names from set 1 that are substrings of names from set 2 from the same table. Basically the pared down query I want is something like this: select t1.myname, t2.myname from mytable t1 inner join mytable t2 on position (t1.myname in t2.myname) > 0 where t1.flag = 1 and t2.flag = 2 ; I have gone through a few variations on the theme, but none perform too well. Any advice on the best way to optimize a query like this would be appreciated. Thanks! George ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Still struggling with history tables
Friends ~
I'm still trying to implement a solution to the requirement to keep a
complete history of data changes to a "person" table. (See earlier
correspondence below.) I'm trying for a variant of the architecture
suggested by Richard Huxton (also below). In my variant, I have this
"identifier" table, carrying the id and invariant info about each person:
/*==*/
/* Table: person_i */
/*==*/
create table person_i (
idi BIGSERIALnot null,
date_of_birthDATE null,
constraint PK_person_i_key_1 primary key (idi)
)
;
And then I have this "history" table, carrying the time-varying info on
which I want to keep a complete history:
/*==*/
/* Table: person_h */
/*==*/
create table person_h (
idh INT8 not null,
start_date DATE not null default 'now()',
end_date DATE null,
name VARCHAR(255) null,
constraint PK_person_h_key_1 primary key (idh, start_date),
constraint fk_reference_6 foreign key (idh)
references person_i (idi)
on delete restrict on update restrict
)
;
Triggers are in place on the "person_h" table so that when an app does an
update, the current h record is expired (with its old data) and a new record
(wuth the updated data)is inserted and made effective "now". What I'm now
trying to build is this view:
/*==*/
/* View: person */
/*==*/
create view person as
select idi, date_of_birth, start_date, end_date, name
from person_i i, person_h h
where i.idi = h.idh;
I want to enable users (and apps) who need to deal only with current data to
be able to treat "person" as a real table (i.e. to write to it as well as
read from it). Specifically, the requirements are that when a user does:
. Insert - The system inserts a record into the i table and the first
record in the h table.
. Select - The system returns attributes of i and h tables (not
duplicating the identifier columns).
. Update - The system allows updating of i attributes
(update-in-place, not creating a new history record) and h attributes
(creating a new history record).
. Delete - The system deletes the i record and all of its h records.
I'm stuck on how to implement the "insert" action, which I thought would be
simple. The problem is this: The i table id is of type BIGSERIAL, i.e.
sequence-assigned. I've tried writing the following rule to get both the i
record and the first h record inserted:
CREATE RULE ru AS
ON INSERT TO person
DO INSTEAD (
INSERT INTO person_i DEFAULT VALUES;
INSERT INTO person_h (idh) VALUES (NEW.idi)
);
I thought this would grab the sequence-assigned value of person_i.idi to put
into person_h.idh (this trick works in trigger functions), but instead it
just assigns Null to person_h.idh, and the transaction fails with a "not
null" violation on person_h.idh. And now that I look at the documentation
(http://www.postgresql.org/docs/7.4/static/rules-update.html), it says that
a column not assigned in the invoking query "is replaced by a null value
(for an INSERT)" in the NEW pseudo-record. Bummer. Triggers behave nicely,
but rules don't.
I'd be willing to do it with a trigger function instead, but I can't attach
a trigger to a view.
I considered doing it with a trigger function on the person_i table, but I
don't know how that could be made to cause an insert of the person_h table
record - and the assignment of h table values such as "name" from the app's
query.
Suggestions?
~ TIA
~ Ken
> -Original Message-
> From: Richard Huxton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, December 20, 2005 4:16 AM
> To: Ken Winter
> Cc: 'PostgreSQL pg-sql list'
> Subject: Re: [SQL] Rule causes baffling error
>
> Ken Winter wrote:
> > Richard ~
> >
> > Let me zoom out for a moment, for the bigger picture.
> >
> > As you have inferred, what I'm trying to do is develop a history-
> preserving
> > table ("my_data" in the example that started this thread). *Most* user
> > programs would see and manipulate this table as if it contained only the
> > current rows (marked by effective_date_and_time <= 'now' and
> > expiration_date_and_time = 'infinity').
> >
> > When these programs do an INSERT, I need automatic actions that set the
> > expiration and date timestamps to 'now' and 'infinity'; when they do an
> > UPDATE, I need automatic actions that save the old data in a history
> record
> > and expire it as of 'now' and the new d
[SQL] Matching several rows
Hi,This is possibly absolutely trivial but I am lost...A table URIGHTS which stores an ID and the RIGHTs this ID has. One ID may have many rights and accordingly records in table, sample:ID RIGHT -20 120 220 520 1030 230 10Now I need to find out which IDs have, say rights 2 AND 5 AND 10.What would be the simplest query? Thanks!
Re: [SQL] Matching several rows
On Jan 18, 2006, at 13:43 , Ivan Steganov wrote:
A table URIGHTS which stores an ID and the RIGHTs this ID has. One
ID may have many rights and accordingly records in table, sample:
Now I need to find out which IDs have, say rights 2 AND 5 AND 10.
select "ID"
from "URIGHTS" as right_2
join "URIGHTS" as right_5 using ("ID")
join "URIGHTS" as right_10 using ("ID")
where right_2."RIGHT" = 2
and right_5."RIGHT" = 5
and right_10."RIGHT" = 10
or
select "ID"
from (
select "ID"
from "URIGHTS"
where "RIGHT" = 2
) as right_2
join (
select "ID"
from "URIGHTS"
where "RIGHT" = 5
) as right_5 using ("ID")
join (
select "ID"
from "URIGHTS"
where "RIGHT" = 10
) as right_10 using ("ID")
Simple is in the eye of the beholder. You might want to compare the
EXPLAIN ANALYZE output to see if there are any significant
differences between these queries.
Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Characters that needs escape characters when inserting to database
I am using Visual Basic as the front end. I connect to postgresql database through ODBC. I encounter some error when I insert value to a field. The error is "syntax error" Which make me think that there are other characters that needs an escape chcracter. Cheers. -Original Message- From: Michael Fuhr [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 17, 2006 9:57 AM To: Michael Glaesemann Cc: Christian Paul B. Cosinas; pgsql-sql@postgresql.org Subject: Re: [SQL] Characters that needs escape characters when inserting to database On Tue, Jan 17, 2006 at 04:20:23PM +0900, Michael Glaesemann wrote: > On Jan 18, 2006, at 7:21 , Christian Paul B. Cosinas wrote: > >Can anyone give me a list of characters that needs to be preceded by > >an escape character before inserting to database. > > Take a look at this documentation on string constants. It should > answer the questions you have. > http://www.postgresql.org/docs/current/interactive/sql-syntax.html#SQL > -SYNTAX-STRINGS Out of curiosity, why are you asking? Unless you're implementing some low-level interface to the database you shouldn't need to worry about escaping strings; just use your API's quote/escape (or whatever) function or its placeholder mechanism (if it has one). If you're using an interface that doesn't have any of these capabilities, what is it? Some people might want to avoid it ;-) -- Michael Fuhr I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
