[SQL] Opposite of LOCK
Is there any possibility to unlock tables after they've been locked? The manual part of lock was fuzzy, so I ask from experts instead. MySQL uses lock and unlock. Antti.
Re: [SQL] Opposite of LOCK
Antti Linno wrote: > Is there any possibility to unlock tables after they've been locked? The > manual part of lock was fuzzy, so I ask from experts instead. MySQL > uses lock and unlock. Yes, COMMIT/ROLLBACK. The transactional concept implies that you hold each lock you accquired since transaction start until the transaction ends. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] SERIAL type does not generate new ID ?
On Sat, 10 Jun 2000, Jean-Marc Libs wrote:
Hi all,
> I don't really understand what happens, so I put context, then problem:
>
> 1/ Context
> --
> I have this table:
>
> CREATE TABLE film (
> film_id SERIAL PRIMARY KEY,
snip
> )
> ;
> SELECT setval ('film_film_id_seq', 6);
>
> 2/ Problem:
>
> I have this query in PHP:
> insert into film
>
>(film_country_id,film_country_id2,film_country_id3,film_country_id4,film_prod_year,film_ec_certif,film_ce_certif,film_eur_support,film_media_support,film_provisoire)
> values ('FR','','','','2000','f','f','f','f','f')
>
> And it gives the following error:
> ERROR: Cannot insert a duplicate key into a unique index
>
> 3/ Question:
>
> Shouldn't it automagically create an appropriate film_id ?
>
OK, I guess I figured out what happens: this serial word just creates
some sequence which acts as a counter of sort, which increments whenever
I insert stuff whithout specifying a value for film_id. Only, when
I insert with a specified film_id (as I did when I imported my
legacy values), the sequence doesn't budge, so it is now out of sync
with the actual count of items in the database.
I'll just SELECT setval to max(film_id) and everything should be OK.
Thanks to those who answered me,
Jean-Marc Libs
--
Jean-Marc Libs, ingénieur INTERNET/INTRANET
Actimage 1 rue St Leon F-67000 STRASBOURGhttp://www.actimage.net
Professionnel : [EMAIL PROTECTED]
Lieu de travail : [EMAIL PROTECTED]
[SQL] join if there, blank if not
Hi all, I just can't get my head round this one so I hope one of you can. I've got two tables, one holding phone calls, and another holding phone numbers. I want to do a select where if the number in the calls table exists in the numbers table, the description is included otherwise the description field is blank. Unfortunately, using the select I've tried, if the number is not on the one of the tables, it's ignored. Calls table cdate date ctime time cextn char(3) cnumber x(12) Numbers table nnumber x(12) ndesc x(30) Select I tried. select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc from calls c, numbers n where c.cnumber = n.nnumber; - Gary Stainburn. Work: http://www.ringways.co.uk mailto:[EMAIL PROTECTED] REVCOM: http://www.revcom.org.uk mailto:[EMAIL PROTECTED] - Murphy's Laws: (327) The minute before the engineer arrives, the printer starts working. -
Re: [SQL] date comparision ???
Thanks for your input. Unfortunately, it doesn't helped..
Here is the samples.
This query works fine:
SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime
'2000-06-02 06:11:01-07') = '2000' LIMIT 1;
datums_
--
2000-07-06 18:51:27+03
(1 row)
But this fails, obviously because the function doesnt know the value of
datums_:
SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime
'datums_') = '2000' LIMIT 1;
ERROR: Bad datetime external representation 'datums_'
datums_ is a timestamp field. How do i get it's value to use in date_part()
function??
date_part('year',datums_) and date_part('year',datetime datums_) doesnt
work also!
- Original Message -
From: Jie Liang
To: sandis
Sent: Friday, July 07, 2000 11:19 PM
Subject: Re: [SQL] date comparision
Hi,
it works for me:
urldb=# \d deleted
Table "deleted"
Attribute |Type | Modifier
-+-+--
url | text|
allocatedto | varchar(30) |
deleteddate | timestamp |
id | integer | not null
Index: deleted_pkey
so , I 've a field deleteddate(type is timestamp) in table deleted.
urldb=# select deleteddate from deleted limit 10 offset 23;
deleteddate
1999-12-17 15:24:19-08
1999-12-17 15:25:14-08
1999-12-17 15:25:29-08
1999-12-17 15:25:35-08
2000-01-19 18:00:51-08
1999-12-17 15:27:02-08
1999-12-17 15:27:59-08
2000-01-19 18:00:54-08
1999-12-17 15:28:16-08
1999-12-17 15:28:20-08
urldb=# select deleteddate from deleted where year(deleteddate)=2000
and rtrim(monthname(deleteddate),' ')='June'
and rtrim(dayname(deleteddate),' ')='Friday' limit 10;
deleteddate
2000-06-02 06:11:01-07
2000-06-02 06:16:08-07
2000-06-02 06:23:17-07
2000-06-02 06:23:17-07
2000-06-02 06:23:17-07
2000-06-02 06:45:30-07
2000-06-02 06:49:55-07
2000-06-02 07:08:27-07
2000-06-02 07:13:21-07
2000-06-02 07:13:21-07
(10 rows)
Good luck!!!
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
Re: [SQL] date comparision ???
On Mon, Jul 10, 2000 at 11:52:16AM +0300, sandis wrote:
> Thanks for your input. Unfortunately, it doesn't helped..
>
> Here is the samples.
>
> This query works fine:
> SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime
> '2000-06-02 06:11:01-07') = '2000' LIMIT 1;
> datums_
> --
> 2000-07-06 18:51:27+03
> (1 row)
>
> But this fails, obviously because the function doesnt know the value of
> datums_:
> SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime
> 'datums_') = '2000' LIMIT 1;
> ERROR: Bad datetime external representation 'datums_'
>
> datums_ is a timestamp field. How do i get it's value to use in date_part()
> function??
> date_part('year',datums_) and date_part('year',datetime datums_) doesnt
> work also!
Right, that's the syntax for a datetime literal, not a cast. How about:
SELECT datums_ FROM jaunumi WHERE flag = 'a' AND
date_part('year', datetime(datums_)) = 2000 LIMIT 1;
By the way, what version are you using? The functional cast is needed for
6.5.X, but not for 7.X.
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
Re: [SQL] Opposite of LOCK
Antti Linno <[EMAIL PROTECTED]> writes: > Is there any possibility to unlock tables after they've been locked? The > manual part of lock was fuzzy, so I ask from experts instead. MySQL > uses lock and unlock. Locks are released at transaction commit/abort. It has to be that way to preserve transaction semantics. I'll refrain from commenting about MySQL's transaction support... regards, tom lane
[SQL] SQL
Hi, I'd like an information about the history of SQL. The similarities and differences between SQL1 and SQL2 and SQL3. Thank you, Frederico Papatella Guerino Belo Horizonte-MG/ Brasil [EMAIL PROTECTED]
Re: [SQL] join if there, blank if not
Gary, What you want here is an outer join. The syntax would look something like this: select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc from calls c, numbers n where c.cnumber=n.nnumber union all select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc from numbers; (I haven't tried this, but it should work.) For more information on outer joins, see Bruce Momjian's book at http://www.postgresql.org/docs/aw_pgsql_book/ . Jacques Williams On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote: > Hi all, > > I just can't get my head round this one so I hope one of you can. > > I've got two tables, one holding phone calls, and another holding phone numbers. > > I want to do a select where if the number in the calls table exists > in the numbers table, the description is included otherwise the > description field is blank. > > Unfortunately, using the select I've tried, if the number is not on > the one of the tables, it's ignored. > > Calls table > cdate date > ctime time > cextn char(3) > cnumber x(12) > > Numbers table > > nnumber x(12) > ndesc x(30) > > Select I tried. > > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc > from calls c, numbers n where c.cnumber = n.nnumber; > > - > Gary Stainburn. > Work: http://www.ringways.co.uk mailto:[EMAIL PROTECTED] > REVCOM: http://www.revcom.org.uk mailto:[EMAIL PROTECTED] > - > Murphy's Laws: (327) The minute before the engineer arrives, the printer starts >working. > - >
[SQL] Bug in to_char()
Greetings,
Working with PostGreSQL 7.02, I found the following problem:
The AM/PM designator in the to_char function does not work proper for 13:00
and 12:00.
See the following:
test=> select to_char('3-12-2000 14:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 02:00 PM
(1 row)
This is correct.
test=> select to_char('3-12-2000 8:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 08:00 AM
(1 row)
This is correct.
test=> select to_char('3-12-2000 13:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 01:00 AM
(1 row)
NO! This is incorrect
test=> select to_char('3-12-2000 1:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 01:00 AM
(1 row)
This is correctly 1 am.
nicklebys=> select to_char('3-12-2000 12:00'::timestamp, 'Dy, HH12:MI PM');
to_char
---
Sun, 12:00 AM
(1 row)
NO! This is 12:00 pm. 0:00 or 24:00 is 12:00 am.
Any known work arounds or bug fixes planned?
Thanks,
Brian
--
+---+
| Brian Powell, President [EMAIL PROTECTED] |
| Filo Group www.filogroup.com|
| One Broadway, Suite 300AAIM: filogroupbrian |
| Denver, CO 80203 ICQ: 75037370|
| 303.733.3248 office 303.733.7122 fax |
+---+
Re: [SQL] importing in sql
On Mon, Jul 10, 2000 at 05:03:08PM -0500, Abdul Karim wrote:
> Hi, I am trying to import loads of data into postgres, I am having
> trouble with a field which is a sequence. I know how to import data
> using a delimiter with the copy command. But I need to know how I
> increase the sequence on each line of import.
>
> My Table has the following format.
>
> field1serial primary key,
> field2char (50),
> field3char(50),
> field4int,
> field5date
>
> The file has the following format
>
> field2|field3|field4|field5|
>
> Each field is separated by a | (pipe), How do I insert the sequence
> number before field2? I have like 30 files in this format and I need to
> import them in one table with each line having unique number. Has
> anyone come across similar situation? any help would be greatly
> appreciated.
>
you don't mention your operating system, but on Linux I'd do this
with awk:
awk '{print NR"|"$0}' data_file > data_file.out
You mention 30 files: to get them sequential, I'd do:
cat files1 file2 [...] | awk '{print NR"|"$0}' | split -C 1m - dataout
Use some glob pattern for the 'cat' command that puts the files in the
order you want (even if it's just listing them all individually)
Awk will then number them, and split will generate file with at most 1
Meg of lines in them. If you're not worried about doing an all in one
bulk load, skip the split.
After this is all loaded, be sure to set the sequence associated with
the serial filed you loading into:
SELECT setval('table_field_seq',max(field)) from table;
Ross
--
Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
[SQL] importing in sql
Hi, I am trying to import loads of data into postgres, I am having trouble with a field which is a sequence. I know how to import data using a delimiter with the copy command. But I need to know how I increase the sequence on each line of import. My Table has the following format. field1serial primary key, field2char (50), field3char(50), field4int, field5date The file has the following format field2|field3|field4|field5| Each field is separated by a | (pipe), How do I insert the sequence number before field2? I have like 30 files in this format and I need to import them in one table with each line having unique number. Has anyone come across similar situation? any help would be greatly appreciated. Please let me know if you need more info. Thanks in advance. -- Abdul Karim The Hub Communications Company Ltd. The Farmhouse Syon Park Middlesex TW8 8JF T: 020 8560 9222 (ext 243) F: 020 8560 9333 Email: mailto:[EMAIL PROTECTED] URL: http://www.thehub.co.uk
Re: [SQL] importing in sql
karim> The file has the following format karim> karim> field2|field3|field4|field5| karim> karim> Each field is separated by a | (pipe), How do I insert the sequence karim> number before field2? I have like 30 files in this format and I need to karim> import them in one table with each line having unique number. Has karim> anyone come across similar situation? any help would be greatly karim> appreciated. I'd create a new (temporary) table with only these 4 fields, and use COPY to load it. Then, insert the data into the real table from the temp one. Let postgres assign the serial numbers. Something like this: insert into (field2, field3, ...) select * from tmptable; Then you can drop your temp table. Jim Rowan DCSI [EMAIL PROTECTED]
RE: [SQL] join if there, blank if not
Jacques, The problem with using the union in this way is that you get NULLs for a number weather or not it has an associated record in calls. To do a pure outer join, it would be something like this: select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc from calls c, numbers n where c.cnumber=n.nnumber union all select null as cdate, null as cextn, null as cnumber, nnumber, ndesc from numbers WHERE nnumber NOT IN (SELECT nnumber FROM calls); --- What I have always had trouble with, though, is if you have multiple fields for a primary key. For example, if a customer master table also had ship-to locations as the key and you wanted to get all customers and any orders for that customer, in rough ANSI SQL it would be: SELECT c.cust_number, c.ship_to, o.item FROM cust c LEFT OUTER JOIN orders o ON c.cust_number = o.cust_number AND c.ship_to = o.ship_to then, in the union, it is not clear how to do it: SELECT c.cust_number, c.ship_to, o.item FROM cust c, orders o WHERE c.cust_number = o.cust_number AND c.ship_to = o.ship_to UNION SELECT cust_number, ship_to, NULL AS item FROM cust WHERE ??? which I never know what to do at ??? - WHERE c.cust_number NOT IN (SELECT cust_number FROM orders) is one choice, but this doesn't help if the ship to doesn't match. We can get wild and try - WHERE c.cust_number NOT IN (SELECT cust_number FROM orders WHERE ship_to = cust.ship_to) but if you go to two and three keys, what happens then? It seems like it should work if we continue. But how efficiently does this work? Has anyone examined this problem? Thanks, Henry -Original Message- From: Jacques Williams [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 12, 2000 9:41 AM To: Gary Stainburn Cc: [EMAIL PROTECTED] Subject: Re: [SQL] join if there, blank if not Gary, What you want here is an outer join. The syntax would look something like this: select c.cdate, c.ctime, c.cextn, c.cnumber, n.ndesc from calls c, numbers n where c.cnumber=n.nnumber union all select null as cdate, null as cextn, null as cnumber, n.nnumber, n.ndesc from numbers; (I haven't tried this, but it should work.) For more information on outer joins, see Bruce Momjian's book at http://www.postgresql.org/docs/aw_pgsql_book/ . Jacques Williams On Wed, Jul 12, 2000 at 04:34:04PM +0100, Gary Stainburn wrote: > Hi all, > > I just can't get my head round this one so I hope one of you can. > > I've got two tables, one holding phone calls, and another holding phone numbers. > > I want to do a select where if the number in the calls table exists > in the numbers table, the description is included otherwise the > description field is blank. > > Unfortunately, using the select I've tried, if the number is not on > the one of the tables, it's ignored. > > Calls table > cdate date > ctime time > cextn char(3) > cnumber x(12) > > Numbers table > > nnumber x(12) > ndesc x(30) > > Select I tried. > > select c.cdate, c.ctime, c.cextn, c.cnumber n.ndesc > from calls c, numbers n where c.cnumber = n.nnumber; > > - > Gary Stainburn. > Work: http://www.ringways.co.uk mailto:[EMAIL PROTECTED] > REVCOM: http://www.revcom.org.uk mailto:[EMAIL PROTECTED] > - > Murphy's Laws: (327) The minute before the engineer arrives, the printer starts working. > - >
RE: [SQL] Re: Matching and Scoring with multiple fields
Thanks to all of you that replied. I think Oliver's idea (which is pretty close to Stephan's) will probably do the trick I think. I will maybe look in the future to add the ability to allow users to weight fields with more priority. So customers could number the top five most important fields and then pick how they feel. I still worry about the results being skewed by extreme data in certain fields but I guess there's no way around that. Thanks again. Tim Johnson, -- http://www.theinkfactory.co.uk -Original Message- From: Oliver Mueschke [mailto:[EMAIL PROTECTED]] Sent: 10 July 2000 21:15 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Re: Matching and Scoring with multiple fields I'm not sure, but it seems you could calculate a column like: SELECT a,b,c,..., abs(-a)+abs(-b)+abs(-c)+... AS weight FROM t ORDER BY weight This way the closest matches would come first. On Mon, Jul 10, 2000 at 07:56:08PM +0100, Tim Johnson wrote: > I have a problem. Ok I'll rephrase that, a challenge. > > I have a table like this: > > a,b,c,d,e,f,g,h > --- > 2,5,3,4,4,5,2,2 > 1,1,1,1,1,1,1,1 > 5,5,5,5,5,5,5,5 > 3,3,2,4,5,1,1,3 > 1,1,5,5,5,5,1,4 > 1,5,5,5,4,4,2,1 > 5,5,5,5,1,1,1,1 > 1,1,1,1,5,5,5,5 > (rows 8) > > a to h are of type int. > > > I want to take input values which relate to this table say: > how do you feel about a: > how do you feel about b: > how do you feel about c: > ... > > and the answers will be 1 to 5. > > Now I want to take those answers for my incoming a to h and scan down the > table pulling out the closest matches from best to worst. There will be > about 2000 rows in the final table and I will LIMIT the rows in blocks of 10 > or so.
