Re: [SQL] date
On Fri, 2006-02-10 at 07:38 +0100, A. Kretschmer wrote:
am 09.02.2006, um 22:18:09 -0800 mailte superboy143 (sent by Nabble.com) folgendes:
>
> Hello,
>
> How can I write an sql query in postgresql so that I can insert a date into
> a table in the format DD-MM-, and when I select the date from the table
> I should get the date in the same format.
You can't define the format in the db, but you can define the
output-format with to_char(date, 'DD-MM-');
HTH, Andreas
You could also try using the data_part() function:
date_part('month',date)||-||date_part('day',date)||-||date_part('year',date)
But I think Andreas' suggestion is a bit more elegant.
Re: [SQL] query
On Fri, 2006-02-10 at 00:11 -0600, Bruno Wolff III wrote: On Tue, Feb 07, 2006 at 01:45:50 -0800, "superboy143 (sent by Nabble.com)" <[EMAIL PROTECTED]> wrote: > > I have a table in which I have a field with format like 100101. It has many values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a query such that I have to get only distinct values such that they contain only the substring I need. If I give 10 as substring, then it should return only 100101 or 100102 but not both i.e if the last two characters are not same it should not return both of them. It should return only values starting with 10 the middle two values should be distinct and the last two characters may be anything. You can probably use the Postgres extension DISTINCT ON to do what you want. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Try substring(column,1,4). That should return values of 1001, 1002, 1003 when grouped.
Re: [SQL] date
Ken Hill <[EMAIL PROTECTED]> writes: > On Fri, 2006-02-10 at 07:38 +0100, A. Kretschmer wrote: >> You can't define the format in the db, but you can define the >> output-format with to_char(date, 'DD-MM-'); > You could also try using the data_part() function: Setting the DateStyle parameter may also be of use, particularly on the input side. regards, tom lane ---(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
[SQL] Deleting rows in a file based on condition
I have the following perl script that reads a fixed-width file and replaces values in various sections of the file.
---
open (IN, '< in.txt');
open (OUT, '> out_test.txt');
while () {
chomp;
$first_section = substr $_, 0, 381; # extract the first section of the record
$facilityno = substr $_, 381, 10; # extract the facilityno field
$second_section = substr $_, 391, 1056; # extract the second section of the record
$requestor_section=" " x 500;
# $requestor_section = substr $_, 1446, 499; # extract requestor section of record
$third_section = substr $_, 1946, 4748; # extract third section of record
# print out the file with changed facilityno value ...
print OUT "$first_section$\0039007300$\$second_section$\$requestor_section$\$third_section\n";
}
close (IN);
close (OUT);
I want to place an "if...then" condition on the $facilityno value; such that if the $facilityno value = 00, delete the record (e.g., don't print out that row); rather skip that row and continue printing out the remaining rows.
Any advice is very much appreciated.
Re: [SQL] Deleting rows in a file based on condition
Oops. I posted this to the wrong support list. Sorry.
-Ken
On Fri, 2006-02-10 at 09:52 -0800, Ken Hill wrote:
I have the following perl script that reads a fixed-width file and replaces values in various sections of the file.
---
open (IN, '< in.txt');
open (OUT, '> out_test.txt');
while () {
chomp;
$first_section = substr $_, 0, 381; # extract the first section of the record
$facilityno = substr $_, 381, 10; # extract the facilityno field
$second_section = substr $_, 391, 1056; # extract the second section of the record
$requestor_section=" " x 500;
# $requestor_section = substr $_, 1446, 499; # extract requestor section of record
$third_section = substr $_, 1946, 4748; # extract third section of record
# print out the file with changed facilityno value ...
print OUT "$first_section$\0039007300$\$second_section$\$requestor_section$\$third_section\n";
}
close (IN);
close (OUT);
I want to place an "if...then" condition on the $facilityno value; such that if the $facilityno value = 00, delete the record (e.g., don't print out that row); rather skip that row and continue printing out the remaining rows.
Any advice is very much appreciated.
Re: [SQL] date
superboy143 (sent by Nabble.com) wrote:
Hello,
How can I write an sql query in postgresql so that I can insert a date into
a table in the format DD-MM-, and when I select the date from the table
I should get the date in the same format.
See postgresql.conf documentation
(http://www.postgresql.org/docs/8.1/interactive/runtime-config-client.html#RUNTIME-CONFIG-CLIENT-FORMAT):
17. Server Configuration
17.10.2. Locale and Formatting
DateStyle = 'German, dmy'
- output format specification: German
- input/output specification for year/month/day ordering: DMY
postgres=# SHOW datestyle;
DateStyle
-
German, DMY
(1 row)
postgres=# SELECT CURRENT_DATE;
date
10.02.2006
(1 row)
postgres=# SELECT to_char('09/03/2005'::date,'dd/mon/');
to_char
-
09/mar/2005
(1 row)
[]s
Osvaldo
___
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora!
http://br.acesso.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
[SQL] Very slow updates when using IN syntax subselect
If I do:
select event_id from event join token using
(token_number) where token_status=50 and
reconciled=false limit 1;
Then:
update event set reconciled=true where event_id={XXX};
It returns in about a second, or less. But If I do the same thing with
the IN syntax:
update event set reconciled=true where event_id in
(select event_id from event join token using (token_number)
where token_status=50 and reconciled=false LIMIT 1);
On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU
forever. Any clues what might be going on? Help would be much
appreciated. I'm not seeing this on all my DB's... just the important ones.
PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-20)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] unique constraint instead of primary key? what
gry@ll.mit.edu (george young) writes:
> On 9 Feb 2006 08:22:59 -0800
> "BigSmoke" <[EMAIL PROTECTED]> threw this fish to the penguins:
>
>> If my tables have one or more UNIQUE constraints/indices, I still add a
>> "id SERIAL PRIMARY KEY" field to most of my tables. This makes
>> referencing easier and faster. It also improves consistency, which is
>> never a bad thing in my opinion.
>
> In this schema overhaul I'm trying to *eliminate* arbitrary "id" columns like
> this. They may sometimes improve performance, but certainly obscure the
> meaning of the data -- naive [read-only] users accessing through excel are
> confused by this sort of thing.
Actually, that's not the only people that get confused.
I'm not usually considered overly naive, and there is a database that
I have been using for reverse-engineering purposes of late which is
filled with both "id" and "v_id" columns which *very* much obscure the
meaning of the data.
I'm not adverse to having some such thing; I *am* adverse to giving
them such generic names.
It would be nice to be certain that the "would-be primary key
characteristics" that you'd probably rather use are sufficiently
permanent to be satisfactory. Unfortunately, they often aren't, or
aren't acceptably usable.
--
output = reverse("gro.gultn" "@" "enworbbc")
http://cbbrowne.com/info/oses.html
"To conquer the enemy without resorting to war is the most desirable.
The highest form of generalship is to conquer the enemy by strategy."
-- Sun Tzu, "The Art of War"
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Very slow updates when using IN syntax subselect
Bryce Nesbitt <[EMAIL PROTECTED]> writes: > update event set reconciled=true where event_id in > (select event_id from event join token using (token_number) > where token_status=50 and reconciled=false LIMIT 1); > On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU > forever. What does EXPLAIN show for this and for the base query? > PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 This may be your problem right here. You are urgently in need of an update in any case --- there are a lot of nasty bugs fixed since 7.4.1: http://developer.postgresql.org/docs/postgres/release.html regards, tom lane ---(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] Very slow updates when using IN syntax subselect
Tom Lane wrote:
> Bryce Nesbitt <[EMAIL PROTECTED]> writes:
>
>> update event set reconciled=true where event_id in
>> (select event_id from event join token using (token_number)
>> where token_status=50 and reconciled=false LIMIT 1);
>>
>> On a 4 CPU machine, 2 CPU's peg at 100%, and the request just eats CPU
>> forever.
>>
> What does EXPLAIN show for this and for the base query?
QUERY PLAN
---
Nested Loop (cost=0.00..3.04 rows=1 width=8)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
-> Index Scan using token_token_number_key on token
(cost=0.00..3.03 rows=1 width=11)
Index Cond: (("outer".token_number)::text =
(token.token_number)::text)
Filter: (token_status = 50)
(6 rows)
QUERY PLAN
-
Nested Loop IN Join (cost=0.00..3.06 rows=1 width=616)
Join Filter: ("outer".event_id = "inner".event_id)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=616)
-> Nested Loop (cost=0.00..3.04 rows=1 width=8)
-> Seq Scan on event (cost=0.00..0.00 rows=1 width=408)
Filter: (reconciled = false)
-> Index Scan using token_token_number_key on token
(cost=0.00..3.03 rows=1 width=11)
Index Cond: (("outer".token_number)::text =
(token.token_number)::text)
Filter: (token_status = 50)
(9 rows)
select count(*) from event;
---
116226
stage=# select count(*) from token;
---
8948
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
