pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert into
13+ messages / 8 participants
[nested] [flat]
* Insert into
@ 2004-01-12 09:12 azwa@nc.com.my
0 siblings, 1 reply; 13+ messages in thread
From: azwa@nc.com.my @ 2004-01-12 09:12 UTC (permalink / raw)
To: pgsql-sql
Hi,
i've structure table like below and want to load data from temp table
into table below :
dwnc=> \d cust_lo_dim
Table "biosadm.cust_lo_dim"
Column | Type | Modifiers
-----------+-----------------------+------------------------------------------
lo_key | integer | not null default
nextval('clo_seq'::text)
lo_no | character varying(25) | not null
lo_date | date |
rcvdate | date |
lo_status | character varying(15) |
Indexes: cust_lo_dim_pkey primary key btree (lo_key)
my temp table as below:
dwnc=> \d custlo_temp;
Table "biosadm.custlo_temp"
Column | Type | Modifiers
----------+---------------+-----------
lono | text |
lodate | text |
rcvdate | text |
loamount | numeric(10,2) |
custname | text |
status | text |
My SELECT STATEMENT :
dwnc=> insert into cust_lo_dim
dwnc-> (lo_no,lo_date,rcvdate,lo_status)
dwnc-> select c.lono,c.lodate,c.rcvdate,c.status
dwnc-> from custlo_temp c ;
ERROR: column "lo_date" is of type date but expression is of type text
You will need to rewrite or cast the expression
Questions :
1) How to rewrite /cast the expression above ??? same goes to others
column .
2) lo_key is the column which values comes from sequence clo_seq. what
should i do first b4 insert into cust_lo_dim ????
i appreciate for the help. TQ
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Insert into
@ 2004-01-13 10:15 Gabriel Dovalo Carril <dovalo@terra.es>
parent: azwa@nc.com.my
0 siblings, 0 replies; 13+ messages in thread
From: Gabriel Dovalo Carril @ 2004-01-13 10:15 UTC (permalink / raw)
To: azwa@nc.com.my; +Cc: pgsql-sql
El lun, 12-01-2004 a las 10:12, azwa@nc.com.my escribió:
>
> Hi,
> ..../....
>
> My SELECT STATEMENT :
>
> dwnc=> insert into cust_lo_dim
> dwnc-> (lo_no,lo_date,rcvdate,lo_status)
> dwnc-> select c.lono,c.lodate,c.rcvdate,c.status
> dwnc-> from custlo_temp c ;
> ERROR: column "lo_date" is of type date but expression is of type
> text
> You will need to rewrite or cast the expression
> Questions :
>
> 1) How to rewrite /cast the expression above ??? same goes to
> others column .
Insert into cust_lo_dim
(lo_no, lo_date, rcvdate, lo_status)
select c.lono, c.lodate::date, c.rcvdate::date, c.status
from custlo_temp c;
> 2) lo_key is the column which values comes from sequence clo_seq. what
> should i do first b4 insert into cust_lo_dim ????
???
Nothing.
Are you getting some error?
--
Gabriel Dovalo
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 13+ messages in thread
* INSERT INTO
@ 2007-03-16 08:50 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 3 replies; 13+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-03-16 08:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 09:03 Bart Degryse <Bart.Degryse@indicator.be>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2 siblings, 1 reply; 13+ messages in thread
From: Bart Degryse @ 2007-03-16 09:03 UTC (permalink / raw)
To: pgsql-sql; +Cc: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
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" <shavonne.marietta@studioform.it> 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/ )
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 09:16 Guillaume Lelarge <guillaume@lelarge.info>
parent: Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 1 reply; 13+ messages in thread
From: Guillaume Lelarge @ 2007-03-16 09:16 UTC (permalink / raw)
To: Bart Degryse <Bart.Degryse@indicator.be>; +Cc: pgsql-sql@postgresql.org, Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Fw: INSERT INTO
@ 2007-03-16 09:26 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 13+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-03-16 09:26 UTC (permalink / raw)
To: pgsql-sql
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
amministrazione@studioform.it
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" <guillaume@lelarge.info>
To: "Bart Degryse" <Bart.Degryse@indicator.be>
Cc: <pgsql-sql@postgresql.org>; "Shavonne Marietta Wijesinghe"
<shavonne.marietta@studioform.it>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 10:31 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 13+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-03-16 10:31 UTC (permalink / raw)
To: pgsql-sql
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" <guillaume@lelarge.info>
To: "Bart Degryse" <Bart.Degryse@indicator.be>
Cc: <pgsql-sql@postgresql.org>; "Shavonne Marietta Wijesinghe"
<shavonne.marietta@studioform.it>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 12:07 Guillaume Lelarge <guillaume@lelarge.info>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 13+ messages in thread
From: Guillaume Lelarge @ 2007-03-16 12:07 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 13:02 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2 siblings, 1 reply; 13+ messages in thread
From: Richard Broersma Jr @ 2007-03-16 13:02 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 13:08 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Richard Broersma Jr @ 2007-03-16 13:08 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 13:37 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 13+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2007-03-16 13:37 UTC (permalink / raw)
To: pgsql-sql
Thanks alot
^_____________^
Shavonne Wijesinghe
http://www.studioform.it
----- Original Message -----
From: "Richard Broersma Jr" <rabroersma@yahoo.com>
To: "Richard Broersma Jr" <rabroersma@yahoo.com>; "Shavonne Marietta
Wijesinghe" <shavonne.marietta@studioform.it>; <pgsql-sql@postgresql.org>
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-16 14:45 Alvaro Herrera <alvherre@commandprompt.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
0 siblings, 0 replies; 13+ messages in thread
From: Alvaro Herrera @ 2007-03-16 14:45 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
Shavonne Marietta Wijesinghe wrote:
> Thanks alot
What happens if you try to insert a string with $$ on it?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: INSERT INTO
@ 2007-03-17 01:19 Josh Williams <drykath@runbox.com>
parent: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2 siblings, 0 replies; 13+ messages in thread
From: Josh Williams @ 2007-03-17 01:19 UTC (permalink / raw)
To: shavonne.marietta@studioform.it; +Cc: pgsql-sql
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2007-03-17 01:19 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-12 09:12 Insert into azwa@nc.com.my
2004-01-13 10:15 ` Gabriel Dovalo Carril <dovalo@terra.es>
2007-03-16 08:50 INSERT INTO Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-16 09:03 ` Re: INSERT INTO Bart Degryse <Bart.Degryse@indicator.be>
2007-03-16 09:16 ` Re: INSERT INTO Guillaume Lelarge <guillaume@lelarge.info>
2007-03-16 10:31 ` Re: INSERT INTO Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-16 12:07 ` Re: INSERT INTO Guillaume Lelarge <guillaume@lelarge.info>
2007-03-16 13:02 ` Re: INSERT INTO Richard Broersma Jr <rabroersma@yahoo.com>
2007-03-16 13:08 ` Re: INSERT INTO Richard Broersma Jr <rabroersma@yahoo.com>
2007-03-16 13:37 ` Re: INSERT INTO Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2007-03-16 14:45 ` Re: INSERT INTO Alvaro Herrera <alvherre@commandprompt.com>
2007-03-17 01:19 ` Re: INSERT INTO Josh Williams <drykath@runbox.com>
2007-03-16 09:26 Fw: INSERT INTO Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox