[SQL] delete where not in another table

If I have two tables, T1 and T2, such that both have the same primary key of "user_id". What is the SQL I would use to delete all rows from T1 that are not in T2? This is one way to write the SQL but it is really inefficient: DELETE FROM T1 WHERE T1.user_id NOT IN (SELECT user_id FROM T2 WHERE

[SQL] subtract two dates to get the number of days

I want to subtract to dates to know the number of days different. Example: 01/02/2010 - 01/01/2010 = 1 day 08/01/2010 - 07/31/2010 = 1 day How do I do this? Thanks, Lance Campbell Software Architect/DBA/Project Manager Web Services at Public Affairs 217-333-0382

[SQL] sequence number in a result

Say I have the following SQL statement: SELECT a, b, c FROM t1 ORDER BY a; Is there a function or special system label I can use that would generate a sequence number in the returning result set? Example: SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a; Result: ab c

Re: [SQL] Create on insert a unique random number

lto:[EMAIL PROTECTED] On Behalf Of Vivek Khera Sent: Tuesday, March 18, 2008 12:57 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Create on insert a unique random number On Mar 18, 2008, at 1:03 PM, Campbell, Lance wrote: > The field n is not random but is sequential. Is there something I > s

Re: [SQL] Create on insert a unique random number

I created the following table: create table xyz ( n serial, abc character varying, constraint n_pkey primary key (n)); Each time I do an insert: insert into xyz(abc) values('adf6'); The field n is not random but is sequential. Is there something I should do to make the serial number random?

[SQL] Create on insert a unique random number

When inserting a record is there a way to have postgres create a random number for a field such that it is unique? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu

Re: [SQL] Like problem

Campbell, Lance Sent: Wednesday, February 13, 2008 10:59 AM To: Richard Huxton Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Richard, The first example you gave me does not work. Below is the test example I used (this example should NOT return 'matched'): SELECT '

Re: [SQL] Like problem

-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 13, 2008 10:53 AM To: Richard Huxton Cc: Campbell, Lance; pgsql-sql@postgresql.org Subject: Re: [SQL] Like problem Richard Huxton <[EMAIL PROTECTED]> writes: > Campbell, Lance wrote: >> WARNIN

Re: [SQL] Like problem

;abcgxyz' LIKE '%abc' || E'_' || 'x%'; ?column? -- matched (1 row) Do you have any thoughts on why none of these examples work with the 'E'? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University

[SQL] Like problem

8.2.5 I am having an issue with trying to use 'LIKE' so that I can match on a string with an underscore in it. What is the proper way to find the following string? Table t1 Column c1 String I want to match on 'abc_' SELECT c1 FROM t1 WHERE c1 LIKE '%abc\_%'; This gives me the follow

Re: [SQL] Random Unique Integer

EMAIL PROTECTED] Sent: Thursday, June 14, 2007 8:26 AM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Random Unique Integer On Thu, Jun 14, 2007 at 08:08:26AM -0500, Campbell, Lance wrote: > I have a table T1. It contains a field called F1. Is there a way for > me to set

[SQL] Random Unique Integer

I have a table T1. It contains a field called F1. Is there a way for me to set the table T1 up such that F1 can be populated with a random integer such that F1 is a unique integer? I would rather not create a stored procedure. Thanks, Lance Campbell Project Manager/Software Archit

Re: [SQL] subtract a day from the NOW function

oject Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu -Original Message- From: Osvaldo Kussama [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 4:41 PM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subj

Re: [SQL] subtract a day from the NOW function

el Glaesemann [mailto:[EMAIL PROTECTED] Sent: Thursday, June 07, 2007 12:27 PM To: Campbell, Lance Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] subtract a day from the NOW function > From: [EMAIL PROTECTED] [mailto:pgsql-sql- > [EMAIL PROTECTED] On Behalf Of Campbell, Lance > Sent: Thur

Re: [SQL] subtract a day from the NOW function

Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Campbell, Lance Sent: Thursday, June 07

Re: [SQL] subtract a day from the NOW function

webservices.uiuc.edu From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Campbell, Lance Sent: Thursday, June 07, 2007 11:09 AM To: pgsql-sql@postgresql.org Subject: [SQL] subtract a day from the NOW function Table Field "some_timestamp" is a timestamp. In a "

[SQL] subtract a day from the NOW function

Table Field "some_timestamp" is a timestamp. In a "WHERE" statement I need to compare a timestamp field in a table "some_timestamp" to now() - one day. Example: SELECT some_timestamp WHERE to_char(some_timestamp, 'MMDD') > (to_char(now(), 'MMDD') - 1 day); The statement "to