[SQL] INSERT INTO
Hello
I have 2 tables. TABLE1 and TABLE2.
TABLE1 has about 400 records with details (client code, name, surname, address,
date of birth) of my clients.
TABLE2 is filled with some of the client details and other extra details
(client code, address, telephone, etc)
So in my ASP page i have a select that gets the client details from TABLE1
using the client code and inserts them in to TABLE2
When i have a string that has a ' inside of it the record is not inserted in to
TABLE2. I know that writing it twice will fix it but how can i ask my ASP code
to do it..
INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');
Thanks
Shavonne Wijesinghe
http://www.studioform.it
Re: [SQL] INSERT INTO
I don't use ASP but in PHP I would do something in the line of
$valuetoinsert = "SANT'ANGELO LODIGIANO";
$query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'", "''",
$valuetoinsert) . "')";
I'm sure ASP has also a string replacement function
>>> "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]> 2007-03-16 9:50 >>>
Hello
I have 2 tables. TABLE1 and TABLE2.
TABLE1 has about 400 records with details (client code, name, surname, address,
date of birth) of my clients.
TABLE2 is filled with some of the client details and other extra details
(client code, address, telephone, etc)
So in my ASP page i have a select that gets the client details from TABLE1
using the client code and inserts them in to TABLE2
When i have a string that has a ' inside of it the record is not inserted in to
TABLE2. I know that writing it twice will fix it but how can i ask my ASP code
to do it..
INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');
Thanks
Shavonne Wijesinghe
http://www.studioform.it ( http://www.studioform.it/ )
Re: [SQL] INSERT INTO
Bart Degryse a ecrit le 16/03/2007 10:03:
I don't use ASP but in PHP I would do something in the line of
$valuetoinsert = "SANT'ANGELO LODIGIANO";
$query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'",
"''", $valuetoinsert) . "')";
I'm sure ASP has also a string replacement function
This doesn't answer Shavonne's question but, instead of str_replace, you
should better use pg_escape_string :
http://www.php.net/manual/en/function.pg-escape-string.php
Regards.
--
Guillaume.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Fw: [SQL] INSERT INTO
thanks.. i will take a look at the link
Shavonne Wijesinghe
http://www.studioform.it
Le informazioni contenute nella presente comunicazione e i relativi allegati
possono essere riservate e sono, comunque destinate esclusivamente alle
persone o alla Società sopra indicati.
La diffusione, distribuzione e/o copiature del documento trasmesso da parte
di qualsiasi soggetto diverso dal destinatario è proibita, sia ai sensi dell'art.
616 c.p., che ai sensi del D. Lgs. n. 196/2003.
Se avete ricevuto questo messaggio per errore, Vi preghiamo di distruggerlo
e di informarci immediatamente per telefono allo 0039362595044 o inviando un
messaggio all'indirizzo e-mail
[EMAIL PROTECTED]
The informations in this communication is confidential and may also be
legally privileged. It is intended for the addressee only.
Access to this e-mail by anyone else is unauthorized. It is not to be relied
upon by any person other than the addressee, except with our prior written
approval. If you received this message please send an e-mail to the se
- Original Message -
From: "Guillaume Lelarge" <[EMAIL PROTECTED]>
To: "Bart Degryse" <[EMAIL PROTECTED]>
Cc: ; "Shavonne Marietta Wijesinghe"
<[EMAIL PROTECTED]>
Sent: Friday, March 16, 2007 10:16 AM
Subject: Re: [SQL] INSERT INTO
Bart Degryse a ecrit le 16/03/2007 10:03:
I don't use ASP but in PHP I would do something in the line of
$valuetoinsert = "SANT'ANGELO LODIGIANO";
$query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'",
"''", $valuetoinsert) . "')";
I'm sure ASP has also a string replacement function
This doesn't answer Shavonne's question but, instead of str_replace, you
should better use pg_escape_string :
http://www.php.net/manual/en/function.pg-escape-string.php
Regards.
--
Guillaume.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] INSERT INTO
i took a look at the link Guillaume gave me. But it also explains on PHP
i'm using ASP :(
- Original Message -
From: "Guillaume Lelarge" <[EMAIL PROTECTED]>
To: "Bart Degryse" <[EMAIL PROTECTED]>
Cc: ; "Shavonne Marietta Wijesinghe"
<[EMAIL PROTECTED]>
Sent: Friday, March 16, 2007 10:16 AM
Subject: Re: [SQL] INSERT INTO
Bart Degryse a ecrit le 16/03/2007 10:03:
I don't use ASP but in PHP I would do something in the line of
$valuetoinsert = "SANT'ANGELO LODIGIANO";
$query = "INSERT INTO TABLE2 (TE_INDI) VALUES ('" . str_replace("'",
"''", $valuetoinsert) . "')";
I'm sure ASP has also a string replacement function
This doesn't answer Shavonne's question but, instead of str_replace, you
should better use pg_escape_string :
http://www.php.net/manual/en/function.pg-escape-string.php
Regards.
--
Guillaume.
---(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
Re: [SQL] Joins on many-to-many relations.
On Wednesday 14 March 2007 22:59, Wiebe Cazemier wrote: > My question was mainly general; > if there is a better way than using subselects to join two tables which > are only connected to eachother through a join-table (containing only > references to the two tables in question). Subselects are usually very > slow, aren't they? I fixed it. I now have a query with two outer joins, instead of the subselect in the join condition: SELECT DISTINCT ON (account.id) account.* FROM trade.transaction_accounts AS account INNER JOIN people.people AS owner ON owner.id = account.owner_id LEFT OUTER JOIN trade.transaction_account_co_owners acct_co_owner ON account.id = acct_co_owner.account_id LEFT OUTER JOIN people.people AS co_owner ON acct_co_owner.co_owner_id = co_owner.id WHERE upper(account.description) LIKE '%KOE%' OR upper(owner.name) LIKE '%KOE%' OR upper(owner.familiar_name) LIKE '%KOE%' OR upper(owner.full_name) LIKE '%KOE%' OR upper(co_owner.name) LIKE '%KOE%' OR upper(co_owner.familiar_name) LIKE '%KOE%' OR upper(co_owner.full_name) LIKE '%KOE%' And now it executes in 1 ms. This is what I was trying to do from the beginning, but because of some mental block, was unable to think of the join condition... ---(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
Re: [SQL] INSERT INTO
Shavonne Marietta Wijesinghe a ecrit le 16/03/2007 11:31: i took a look at the link Guillaume gave me. But it also explains on PHP i'm using ASP :( Yes, that's why I said that it didn't answer your question. Sorry if this wasn't clear. After a bit of googling, I found the replace function. Something like that : <%= replace(string,"'","''") %> might work. But remember I don't use ASP. Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] INSERT INTO
> When i have a string that has a ' inside of it the record is not inserted in
> to TABLE2. I know
> that writing it twice will fix it but how can i ask my ASP code to do it..
>
> INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');
If you are using PostgreSQL >= 8.0 you can use dollars sign quoting like the
following:
INSERT INTO TABLE2 (TE_INDI) VALUES ($SANT'ANGELO LODIGIANO$);
or even a custom quotation identifier:
INSERT INTO TABLE2 (TE_INDI) VALUES ($myquote$SANT'ANGELO LODIGIANO$myquote$);
Would this work in ASP?
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] INSERT INTO
> INSERT INTO TABLE2 (TE_INDI) VALUES ($SANT'ANGELO LODIGIANO$); My mistake, a single $ will not work you need two like $$: INSERT INTO TABLE2 (TE_INDI) VALUES ($$SANT'ANGELO LODIGIANO$$); Regards, Richard Broersma Jr. ---(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
Re: [SQL] INSERT INTO
Thanks alot ^_^ Shavonne Wijesinghe http://www.studioform.it - Original Message - From: "Richard Broersma Jr" <[EMAIL PROTECTED]> To: "Richard Broersma Jr" <[EMAIL PROTECTED]>; "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>; Sent: Friday, March 16, 2007 2:08 PM Subject: Re: [SQL] INSERT INTO INSERT INTO TABLE2 (TE_INDI) VALUES ($SANT'ANGELO LODIGIANO$); My mistake, a single $ will not work you need two like $$: INSERT INTO TABLE2 (TE_INDI) VALUES ($$SANT'ANGELO LODIGIANO$$); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] INSERT INTO
Shavonne Marietta Wijesinghe wrote: > Thanks alot What happens if you try to insert a string with $$ on it? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Grabbing Newest Records From Duplicates
Given the following test table, I want to grab only the newest record and
disregard any older duplicates based on name. Is this the most efficient way
to do it? Will the indicies even make a difference? The table below
demonstrates a simple proof of concept. My final table will have millions of
records; however, the test I'm doing now does not seem to use the indicies
(probably because there are too few records?).
optin=# \d test
Table "public.test"
Column | Type | Modifiers
+-+---
name | text|
time | date|
id | integer |
Indexes:
"idx_name" btree (name)
"idx_time" btree ("time")
optin=# explain select * from test t1 where not exists (select 1 from test
t2 where t2.name = t1.name and t2.time > t1.time);
Thanks,
Travis
Re: [SQL] Grabbing Newest Records From Duplicates
"Travis Whitton" <[EMAIL PROTECTED]> writes: > Given the following test table, I want to grab only the newest record and > disregard any older duplicates based on name. Is this the most efficient way > to do it? No, it's gonna be pretty awful. The best way I know of involves DISTINCT ON (see the "weather reports" example in the SELECT reference page). Unfortunately that's a Postgres-only construct. If you want something portable then you'll need something messy with subqueries... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Grabbing Newest Records From Duplicates
One last question, using the "weather report" example, is it going to improve performance for the DISTINCT ON query to add an index to the location and time columns? Thanks a lot, Travis On 3/16/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Travis Whitton" <[EMAIL PROTECTED]> writes: > Given the following test table, I want to grab only the newest record and > disregard any older duplicates based on name. Is this the most efficient way > to do it? No, it's gonna be pretty awful. The best way I know of involves DISTINCT ON (see the "weather reports" example in the SELECT reference page). Unfortunately that's a Postgres-only construct. If you want something portable then you'll need something messy with subqueries... regards, tom lane
[SQL] log file permissions?
Hi dudes. I have my pg log file rotation configured so i have a psql_`today`.log. The thing is, can i configure postgres so the file permissions (today is 600) could by 640, so developers could login and tailf the logfile (without using the postgres superuser, course)? Thanks! Gerardo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Grabbing Newest Records From Duplicates
"Travis Whitton" <[EMAIL PROTECTED]> writes: > One last question, using the "weather report" example, is it going to > improve performance for the DISTINCT ON query to add an index to the > location and time columns? A two-column index matching the ORDER BY that you use might help, or then again might not --- for full-table scans it's often faster to sort than to try to use an index. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] log file permissions?
Gerardo Herzig wrote: > Hi dudes. I have my pg log file rotation configured so i have a > psql_`today`.log. The thing is, can i configure postgres so the file > permissions (today is 600) could by 640, so developers could login and > tailf the logfile (without using the postgres superuser, course)? This really belongs over in admin. Are you logging through syslog or stdout (ie. what are the following settings in postgresql.conf and are they in any way overridden by your startup scripts)?: syslog = syslog_facility = syslog_ident = If your are logging through syslog, then postgres has no control over those permissions. You will have to look at your syslog daemon configuration. Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] log file permissions?
Gerardo Herzig <[EMAIL PROTECTED]> writes: > Hi dudes. I have my pg log file rotation configured so i have a > psql_`today`.log. The thing is, can i configure postgres so the file > permissions (today is 600) could by 640, so developers could login and > tailf the logfile (without using the postgres superuser, course)? You'd have to change the code --- the syslogger process inherits umask 077 from the postmaster. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] INSERT INTO
From: "Shavonne Marietta Wijesinghe" <[EMAIL PROTECTED]>
> I have 2 tables. TABLE1 and TABLE2.
>
> TABLE1 has about 400 records with details (client code, name, surname,
> address, date of birth) of my clients.
> TABLE2 is filled with some of the client details and other extra details
> (client code, address, telephone, etc)
>
> So in my ASP page i have a select that gets the client details from TABLE1
> using the client code and inserts them in to TABLE2
>
> When i have a string that has a ' inside of it the record is not inserted in
> to TABLE2. I know that writing it twice will fix it but how can i ask my ASP
> code to do it..
I'm sadly tasked with maintaining a little ASP code from time to time. If you
need to do it through a script, what you're looking for is the Replace
function, as already mentioned in the thread:
Replace( InputString, "'", "''" )
However since we're already on pgsql-sql, assuming you're not doing a whole lot
more than pulling from one table and inserting into another, you may want to
consider seeing if you can roll it into a single SQL statement:
> INSERT INTO TABLE2 (TE_INDI) VALUES ('SANT'ANGELO LODIGIANO');
INSERT INTO TABLE2 (TE_INDI) VALUES SELECT TE_INDI FROM TABLE1 WHERE (...)
No quoting or server->client->server worries to deal with at all...
Best of luck,
- Josh Williams
---(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
