[HACKERS] problem with copy command
dear all, I currently using postgresql v7.0.3 when i import text file to table with command "copy tablename from '/tmp/a.txt'; and it shows "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk" ,then it exits with doing nothing. I want to ignore this errors and continue copy the next record. How to do that? if I don't filter in '/tmp/a.txt' before using copy command. Thank you so much for your help in advance . Regards Jaruwan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
RE: [SQL] Sorting and then...
Title: RE: [SQL] Sorting and then... But if you want the largest 10, then you can: SELECT name FROM ORDER BY id DESC LIMIT 10; assuming that you mean largest numerically. Cheers... MikeA >> -Original Message- >> From: Roberto Mello [mailto:[EMAIL PROTECTED]] >> Sent: 10 April 2001 03:41 >> To: [EMAIL PROTECTED] >> Cc: [EMAIL PROTECTED] >> Subject: Re: [SQL] Sorting and then... >> >> >> On Mon, Apr 09, 2001 at 07:22:52PM -0400, Wei Weng wrote: >> >> > And I want to get the names of the largest 10 "id"s. How >> can I do that in >> > sql? >> >> What do you mean by "largest"? Largest id? "largest" >> text string? If >> it's the id you can do: >> >> select max(id) from ; >> >> -Roberto >> -- >> +| http://fslc.usu.edu USU Free Software & GNU/Linux >> Club |--+ >> Roberto Mello - Computer Science, USU - http://www.brasileiro.net >> http://www.sdl.usu.edu - Space Dynamics Lab, Developer >> Backup is for whimps! >> >> ---(end of >> broadcast)--- >> TIP 3: 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 >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
[SQL] Copying null values
Hi all,
I am using:
Debian 2.2 (kernel 2.2.18)
PgSQL version 7.0.3
I would like empty fields from a file being recognised as NULL values.
It doesn't seem to work when a COPY is done, but when I make an INSERT
without
specifying the value, this is recognised as a NULL
The file I want to copy is:
,0,Unknown,01/01/3000,01/01/3000,01/01/3000,0,
1,3,,01/01/3000,01/01/3000,01/01/3000,0,
2,,Unknown,01/01/3000,01/01/3000,01/01/3000,0,
What I am doing is:
--
CREATE TABLE bill(
bill_id SERIAL PRIMARY KEY,
code INTEGER,
bill_number CHAR(20),
issue_date DATE DEFAULT CURRENT_DATE NOT NULL,
expire_date DATE,
negotiate_date DATE,
amount DECIMAL(9,2) NOT NULL,
comment TEXT
);
CREATE FUNCTION bill_id_max()
RETURNS INT AS 'SELECT max(bill_id) FROM bill'
LANGUAGE 'sql';
COPY bill FROM '/home/amanda/informatica/provas/bills-copy.dat' USING
DELIMITERS ',' WITH NULL AS '\,';
SELECT setval('bill_bill_id_seq',bill_id_max());
INSERT INTO bill (code,amount) VALUES (34,10);
INSERT INTO bill (bill_number,amount) VALUES ('494949/949',10);
SELECT * FROM bill WHERE bill_number IS NULL;
SELECT * FROM bill WHERE code IS NULL;
COPY bill TO '/tmp/copytest.out' USING DELIMITERS ',';
The result is:
setval
2
(1 row)
INSERT 29322 1
INSERT 29323 1
bill_id | code | bill_number | issue_date | expire_date |
negotiate_date | amount | comment
-+--+-++-++---+-
3 | 34 | | 2001-04-10 |
|| 10.00 |
(1 row)
bill_id | code | bill_number | issue_date | expire_date |
negotiate_date | amount | comment
-+--+--++-++---+-
4 | | 494949/949 | 2001-04-10 |
|| 10.00 |
(1 row)
COPY
This is the result file /tmp/copytest.out
0,0,Unknown ,3000-01-01,3000-01-01,3000-01-01,0.00,
1,3,,3000-01-01,3000-01-01,3000-01-01,0.00,
2,0,Unknown ,3000-01-01,3000-01-01,3000-01-01,0.00,
3,34,\N,2001-04-10,\N,\N,10.00,\N
4,\N,494949/949 ,2001-04-10,\N,\N,10.00,\N
Thanks
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] The Momjian Tome / DBA Certification
Hey Bruce,
Your book finally hit the bookshelves of Staten Island.
We have a Barnes and Noble here that actually has a reasonable
database section although it is misnamed 'Networking'. ;-)
I'll be getting my copy on payday - I found the sql examples
that I downloaded very useful and as I've been running Pg in
production since 6.3.2 I'd like to support the project.
I have a question as well. Any chance you folks or Great Bridge
would offer Pg*DBA certification exams?
Cheers,
Tom
SVCMC - Center for Behavioral Health
Thomas Good tomg@ { admin | q8 } .nrnet.org
Database Administrator Phone: 718-354-5528
Staten Island Region Fax: 718-354-5056
Powered by: PostgreSQL s l a c k w a r e FreeBSD:
RDBMS |-- linux The Power To Serve
/* Jeder Jeck ist anders! */
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Copying null values
On Tuesday, 10. April 2001 14:17, Amanda Riera wrote: > I would like empty fields from a file being recognised as NULL > values. It doesn't seem to work when a COPY is done, but when I make > an INSERT without > specifying the value, this is recognised as a NULL > > The file I want to copy is: > > ,0,Unknown,01/01/3000,01/01/3000,01/01/3000,0, > 1,3,,01/01/3000,01/01/3000,01/01/3000,0, > 2,,Unknown,01/01/3000,01/01/3000,01/01/3000,0, > > What I am doing is: > > COPY bill FROM '/home/amanda/informatica/provas/bills-copy.dat' USING > DELIMITERS ',' WITH NULL AS '\,'; Just try COPY ... WITH NULL AS ''; HTH, Christof -- gl.aser . software engineering . internet service http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] \i command
Hi All, From pgsql, I try to insert data in table using the \i command. Nothing takes place and after this command pgsql is hung... i cant use it any more. The same command works with all other tables but this is only one I am having problem with. I have attached my file with message. Could someone help me out here what is the reason for this behaviour. I am using postgres7.03 on RH 6.2. CREATE TABLE "tracks" ( "song_id" int4, "coll_id" int4, "track_no" int4 ); INSERT INTO "tracks" VALUES (1,2,1); INSERT INTO "tracks" VALUES (2,2,2); INSERT INTO "tracks" VALUES (3,2,3); INSERT INTO "tracks" VALUES (4,2,4); INSERT INTO "tracks" VALUES (5,2,5); INSERT INTO "tracks" VALUES (6,2,6); INSERT INTO "tracks" VALUES (7,2,7); INSERT INTO "tracks" VALUES (8,2,8); INSERT INTO "tracks" VALUES (9,2,9); INSERT INTO "tracks" VALUES (10,2,10); INSERT INTO "tracks" VALUES (11,58,12); INSERT INTO "tracks" VALUES (12,58,1); INSERT INTO "tracks" VALUES (13,3,12); INSERT INTO "tracks" VALUES (14,59,3); INSERT INTO "tracks" VALUES (15,61,2); INSERT INTO "tracks" VALUES (16,61,5); INSERT INTO "tracks" VALUES (17,3,6); INSERT INTO "tracks" VALUES (18,59,1); INSERT INTO "tracks" VALUES (19,61,4); INSERT INTO "tracks" VALUES (20,3,4); INSERT INTO "tracks" VALUES (21,59,2); INSERT INTO "tracks" VALUES (22,55,10); INSERT INTO "tracks" VALUES (24,4,3); INSERT INTO "tracks" VALUES (25,4,7); INSERT INTO "tracks" VALUES (26,4,1); INSERT INTO "tracks" VALUES (27,4,10); INSERT INTO "tracks" VALUES (28,5,8); INSERT INTO "tracks" VALUES (29,5,1); INSERT INTO "tracks" VALUES (30,5,NULL); INSERT INTO "tracks" VALUES (31,6,1); INSERT INTO "tracks" VALUES (32,39,1); INSERT INTO "tracks" VALUES (33,39,2); INSERT INTO "tracks" VALUES (34,39,3); INSERT INTO "tracks" VALUES (35,39,4); INSERT INTO "tracks" VALUES (36,39,5); INSERT INTO "tracks" VALUES (37,39,6); INSERT INTO "tracks" VALUES (38,39,7); INSERT INTO "tracks" VALUES (39,39,8); INSERT INTO "tracks" VALUES (40,39,9); INSERT INTO "tracks" VALUES (41,39,10); INSERT INTO "tracks" VALUES (42,39,11); INSERT INTO "tracks" VALUES (43,38,1); INSERT INTO "tracks" VALUES (44,38,2); INSERT INTO "tracks" VALUES (45,38,3); INSERT INTO "tracks" VALUES (46,38,4); INSERT INTO "tracks" VALUES (47,38,5); INSERT INTO "tracks" VALUES (48,38,6); INSERT INTO "tracks" VALUES (49,38,7); INSERT INTO "tracks" VALUES (50,38,8); INSERT INTO "tracks" VALUES (51,38,9); INSERT INTO "tracks" VALUES (52,38,10); INSERT INTO "tracks" VALUES (53,37,1); INSERT INTO "tracks" VALUES (54,37,2); INSERT INTO "tracks" VALUES (55,37,3); INSERT INTO "tracks" VALUES (56,37,4); INSERT INTO "tracks" VALUES (57,37,5); INSERT INTO "tracks" VALUES (58,37,6); INSERT INTO "tracks" VALUES (59,37,7); INSERT INTO "tracks" VALUES (60,37,8); INSERT INTO "tracks" VALUES (61,37,9); INSERT INTO "tracks" VALUES (62,37,10); INSERT INTO "tracks" VALUES (63,37,11); INSERT INTO "tracks" VALUES (64,37,12); INSERT INTO "tracks" VALUES (65,37,13); INSERT INTO "tracks" VALUES (66,36,1); INSERT INTO "tracks" VALUES (67,36,2); INSERT INTO "tracks" VALUES (68,36,3); INSERT INTO "tracks" VALUES (69,36,4); INSERT INTO "tracks" VALUES (70,36,5); INSERT INTO "tracks" VALUES (71,36,6); INSERT INTO "tracks" VALUES (72,36,7); INSERT INTO "tracks" VALUES (73,36,8); INSERT INTO "tracks" VALUES (74,36,9); INSERT INTO "tracks" VALUES (75,36,10); INSERT INTO "tracks" VALUES (76,36,11); INSERT INTO "tracks" VALUES (77,36,12); INSERT INTO "tracks" VALUES (78,36,13); INSERT INTO "tracks" VALUES (79,36,14); INSERT INTO "tracks" VALUES (80,10,1); INSERT INTO "tracks" VALUES (81,10,2); INSERT INTO "tracks" VALUES (82,10,3); INSERT INTO "tracks" VALUES (83,10,4); INSERT INTO "tracks" VALUES (84,10,5); INSERT INTO "tracks" VALUES (85,10,6); INSERT INTO "tracks" VALUES (86,10,7); INSERT INTO "tracks" VALUES (87,10,8); INSERT INTO "tracks" VALUES (88,10,9); INSERT INTO "tracks" VALUES (89,10,10); INSERT INTO "tracks" VALUES (90,10,11); INSERT INTO "tracks" VALUES (91,10,12); INSERT INTO "tracks" VALUES (92,10,13); INSERT INTO "tracks" VALUES (93,10,14); INSERT INTO "tracks" VALUES (94,10,15); INSERT INTO "tracks" VALUES (95,10,16); INSERT INTO "tracks" VALUES (96,10,17); INSERT INTO "tracks" VALUES (97,10,18); INSERT INTO "tracks" VALUES (98,13,1); INSERT INTO "tracks" VALUES (99,13,2); INSERT INTO "tracks" VALUES (100,13,3); INSERT INTO "tracks" VALUES (101,13,4); INSERT INTO "tracks" VALUES (102,13,5); INSERT INTO "tracks" VALUES (103,13,6); INSERT INTO "tracks" VALUES (104,13,7); INSERT INTO "tracks" VALUES (105,13,8); INSERT INTO "tracks" VALUES (106,13,9); INSERT INTO "tracks" VALUES (107,13,10); INSERT INTO "tracks" VALUES (108,13,11); INSERT INTO "tracks" VALUES (109,13,12); INSERT INTO "tracks" VALUES (110,13,13); INSERT INTO "tracks" VALUES (111,13,14); INSERT INTO "tracks" VALUES (112,13,15); INSERT INTO "tracks" VALUES (113,14,1); INSERT INTO "tracks" VALUES (114,177,2); INSERT INTO "tracks" VALUES (115,178,3); INSERT INTO "tracks" VALUES (1
Re: [SQL] Sorting and then...
SELECT name FROM test ORDER BY id DESC LIMIT 10; Take care, Jason --- Wei Weng <[EMAIL PROTECTED]> wrote: > Suppose I have a table > > create table test > ( > id integer, > name text > ); > > And I want to get the names of the largest 10 "id"s. > How can I do that in > sql? > > Thanks! > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ ---(end of broadcast)--- TIP 3: 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
[SQL] Semantics of Typed Numeric Constants
Thomas Lockhart wrote in comp.databases.postgresql.hackers: > The parser does not know that your int4 constant "0" can be represented > as an int2. Try > > SELECT * FROM access_log WHERE method_num = int2 '0'; > > (note the type coersion on the constant; there are other ways of > specifying the same thing). Surely this is something that should be fixed. An int2 column ought to behave exactly like an int4 with a CHECK() constraint forcing the value to be in range. In object oriented terms: a smallint isA integer a integer isA bigint Likewise: a integer isA smallint if it falls in -32768..32767 a bigint isA integer if it falls in -2147483648..2147483647 Similar promotion rules should apply for all other numeric types. Any floating point value without a fractional part should be treated exactly like a big integer. The issues here are closely related to the 7.1 changes in INHERITS semantics. If any operator treats a smaller precision (more highly constrained) type in a materially different way than a compatible higher precision type, it is fundamentally broken for exactly the same reason that we expect a query on a super-class would be if if did not return all matching instances of every sub class. If a function is overloaded with multiple compatible scalar data types, the database should be free to call any matching implementation after performing an arbitrary number of *lossless* compatible type conversions. i.e. if you have f(smallint), f(integer), and f(double) the actual function called by f(0) should be undefined. The distinction between smallint '0', integer '0', and double '0' is meaningless and should be explicitly ignored. This is a little extreme, but I do not think it makes a lot of sense to maintain semantic differences between different representations of the same number. (Oracle certainly doesn't) Any comments? - Mark Butler ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: \i command
On Wed, 11 Apr 2001, Najm Hashmi wrote: > Hi All, > From pgsql, I try to insert data in table using the \i command. Nothing > takes place and after this command pgsql is hung... i cant use it any more. > The same command works with all other tables but this is only one I am having > problem with. > I have attached my file with message. Could someone help me out here what is > the reason for this behaviour. Works just fine for me (Pg7.1 RC3, Linux). Can you do manual inserts into the table? Can you insert just a few records using \i? Can you vacuum the table? -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Re: \i command
On Wed, 11 Apr 2001, Najm Hashmi wrote: > Joel Burton wrote: > > > On Wed, 11 Apr 2001, Najm Hashmi wrote: > > > > > From pgsql, I try to insert data in table using the \i command. Nothing > > > takes place and after this command pgsql is hung... i cant use it any more. > > > The same command works with all other tables but this is only one I am having > > > problem with. > > > I have attached my file with message. Could someone help me out here what is > > > the reason for this behaviour. > > > > Can you do manual inserts into the table? > > Can you insert just a few records using \i? > > Can you vacuum the table? > > Hi, No I can't even do manaul insert on that particular table. This is really > weird. Could this table's internals be corrupted? > One more thing, if I run the same file on another db it works just fine. I am > just wondering what is wrong with the structure. > Thank you for replying. Anyone else has an idea about it > Regards. The problem is almost certainly not w/your structure at all. Your tables has probably just become corrupted. Can you vacuum it? Can you dump it and recreate it? (You may also want to try running postmaster at a higher level of debug and checking the logs.) Good luck, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] a select statement that sometimes joins
Here's a situation I've run into more than once with SQL: I want to select all the rows in a table that match a criteria, where one of the criteria is possibly having a related entry in a second table. For my example, lets say I have table named 'messages' and another named 'message_attachments'. The former has a primary key of msg_id, the latter also contains msg_id, and has an attachment_id as it's primary key. This statement shows me all the messages that also have attachments: SELECT messages.msg_id, message_attachments.attachment_id FROM messages,message_attachments WHERE messages.msg_id = message_attachments.msg_id; But I want a statement that says: "Show me all the messages, and include information about an attachment if they have one" (Let's further assume that a message will have only one attachment). Is this possible? Anyone like to share an example? Much thanks. -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: \i command
Joel Burton wrote: > On Wed, 11 Apr 2001, Najm Hashmi wrote: > > > Hi All, > > From pgsql, I try to insert data in table using the \i command. Nothing > > takes place and after this command pgsql is hung... i cant use it any more. > > The same command works with all other tables but this is only one I am having > > problem with. > > I have attached my file with message. Could someone help me out here what is > > the reason for this behaviour. > > Works just fine for me (Pg7.1 RC3, Linux). > > Can you do manual inserts into the table? > Can you insert just a few records using \i? > Can you vacuum the table? > > -- > Joel Burton <[EMAIL PROTECTED]> > Director of Information Systems, Support Center of Washington > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Hi, No I can't even do manaul insert on that particular table. This is really weird. Could this table's internals be corrupted? One more thing, if I run the same file on another db it works just fine. I am just wondering what is wrong with the structure. Thank you for replying. Anyone else has an idea about it Regards. begin:vcard n:Hashmi;Najm x-mozilla-html:FALSE org:Mondo-Live.com;www.flipr.com adr:;; version:2.1 email;internet:[EMAIL PROTECTED] x-mozilla-cpt:;6144 fn:Najm Hashmi end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Re: select substr???
On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote: > Hi, > > I have postgres 6.x (where x is something). > > I have the following list of data > > data > > ABC* > ABC > ABC- ... > what I want to do is 'select distinct(data) [ignoring non alphanumeric > characters] order by data' somewhere I use the following, which might be adapted to do what you want. I am sure there are more elegant ways of doing this, though. create function ComparisonString(text) returns text as ' declare t text; r text; c char; ns bool; begin if $1 is null then return NULL; end if; t = lower(trim(both $1)); r = ; ns = false; for i in 1 .. char_length(t) loop c = substring(t from i for 1); if c = '' '' then if ns then r = r || '' ''; end if; ns = false; else if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 then r = r || c; ns = true; end if; end if; end loop; return trim(both r); end; ' language 'plpgsql' with (IsCachable); Albert. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] a select statement that sometimes joins
Mark Stosberg wrote: > > > >Here's a situation I've run into more than once with SQL: > >I want to select all the rows in a table that match a criteria, where one >of the criteria is possibly having a related entry in a second table. For >my example, lets say I have table named 'messages' and another named >'message_attachments'. The former has a primary key of msg_id, the latter >also contains msg_id, and has an attachment_id as it's primary key. > >This statement shows me all the messages that also have attachments: > >SELECT > messages.msg_id, > message_attachments.attachment_id > FROM messages,message_attachments > WHERE messages.msg_id = message_attachments.msg_id; > >But I want a statement that says: "Show me all the messages, and include >information about an attachment if they have one" SELECT m.msg_id, a.attachment_id FROM messages AS m LEFT OUTER JOIN message-attachments AS a ON m.msg_id = a.msg_id; This requires 7.1 for the LEFT OUTER JOIN. In 7.0.3 you could do it with a UNION. -- 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 "But seek ye first the kingdom of God, and his righteousness; and all these things shall be added unto you." Matthew 6:33 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] RE: Re: select substr???
Regular expressions make this much easier. The below could be shortened to: create function ComparisonString(text) returns text as ' declare t alias for $1; r text; c char; begin if t is null or t !~ ''[^a-zA-Z0-9]'' then return t; end if; r = ; for i in 1 .. char_length(t) loop c = substring(t from i for 1); if c ~ ''[a-zA-Z0-9]'' then r = r || c; end if; end loop; return r; end; ' language 'plpgsql' with (IsCachable); > -Original Message- > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, April 10, 2001 11:38 AM > To: [EMAIL PROTECTED] > Subject: Re: Re: select substr??? > > On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote: > > Hi, > > > > I have postgres 6.x (where x is something). > > > > I have the following list of data > > > > data > > > > ABC* > > ABC > > ABC- > ... > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > > characters] order by data' > > somewhere I use the following, which might be adapted to do what you > want. I am sure there are more elegant ways of doing this, though. > > create function ComparisonString(text) returns text as ' > declare > t text; > r text; > c char; > ns bool; > begin > if $1 is null then > return NULL; > end if; > t = lower(trim(both $1)); > r = ; > ns = false; > for i in 1 .. char_length(t) loop > c = substring(t from i for 1); > if c = '' '' then > if ns then > r = r || '' ''; > end if; > ns = false; > else > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > then > r = r || c; > ns = true; > end if; > end if; > end loop; > return trim(both r); > end; > ' language 'plpgsql' with (IsCachable); > > Albert. > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 3: 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
[SQL] RE: Re: select substr???
And if you have plperl installed, something like this is even easier: create function texttrim(text) returns text as '$_[0] =~ s/\\W//g; return $_[0]' language 'plperl'; (I just read the docs) :-) > -Original Message- > From: Jeff Eckermann [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, April 10, 2001 5:41 PM > To: [EMAIL PROTECTED] > Subject: RE: Re: select substr??? > > Regular expressions make this much easier. The below could be shortened > to: > > create function ComparisonString(text) returns text as ' > declare > t alias for $1; > r text; > c char; > begin > if t is null or t !~ ''[^a-zA-Z0-9]'' > then > return t; > end if; > r = ; > for i in 1 .. char_length(t) loop > c = substring(t from i for 1); > if c ~ ''[a-zA-Z0-9]'' > then > r = r || c; > end if; > end loop; > return r; > end; > ' language 'plpgsql' with (IsCachable); > > > > > -Original Message- > > From: Albert REINER [SMTP:[EMAIL PROTECTED]] > > Sent: Tuesday, April 10, 2001 11:38 AM > > To: [EMAIL PROTECTED] > > Subject:Re: Re: select substr??? > > > > On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote: > > > Hi, > > > > > > I have postgres 6.x (where x is something). > > > > > > I have the following list of data > > > > > > data > > > > > > ABC* > > > ABC > > > ABC- > > ... > > > what I want to do is 'select distinct(data) [ignoring non alphanumeric > > > characters] order by data' > > > > somewhere I use the following, which might be adapted to do what you > > want. I am sure there are more elegant ways of doing this, though. > > > > create function ComparisonString(text) returns text as ' > > declare > > t text; > > r text; > > c char; > > ns bool; > > begin > > if $1 is null then > > return NULL; > > end if; > > t = lower(trim(both $1)); > > r = ; > > ns = false; > > for i in 1 .. char_length(t) loop > > c = substring(t from i for 1); > > if c = '' '' then > > if ns then > > r = r || '' ''; > > end if; > > ns = false; > > else > > if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 > > then > > r = r || c; > > ns = true; > > end if; > > end if; > > end loop; > > return trim(both r); > > end; > > ' language 'plpgsql' with (IsCachable); > > > > Albert. > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > ---(end of broadcast)--- > TIP 3: 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 ---(end of broadcast)--- TIP 3: 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
[SQL] Re: [GENERAL] install pgSQL 7.1
No, I'm still at sea. Please help ! JACK - Original Message - From: "Justin Clift" <[EMAIL PROTECTED]> To: "datactrl" <[EMAIL PROTECTED]> Sent: Tuesday, April 10, 2001 3:18 AM Subject: Re: [GENERAL] install pgSQL 7.1 > Hi, > > Have people answered you regarding this? > > Regards and best wishes, > > Justin Clift > > > datactrl wrote: > > > > I down load pgSQL v 7.1rc1-1 rpm from > > ftp://ftp.postgresql.org/pub/dev/test-rpms/. When I install it, > > there is a dependency check error for libpg.so.2 & libreadline.so.4.1. > > My server is running RH 6.2. Do I have to upgrade to RH 7.x? > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Foreign Key between different databases
Hello, I've a table in a database and I want to create a column that is a foreign key to a table in an other database. When I try ...,foreign key (x) references otherdb.table(x) it says 'parse error at or near "."'. Is there an other way to create this kind of foreign key? Stan van de Mortel [[EMAIL PROTECTED]] ---(end of broadcast)--- TIP 3: 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
[HACKERS] Re: problem with copy command
On Tue, 10 Apr 2001, Jaruwan Laongmal wrote: > dear all, > I currently using postgresql v7.0.3 > when i import text file to table with command "copy tablename from > '/tmp/a.txt'; > and it shows > "copy: line 20, Cannot insert a duplicate key into unique index testpri_pk" > ,then it exits with doing nothing. > > I want to ignore this errors and continue copy the next record. How to do > that? > if I don't filter in '/tmp/a.txt' before using copy command. AFAIK, you can't ignore primary keys, so you can't cheat and get it in, even for a moment. And if COPY encounters bad data, it ends the transaction. (Both of these seem like the Right Thing to me, though perhaps there's an argument for COPY IGNORING ERRORS or something like that. Ick.) Either 1) filter /tmp/a.txt to remove duplicates or 2) drop your unique index, copy the data, get rid of duplicates, the add the index again or 2) Assuming your table you're importing to is CREATE TABLE names (lname text, fname text, primary key (lname, fname) ); Create another table w/o the primary key: CREATE TABLE import (lname text, fname text); copy to *this* table, then copy from this table to the names table, ignoring duplicates in the import: SELECT distinct fname, lname into names from import; -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
