Re: [despammed] [SQL] converting varchar to integer
[EMAIL PROTECTED] <[EMAIL PROTECTED]> schrieb: > Hi, > >I have a varchar column, and I need to > >1) check the value in it is an integer >2) get the integer value (as integer) > >The problem is I can't suppose the're only correct >values - ie there can be something like 'xssdkjsd', >'230kdd' or even an empty string etc. test=# select * from foo; t | n --+--- bla | bla1 | 2| (3 rows) test=# update foo set n = substring(t , '[0-9]')::int; UPDATE 3 test=# select * from foo; t | n --+--- bla | bla1 | 1 2| 2 (3 rows) Regards, Andreas -- Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau- fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] converting varchar to integer
Using CASE to avoid '':
CREATE TABLE test (number TEXT);
INSERT INTO test VALUES('123');
INSERT INTO test VALUES('a123b');
INSERT INTO test VALUES('');
teste=> SELECT CASE number WHEN '' THEN NULL ELSE
to_number(number,'990') END AS
number FROM test;;
number
123
123
(3 lines)
__
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/
---(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: [despammed] [SQL] converting varchar to integer
At 05:30 AM 8/17/05, Kretschmer Andreas wrote: [EMAIL PROTECTED] <[EMAIL PROTECTED]> schrieb: > Hi, > >I have a varchar column, and I need to > >1) check the value in it is an integer >2) get the integer value (as integer) test=# update foo set n = substring(t , '[0-9]')::int; I think you meant: update foo set n = substring(t , '[0-9]+')::int; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Is it This Join Condition Do-Able?
Given three tables: a, b, c ; each consist of a 'keyfld' and a field called 'foo': tbl a tbl b tbl c - - - a.keyfldb.keyfld c.keyfld a.foo1 b.foo2 c.foo3 I want to always return all of tbl a; and I want to return b.foo2 and c.foo3 if they can be joined to based on keyfld.a; I know that it will involve a LEFT OUTER JOIN on table a, but have not seen any examples of joins like this on 3 or more tables. select a.keyfld, a.foo1, b.foo2, c.foo3 from a, b, c where a.keyfld = and a.keyfld = b.keyfld and a.keyfld = c.keyfld; Results could look like this: a.keyfld a.foo1 b.foo2 c.foo3 (null) (null) (null) (null) ---(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] Is it This Join Condition Do-Able?
How about: SELECT a.keyfld, a.foo1, b.foo2, c.foo3 FROM a LEFT JOIN b USING(keyfld) LEFT JOIN c USING(keyfld) Dmitri > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van Ingen > Sent: Wednesday, August 17, 2005 12:55 PM > To: [email protected] > Subject: [SQL] Is it This Join Condition Do-Able? > > > Given three tables: a, b, c ; each consist of a 'keyfld' and > a field called > 'foo': > tbl a tbl b tbl c >- - - >a.keyfldb.keyfld c.keyfld >a.foo1 b.foo2 c.foo3 > > I want to always return all of tbl a; and I want to return > b.foo2 and c.foo3 if they can be joined to based on keyfld.a; > I know that it will involve a LEFT OUTER JOIN on table a, but > have not seen any examples of joins like this on 3 or more tables. > > select a.keyfld, a.foo1, b.foo2, c.foo3 > from a, b, c > where a.keyfld = > and a.keyfld = b.keyfld > and a.keyfld = c.keyfld; > > Results could look like this: > a.keyfld a.foo1 b.foo2 c.foo3 > (null) > (null) > (null) (null) > > > > > ---(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 > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Is it This Join Condition Do-Able?
On Wed, Aug 17, 2005 at 12:54:50PM -0400, Lane Van Ingen wrote: > Given three tables: a, b, c ; each consist of a 'keyfld' and a field called > 'foo': > tbl a tbl b tbl c >- - - >a.keyfldb.keyfld c.keyfld >a.foo1 b.foo2 c.foo3 > > I want to always return all of tbl a; and I want to return b.foo2 and c.foo3 > if > they can be joined to based on keyfld.a; I know that it will involve a LEFT > OUTER > JOIN on table a, but have not seen any examples of joins like this on 3 or > more > tables. Does this example do what you want? CREATE TABLE a (keyfld integer, foo1 text); CREATE TABLE b (keyfld integer, foo2 text); CREATE TABLE c (keyfld integer, foo3 text); INSERT INTO a VALUES (1, 'a1'); INSERT INTO a VALUES (2, 'a2'); INSERT INTO a VALUES (3, 'a3'); INSERT INTO a VALUES (4, 'a4'); INSERT INTO b VALUES (1, 'b1'); INSERT INTO b VALUES (4, 'b4'); INSERT INTO c VALUES (2, 'c2'); INSERT INTO c VALUES (4, 'c4'); SELECT a.keyfld, a.foo1, b.foo2, c.foo3 FROM a LEFT OUTER JOIN b USING (keyfld) LEFT OUTER JOIN c USING (keyfld); keyfld | foo1 | foo2 | foo3 +--+--+-- 1 | a1 | b1 | 2 | a2 | | c2 3 | a3 | | 4 | a4 | b4 | c4 (4 rows) -- Michael Fuhr ---(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: [despammed] [SQL] converting varchar to integer
am 17.08.2005, um 12:46:01 -0400 mailte Frank Bax folgendes: > >>1) check the value in it is an integer > >>2) get the integer value (as integer) > >test=# update foo set n = substring(t , '[0-9]')::int; > > > I think you meant: > update foo set n = substring(t , '[0-9]+')::int; Yes, of corse. Thank you. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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] Is it This Join Condition Do-Able?
On Wed, Aug 17, 2005 at 12:54:50PM -0400, Lane Van Ingen wrote: > Given three tables: a, b, c ; each consist of a 'keyfld' and a field called > 'foo': > tbl a tbl b tbl c >- - - >a.keyfldb.keyfld c.keyfld >a.foo1 b.foo2 c.foo3 > > I want to always return all of tbl a; and I want to return b.foo2 and c.foo3 > if > they can be joined to based on keyfld.a; I know that it will involve a LEFT > OUTER > JOIN on table a, but have not seen any examples of joins like this on 3 or > more > tables. > > select a.keyfld, a.foo1, b.foo2, c.foo3 > from a, b, c > where a.keyfld = > and a.keyfld = b.keyfld > and a.keyfld = c.keyfld; > > Results could look like this: > a.keyfld a.foo1 b.foo2 c.foo3 > (null) > (null) > (null) (null) > Just use two left joins: select a.keyfld, a.foo1, b.foo2, c.foo3 from a left join b on a.keyfld = b.keyfld left join c on a.keyfld = c.keyfld where a.keyfld = ; HTH, Jeremy ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Locating ( FKs ) References to a Primary Key
Hi to all, Is there any means by which one can get all Foreign Keys (References) that 'point' to a certain Primary Key for a given table ? For instance, let's consider those three tables: (NOTE: table contents here are not deeply thought of...) // employees table create table emp (id serial primary key, first_name varchar not null, last_name varchar not null, .etc.); // employee address create table emp_address (emp_id integer references emp (id), city integer references city (id), primary key (emp_id, city), comments varchar not null); // employee categories () create table emp_categories (emp_id integer references emp (id), institution integer references institutions (id), unique (emp_id, institution), category integer references categories (id), primary key (emp_id, institution, category), description varchar not null); So, can we issue a query that gets all references to emp.id ? which should yield here: emp_address.emp_id and emp_categories.emp_id Thanks in advance, Roger Tannous. Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Is it This Join Condition Do-Able?
I don't see what the problem is. Did you mean to insert (3,'C3') into table c, rather than b? Dmitri > -Original Message- > From: Mischa Sandberg [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 17, 2005 3:31 PM > To: Dmitri Bichko > Cc: Lane Van Ingen; [email protected] > Subject: Re: [SQL] Is it This Join Condition Do-Able? > > > Quoting Dmitri Bichko <[EMAIL PROTECTED]>: > > > How about: > > > > SELECT a.keyfld, a.foo1, b.foo2, c.foo3 > > FROM a > > LEFT JOIN b USING(keyfld) > > LEFT JOIN c USING(keyfld) > > ((( See response at end ))) > > > > -Original Message- > [mailto:[EMAIL PROTECTED] > > > On Behalf Of Lane Van > > Ingen > > > Sent: Wednesday, August 17, 2005 12:55 PM > > > Subject: [SQL] Is it This Join Condition Do-Able? > > > > > > Given three tables: a, b, c ; each consist of a 'keyfld' and > > > a field called > > > 'foo': > > > tbl a tbl b tbl c > > >- - - > > >a.keyfldb.keyfld c.keyfld > > >a.foo1 b.foo2 c.foo3 > > > > > > I want to always return all of tbl a; and I want to return > > > b.foo2 and c.foo3 if they can be joined to based on keyfld.a; > > > I know that it will involve a LEFT OUTER JOIN on table a, but > > > have not seen any examples of joins like this on 3 or more tables. > ... > > Having a bit of uncertainty of how LEFT JOIN associates, I tried the > following test (psql -qe), with (to me) highly surprising results. > Anyone care to comment on the third row of output? > > select version(); > version > -- > - > PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 > (SuSE Linux) > > create temp table a(keyf int, val text); > create temp table b(keyf int, val text); > create temp table c(keyf int, val text); > insert into a values(1, 'A1'); > insert into a values(2, 'A2'); > insert into a values(3, 'A3'); > insert into a values(4, 'A4'); > insert into b values(1, 'B1'); > insert into b values(2, 'B2'); > insert into c values(2, 'C2'); > insert into b values(3, 'C3'); > select keyf, a.val as aval, > coalesce(b.val,'Bxx') as bval, > coalesce(c.val,'Cxx') as cval > from a left join b using(keyf) left join c using (keyf); > keyf aval bval cval > >1 A1 B1 Cxx >2 A2 B2 C2 >3 A3 C3 Cxx >4 A4 Bxx Cxx > > > > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Locating ( FKs ) References to a Primary Key
I have a couple of views I always add to 'information_schema' to help with these sorts of things. Here's the one for foreign keys: CREATE VIEW information_schema.foreign_key_tables AS SELECT n.nspname AS schema, cl.relname AS table_name, a.attname AS column_name, ct.conname AS key_name, nf.nspname AS foreign_schema, clf.relname AS foreign_table_name, af.attname AS foreign_column_name, pg_get_constraintdef(ct.oid) AS create_sql FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r') JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace) JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND ct.confrelid != 0 AND ct.conkey[1] = a.attnum) JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r') JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace) JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND af.attnum = ct.confkey[1]) ; GRANT SELECT ON information_schema.foreign_key_tables TO PUBLIC; Searching the 'foreign_*' fields for your schema/table/column will give you all the tables that reference it in a foreign key constraint. I also provide the SQL used to create the constraint, since the purpose of this is to drop and then recreate dependencies when reloading a single table. Hope that gets you started, Dmitri > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Roger Motorola > Sent: Wednesday, August 17, 2005 3:07 PM > To: [email protected] > Subject: [SQL] Locating ( FKs ) References to a Primary Key > > > Hi to all, > > Is there any means by which one can get all Foreign Keys > (References) that 'point' to a certain Primary Key for a given table ? > > For instance, let's consider those three tables: > > (NOTE: table contents here are not deeply thought of...) > > // employees table > create table emp > (id serial primary key, > first_name varchar not null, > last_name varchar not null, > .etc.); > > // employee address > create table emp_address > (emp_id integer references emp (id), > city integer references city (id), > primary key (emp_id, city), > comments varchar not null); > > // employee categories () > create table emp_categories > (emp_id integer references emp (id), > institution integer references institutions (id), > unique (emp_id, institution), > category integer references categories (id), > primary key (emp_id, institution, category), > description varchar not null); > > > So, can we issue a query that gets all references to emp.id ? > which should yield here: > emp_address.emp_id > and emp_categories.emp_id > > > Thanks in advance, > Roger Tannous. > > > > > Start your day with Yahoo! - make it your home page > http://www.yahoo.com/r/hs > > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Is it This Join Condition Do-Able?
Quoting Dmitri Bichko <[EMAIL PROTECTED]>: > How about: > > SELECT a.keyfld, a.foo1, b.foo2, c.foo3 > FROM a > LEFT JOIN b USING(keyfld) > LEFT JOIN c USING(keyfld) ((( See response at end ))) > > -Original Message- > > [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van > Ingen > > Sent: Wednesday, August 17, 2005 12:55 PM > > Subject: [SQL] Is it This Join Condition Do-Able? > > > > Given three tables: a, b, c ; each consist of a 'keyfld' and > > a field called > > 'foo': > > tbl a tbl b tbl c > >- - - > >a.keyfldb.keyfld c.keyfld > >a.foo1 b.foo2 c.foo3 > > > > I want to always return all of tbl a; and I want to return > > b.foo2 and c.foo3 if they can be joined to based on keyfld.a; > > I know that it will involve a LEFT OUTER JOIN on table a, but > > have not seen any examples of joins like this on 3 or more tables. ... Having a bit of uncertainty of how LEFT JOIN associates, I tried the following test (psql -qe), with (to me) highly surprising results. Anyone care to comment on the third row of output? select version(); version --- PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.3 (SuSE Linux) create temp table a(keyf int, val text); create temp table b(keyf int, val text); create temp table c(keyf int, val text); insert into a values(1, 'A1'); insert into a values(2, 'A2'); insert into a values(3, 'A3'); insert into a values(4, 'A4'); insert into b values(1, 'B1'); insert into b values(2, 'B2'); insert into c values(2, 'C2'); insert into b values(3, 'C3'); select keyf, a.val as aval, coalesce(b.val,'Bxx') as bval, coalesce(c.val,'Cxx') as cval from a left join b using(keyf) left join c using (keyf); keyf aval bval cval 1 A1 B1 Cxx 2 A2 B2 C2 3 A3 C3 Cxx 4 A4 Bxx Cxx ---(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] Is it This Join Condition Do-Able?
Mischa Sandberg <[EMAIL PROTECTED]> writes: > Anyone care to comment on the third row of output? I think you mistyped the last INSERT: > insert into c values(2, 'C2'); > insert into b values(3, 'C3'); I suppose you meant insert into c ... regards, tom lane ---(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] Is it This Join Condition Do-Able? Ooh, ouch, blush
The Subject says it all. (author beats a hasty retreat). Quoting Dmitri Bichko <[EMAIL PROTECTED]>: > I don't see what the problem is. > Did you mean to insert (3,'C3') into table c, rather than b? > > create temp table a(keyf int, val text); > > create temp table b(keyf int, val text); > > create temp table c(keyf int, val text); > > insert into a values(1, 'A1'); > > insert into a values(2, 'A2'); > > insert into a values(3, 'A3'); > > insert into a values(4, 'A4'); > > insert into b values(1, 'B1'); > > insert into b values(2, 'B2'); > > insert into c values(2, 'C2'); > > insert into b values(3, 'C3'); > > select keyf, a.val as aval, > > coalesce(b.val,'Bxx') as bval, > > coalesce(c.val,'Cxx') as cval > > from a left join b using(keyf) left join c using (keyf); > > keyf aval bval cval > > > >1 A1 B1 Cxx > >2 A2 B2 C2 > >3 A3 C3 Cxx > >4 A4 Bxx Cxx ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] How to secure PostgreSQL Data for distribute?
Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise.
Re: [SQL] How to secure PostgreSQL Data for distribute?
On 8/17/05, Premsun Choltanwanich <[EMAIL PROTECTED]> wrote: > > Dear All, > > I need to distribute my application that use PostgreSQL as database to > my customer. But I still have some questions in my mind on database > security. I understand that everybody who get my application database will > be have a full control permission on my database in case that PostgreSQL > already installed on their computer and they are an administrator on > PostgreSQL. So that mean data, structure and any ideas contain in database > will does not secure on this point. Is my understanding correct? > > What is the good way to make it all secure? Please advise. If your customer has the root password of the machine that is running postgresql, there's nothing you can do in order to limit the access to the database structure. My recommendation is: show them the code, make it free! ---(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] How to secure PostgreSQL Data for distribute?
On Thursday 18 Aug 2005 8:26 am, Premsun Choltanwanich wrote: > Dear All, > > I need to distribute my application that use PostgreSQL as > database to my customer. But I still have some questions in my mind > on database security. I understand that everybody who get my > application database will be have a full control permission on my > database in case that PostgreSQL already installed on their > computer and they are an administrator on PostgreSQL. So that mean > data, structure and any ideas contain in database will does not > secure on this point. Is my understanding correct? correct > > What is the good way to make it all secure? Please advise. no way - you could try to frighten him so much that he is afraid to touch anything. Or you could educate him so that he can creatively touch some parts of it and ease your burden of support and maintainence. Also enter into a proper license agreement with him so that he doesnt redistribute or hijack your ideas and your code. In the long run, if you follow this course, you will find that you will be able to serve your customers more effectively -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(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] How to secure PostgreSQL Data for distribute?
Premsun Choltanwanich wrote: Dear All, I need to distribute my application that use PostgreSQL as database to my customer. But I still have some questions in my mind on database security. I understand that everybody who get my application database will be have a full control permission on my database in case that PostgreSQL already installed on their computer and they are an administrator on PostgreSQL. So that mean data, structure and any ideas contain in database will does not secure on this point. Is my understanding correct? What is the good way to make it all secure? Please advise. If your customer can access the data, they can access the data. If they have control over the system, they can access the system. I guess you could build some sort of encryption into your client, but that seems pretty easy to circumvent. The short answer is that there is no good way to do this. If you are worried about this, the technology isn't going to save you. No technology will save you. Instead, I would highly suggest discussing the matter with an attourney and see if there is a legal remedy that might provide adequate protection. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Locating ( FKs ) References to a Primary Key
Dmitri, Thanks !! I got exactly what I wanted :) In fact, I used your query like this: SELECT cl.relname AS FK_table_name, a.attname AS FK_column_name, clf.relname AS PK_table_name, af.attname AS PK_column_name FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class cl ON (a.attrelid = cl.oid AND cl.relkind = 'r') JOIN pg_catalog.pg_namespace n ON (n.oid = cl.relnamespace) JOIN pg_catalog.pg_constraint ct ON (a.attrelid = ct.conrelid AND ct.confrelid != 0 AND ct.conkey[1] = a.attnum) JOIN pg_catalog.pg_class clf ON (ct.confrelid = clf.oid AND clf.relkind = 'r') JOIN pg_catalog.pg_namespace nf ON (nf.oid = clf.relnamespace) JOIN pg_catalog.pg_attribute af ON (af.attrelid = ct.confrelid AND af.attnum = ct.confkey[1]) WHERE n.nspname = nf.nspname AND n.nspname = 'public' AND clf.relname like 'sip_emp' AND af.attname = 'id'; Best Regards, Roger Tannous. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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
