[SQL] "'" in SQL INSERT statement
Hi, I have some data that I wish to transfer into a database using perl/DBI. Some of the data are strings containing the apostrophe "'" which I use as string delimiter. How can I put these into my database using the INSERT statement? Thanks, Markus
Re: [SQL] "'" in SQL INSERT statement
Markus Wagner wrote:
>Hi,
>
>I have some data that I wish to transfer into a database using perl/DBI.
>Some of the data are strings containing the apostrophe "'" which I use
>as string delimiter.
>
>How can I put these into my database using the INSERT statement?
Escape the apostrophe with another apostrophe or a backslash:
junk=# insert into a (b) values ('John''s text');
INSERT 6815936 1
junk=# select * from a;
a | b
---+-
1 | some text
2 | John's text
(2 rows)
junk=# insert into a (b) values ('Fred\'s text');
INSERT 6815937 1
junk=# select * from a;
a | b
---+-
1 | some text
2 | John's text
3 | Fred's text
(3 rows)
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"My little children, let us not love in word, neither
in tongue; but in deed and in truth."
I John 3:18
Re: [SQL] "'" in SQL INSERT statement
Markus Wagner wrote:
> I have some data that I wish to transfer into a database using perl/DBI.
If you use Perl DBI you should issue statements like
$dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
undef, $value1, $value2);
This binding takes care of quoting and escapes all characters that may
cause problems in the database backed (e.g. "that's" becomes "that''s"
etc.)
There is a DBI mailing list where you can find more info and support:
see
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
Re: [SQL] "'" in SQL INSERT statement
On Thu, 25 Jan 2001, Markus Wagner wrote: > I have some data that I wish to transfer into a database using perl/DBI. > Some of the data are strings containing the apostrophe "'" which I use > as string delimiter. > > How can I put these into my database using the INSERT statement? You will need to escape them with the \ character. So "Bill's Garage" will become "Bill\'s Garage". -- Brett http://www.chapelperilous.net/~bmccoy/ --- Romeo wasn't bilked in a day. -- Walt Kelly, "Ten Ever-Lovin' Blue-Eyed Years With Pogo"
[SQL] Cannot CREATE INDEX that contains a function
I am testing Postgresql-7.0.3 I would like to have an index all in lowercase to speed up this select: SELECT * FROM test WHERE lower(username) = 'max'; Please tell me what am I doing wrong: In psql... testdb=# CREATE TABLE test (username varchar(50)); CREATE testdb=# CREATE UNIQUE INDEX test_index ON test (lower(username)); ERROR: DefineIndex: function 'lower(varchar)' does not exist The same happens using upper, initcap,... Thanks Roberto Bertolusso
Re: [SQL] Rule not invoked in 7.1
Tom Lane wrote: > Kyle <[EMAIL PROTECTED]> writes: > > ERROR: Cannot update a view without an appropriate rule. > > 7.1 insists that you provide an *unconditional* DO INSTEAD rule > for a view. What do you think was happening on your old database > when the "where old.status = 'appr'" clause wasn't satisfied? > Nothing good I'm afraid. No harm in the UPDATE case, because so far there aren't any tuples in the view that could be affected by the still executed original query. But in an INSERT case, it would let tuples through into the views heap file. > If you really do need conditional rules, you can satisfy the check > by writing one unconditional DO INSTEAD NOTHING rule and then one > or more conditional non-INSTEAD rules. But you should think carefully > about what you expect to happen when you use a conditional rule. Alternatively he should be able to move the condition down into the query itself. In that case, it's an unconditional INSTEAD rule, causing the rewriter not to fork off another query but replace the initial one completely. But the condition is still there and affects the effects. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [SQL] unreferenced primary keys: garbage collection
> On Tue, 23 Jan 2001, Forest Wilkinson wrote: > > > Jan, > > > > Thanks for the reply, but your solution is rather unattractive to me. It > > requires that, any time a reference to an address id is changed, five > > tables be searched for the address id. This will create unwanted overhead If - and I think this is the case for you - it is no problem for you to have some superfluous adresses in your tables, but you only want to avoid that those adresses remain there for a long time, you could simply run the function Jan sent from a cron job. That seems to be likely to be more efficient not only than the triggers but also to the ON DELETE RESTRICT solution, I guess. Trivial, but I HTH - Albert. > > Forest Wilkinson wrote: > > >> > I have a database in which five separate tables may (or may not) reference > > >> > any given row in a table of postal addresses. I am using the primary / > > >> > foreign key support in postgres 7 to represent these references. > > >> > > > >> > My problem is that, any time a reference is removed (either by deleting or > > >> > updating a row in one of the five referencing tables), no garbage > > >> > collection is being performed on the address table. That is, when the > > >> > last reference to an address record goes away, the record is not removed > > >> > from the address table. Over time, my database will fill up with > > >> > abandoned address records. -- -- Albert Reiner <[EMAIL PROTECTED]> Deutsch * English * Esperanto * Latine --
Re: [SQL] Rule not invoked in 7.1
Jan Wieck wrote: Tom Lane wrote: > Kyle <[EMAIL PROTECTED]> writes: > > ERROR: Cannot update a view without an appropriate rule. > > 7.1 insists that you provide an *unconditional* DO INSTEAD rule > for a view. What do you think was happening on your old database > when the "where old.status = 'appr'" clause wasn't satisfied? > Nothing good I'm afraid. No harm in the UPDATE case, because so far there aren't any tuples in the view that could be affected by the still executed original query. But in an INSERT case, it would let tuples through into the views heap file. > If you really do need conditional rules, you can satisfy the check > by writing one unconditional DO INSTEAD NOTHING rule and then one > or more conditional non-INSTEAD rules. But you should think carefully > about what you expect to happen when you use a conditional rule. I'm using the view as a way of restricting a single class of users to only update tuples that have a certain status in the table. Isn't this essentially what a "dynamic view" is? If someone happens to know the primary key of a record they should not be able to access, and they try to update it, I would like the backend to ignore the query (or better yet, raise an exception but I haven't figured out how to do that). If the status is correct, the update should proceed. I've inserted the dummy do nothing rule as follows: create view pay_req_v_prl as select empl_id,wdate,seq,hours,hot,proj,entby,paytyp,status,poinum,added,rate,otrate,appby,gross,rgross,cost,ttype,expnum,oid as _oid from pay_req; create rule pay_req_v_prl_upnull as on update to pay_req_v_prl do instead nothing; create rule pay_req_v_prl_update as on update to pay_req_v_prl where old.status = 'appr' do instead update pay_req set status = new.status, gross = new.gross, cost = new.cost, ttype = new.ttype, expnum = new.expnum, rgross = new.rgross, hot = new.hot where empl_id = old.empl_id and wdate = old.wdate and seq = old.seq; This seems to work now when I do: psql ati -c "update pay_req_v_prl set gross = 90.09 where empl_id = 1010 and wdate = '2001-01-08' and seq = 1;" You see any problems with this method? BTW, the update still returns UPDATE 0 from psql even though a record was updated. I've never quite figured out why views with rules do this. I've also done some testing on 7.1 for that nasty thing in 7.0 where you had to give select,update privs to a table referenced by a foreign key. So far, looks good. I was able to reference a table that the user didn't have privs to at all. I think that is the desired behavior. Good work guys! 7.1 is looking good. begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard
[SQL] Re: Inserting and incrementing with MAX aggregate
Ahhh, thank you that worked. I don't know why but for some reason I didn't think I could do a subquery in an insert *laff*- 'learn something new everyday :) Keith- Ian Harding wrote: > Could you not: > > insert into events (eid,name) values ((SELECT max(eid) FROM > EVENTS)+1,'server down'); > > It looks like it would work. It's just a subquery... > > Ian > > Keith Perry wrote: > > > I was wondering if this statement was still supported in 7.0.x versions > > of pgSQL. I upgraded from a 6.x version where say: > > > > insert into events (eid,name) values (max(eid)+1,'server down'); > > > > works but not in the 7.x variants. > > > > I know that I could use the serial type for that column but in the > > interest not having to rewrite the code (or dump, drop and recreate the > > tables/data), I wanted to know if there was a more stand way to > > incrementing a field automatically that would be fairly portable. Any > > help would be appreciated. > > > > Keith Perry > > VCSN Inc. > > [EMAIL PROTECTED] > > http://vcsn.com
Re: [SQL] "'" in SQL INSERT statement
Alessio Bragadini writes:
> Markus Wagner wrote:
> > I have some data that I wish to transfer into a database using perl/DBI.
>
> If you use Perl DBI you should issue statements like
> $dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
> undef, $value1, $value2);
$dbh->quote() also puts in the appropriate escapes, ala:
$dbh->do('INSERT INTO TABLE (field) VALUES ('.$dbh->quote($value).')');
Dan
Re: [SQL] "'" in SQL INSERT statement
Hi,
Using a backslash to escape it.
insert into table(field) values('what\'s that');
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Thu, 25 Jan 2001, Markus Wagner wrote:
> Hi,
>
> I have some data that I wish to transfer into a database using perl/DBI.
> Some of the data are strings containing the apostrophe "'" which I use
> as string delimiter.
>
> How can I put these into my database using the INSERT statement?
>
> Thanks,
>
> Markus
>
[SQL] crypt and decrypt
How can i crypt and decrypt data when insert or selecting in a database?
[SQL] Don't want blank data
Greetings, Is there a way to have postgresql always return a value for each row requested? To be more clear, if I were using a Perl SQL hybrid I would write something like SELECT computer_ip or 'unset' FROM computers; So that if computers.computer_ip is NULL or '' I will get 'unset' back from the database. I hope this makes sense and somebody can point me in a good direction -- Dave
[SQL] Is there anything like DESCRIBE?
Hi, I have a number of empty tables and I want to get the column names and data types with an SQL statement. I want to do this procedurally, not interactively (so I can't use \d in psql). Postgres doesn't support DESCRIBE... is there any other way to do this? Thanks! Mike
Re: [SQL] #DELETED error when using Access 2000 as frontend
Markus Wagner wrote: > Hi, > > I tried to subscribe to pgsql-interfaces several times and received "user not > found". I also searched the pgsql-interfaces archives, without success. So > here is my problem. > > I want to use pg 7.x as a backend for a MS Access application. I linked a > table via ODBC, using the newest ODBC driver. I can open and view tables. But > after I insert a new record, all fields will contain "#deleted". When I > reopen the table, the inserted data is displayed correctly. > > How can I fix this? > > Thank you, > > Markus I recall seeing an MS Access 2000 bug that will cause this. I don't remember the details, but I suspect a search of the knowledgebase at Microsoft (as soon as their dns is fixed - they must be upgrading to windows 2000;^) will yield the answer. Ian
[SQL] Change or get currentdb
Hi, All! Sorry for newby question... How can I change and/or get to know a current db name using sql script in PostgreSQL? Thanks, Sergiy. P.S. it seems use doesn't work :-(
Re: [SQL] Change or get currentdb
>From \? \c[onnect] [dbname|- [user]] connect to new database (currently '') so typing "\c" gives you the database you're currently connected to and "\c " would connect you to that database. On Thu, 25 Jan 2001, Sergiy Ovcharuk wrote: ->How can I change and/or get to know a current db name using sql script in ->PostgreSQL? -- Dave
Re: [SQL] Don't want blank data
On Thu, 25 Jan 2001, David Olbersen wrote: > Greetings, > Is there a way to have postgresql always return a value for each row > requested? To be more clear, if I were using a Perl SQL hybrid I would write > something like > > SELECT computer_ip or 'unset' FROM computers; > > So that if computers.computer_ip is NULL or '' I will get 'unset' back from > the database. I hope this makes sense and somebody can point me in a good > direction Perhaps: select case when computer_ip is null or computer_ip='' then 'unset'::text else computer_ip end from computers; (the ::text should probably be whatever type computer_ip is)...
[SQL] RE: Is there anything like DESCRIBE?
This works for me: SELECT DISTINCT c.relname as table_name, a.attname as column_name, t.typname, pa.adsrc as default FROM (pg_attribute a join pg_class c on a.attrelid = c.oid join pg_type t on a.atttypid = t.oid) left join pg_attrdef pa on c.oid = pa.adrelid AND a.attnum = pa.adnum where exists (select * from pg_tables where tablename = c.relname and substr(tablename,1,2) <> 'pg') order by c.relname, a.attname; -Original Message- From: Mike D'Agosta [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, January 24, 2001 12:01 PM To: [EMAIL PROTECTED] Subject:Is there anything like DESCRIBE? Hi, I have a number of empty tables and I want to get the column names and data types with an SQL statement. I want to do this procedurally, not interactively (so I can't use \d in psql). Postgres doesn't support DESCRIBE... is there any other way to do this? Thanks! Mike
Re: [SQL] Is there anything like DESCRIBE?
Mike D'Agosta wrote: > > Hi, > >I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? > > Thanks! > Mike like this drfrog=# \d messages Table "messages" Attribute | Type | Modifier ---+-+-- id| integer | not null msgid | text| boxid | integer | accountid | integer | date | text| sentto| text| sentfrom | text| subject | text| contenttype | text| contentxferencode | text| mimeversion | text| precedence| text| approvedby| text| inreplyto | text| replyto | text| listsub | text| listunsub | text| status| text| xorigip | text| cc| text| bcc | text| sender| text| returnpath| text| priority | text| xmailer | text| xuidl | text| xsender | text| localdate | text| newmsg| text| replyf| integer | friendly | text| rreceiptto| text| score | integer | ref | text| serverstat| integer | Index: messages_pkey
Re: [SQL] Is there anything like DESCRIBE?
"Mike D'Agosta" wrote: >Hi, > > I have a number of empty tables and I want to get the column names and >data types with an SQL statement. I want to do this procedurally, not >interactively (so I can't use \d in psql). Postgres doesn't >support DESCRIBE... is there any other way to do this? If you run psql with the -E option, it will show you the query it uses when you type `\d+`. Use that query in your procedural code. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My little children, let us not love in word, neither in tongue; but in deed and in truth." I John 3:18
Re: [SQL] Don't want blank data
David Olbersen wrote: >Greetings, > Is there a way to have postgresql always return a value for each row > requested? To be more clear, if I were using a Perl SQL hybrid I would wri >te > something like > > SELECT computer_ip or 'unset' FROM computers; > > So that if computers.computer_ip is NULL or '' I will get 'unset' back fro >m > the database. I hope this makes sense and somebody can point me in a good > direction SELECT COALESCE(computer_ip,'unset') AS computer_ip FROM computers; COALESCE() returns the leftmost non-null value from its parameters. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "My little children, let us not love in word, neither in tongue; but in deed and in truth." I John 3:18
[SQL] Re: Problem with Dates
template1=# select '31/12/2000'::date; ?column? 2000-12-31 (1 row) template1=# select '31/12/2000'::date + '365 days'::timespan; ?column? 2002-01-01 00:00:00+13<<< Wrong (1 row) template1=# select '31/12/2000'::date + '364 days'::timespan; ?column? 2001-12-31 00:00:00+13 (1 row) template1=# select '31/12/2000'::date + '363 days'::timespan; ?column? 2001-12-30 00:00:00+13 (1 row) > Not sure if gets you anywhere. But data points. > > There is an email archive on the postgresql.org website you could search if > you think it's been answered before. I have tried looking here but it is pretty hard to know what to look for. Glen.
[SQL] Re: Is there anything like DESCRIBE?
Mike D'Agosta wrote: > > Hi, > >I have a number of empty tables and I want to get the column names and > data types with an SQL statement. I want to do this procedurally, not > interactively (so I can't use \d in psql). Postgres doesn't > support DESCRIBE... is there any other way to do this? src/tutorial/syscat.source has some examples you might like to study. -Ron-
Re: [SQL] Cannot CREATE INDEX that contains a function
Roberto Bertolusso <[EMAIL PROTECTED]> writes: > testdb=# CREATE TABLE test (username varchar(50)); > CREATE > testdb=# CREATE UNIQUE INDEX test_index ON test (lower(username)); > ERROR: DefineIndex: function 'lower(varchar)' does not exist Short answer in 7.0.* is to make the column be type text not varchar. 7.1 is more flexible about this ... regards, tom lane
[SQL] Re: abstract data types?
Hi Tom, listers, Thanks for the info. > On Sat, 20 Jan 2001, Tom Lane wrote: > > >> None, I fear. The stuff you are fooling with is leftover from the old >> PostQuel language. Most of it is suffering from bit rot, because the >> developers' focus has been on SQL92 compliance for the last six or seven >> years. > Damn! Not what I wanted to hear :-( >> I hadn't realized that SQL99 had caught up to PostQuel in this >> area ;-). > FWIW, this is actually one of the primary reasons that I became interested in PostgreSQL, before I even knew about SQL3/SQL99. Seems like such a cool idea :-) >> Sounds like we will have to dust off some of that stuff and >> get it working again. No promises about timeframe, unless someone >> steps up to the plate to do the work... > OK, what few coding skills I had are so rusty I'm pretty much back to square one, but I would like to help out where possible (Docs maybe?). Then again, might as well jump in the deep end, and have a look to see what needs doing anyway :-) Can you please give me some pointers as to where I should look in the docs and code to see how classes are currently handled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creating classes hides * Anything else that I should be aware of! For the moment I guess I don't need to worry about the parser, just how the operations related to the classes (both system and user) work/are implemented. Correct? > What goes around comes around. :-) And hits you in the back of the head just when you least expect it ... cheers, John -- john reid e-mail [EMAIL PROTECTED] uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... --
[SQL] Re: abstract data types?
Hi Josh et al, Sorry for the tardy reply, and thanks for your comments. Any suggestions or pointers on robust database design will be greatly appreciated. Josh Berkus wrote: > Jim, > > >>> I'm trying to figure out what support PostgreSQL >> >> offers for SQL99 >> >>> abstract data types. >> > I'm a little curious why what you're attempting couldn't be > done with two columns rather than inventing your own data > type. As somebody who often rescues databases gone bad, > composite data types have not earned a warm place in my > heart ... > > -Josh Berkus What we are attempting is the storage of vector data for a geographical (or spatial) information system in a database. We hope to base the implementation on the upcoming standard from the ISO TC/211 committee. More information can be found at http://FMaps.sourceforge.net/ - the webpages need a major revamp so the best place to look for current developments is in the mailing list archive. A good source of info can be found at http://gdal.velocet.ca/projects/osvecdb/index.html, especially relevant is the comparison of the SQL/MM, OGC, and ISO TC/211 standards (http://gdal.velocet.ca/projects/osvecdb/comp-mm-ogc-tc211.pdf ). To answer your question, it is a bit hard to say at the moment as the design schema for our project has only just been started. The draft versions of the ISO standard that I have seen use an object oriented data model, so to me it makes sense to try and keep the database schema as close as possible to this (minimise data impedance). Briefly, at its' simplest the schema will probably use a two tier approach. Tier 0ne -- The original data stored in the most flexible way that we can think of, with associated metadata tables. Tier Two --- These will effectively be persistent views on the T1 tables structured for efficient access by client applications. OK, as far I know no such beast as a persistent view exists in the SQL standards, but that is probably the best way to describe what I have in mind. Using views as currently implemented in PostgreSQL would probably not be viable as it is likely that, if multiple spatial reference systems are defined on a area of interest, reprojection of the geometry objects would be a performance killer. cheers, John
