Re: [SQL] Wicked screensaver
See the attached file for details ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Spam Re: read it immediately
Requested file. Potentially Dangerous Attachment Removed. The file "text.txt.exe" has been blocked. File quarantined as: "". A ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Confused by timezones
Sorry, I am trying to find my way in formatting timestamps for different
timezones and I am a little confused.
[ PostgreSQL 7.0.0 on alphaev6-dec-osf4.0f, compiled by cc ]
Let's imagine
CREATE TABLE tztest (id SERIAL, v TEXT, ts TIMESTAMP DEFAULT now());
How can I format a
SELECT to_char(ts,'DD/MM/ HH:MI:SS')
in order to have the accompanying timezone for the timestamp?
If I select the ISO format, I ofcourse have it ('2000-12-15
13:09:59+02')
but I cannot find a to_char element for it, either in offset or codes
(which I'd prefer).
Is this possible?
On a more general ground, I checked the 'Date/Time Data Types' section
of the user manual, but I don't manage to have the expected behaviour,
with either the PGTZ env variable or the SET TIMEZONE command. Here's an
example (my default is EET i.e. +02):
village=# select ts from tztest;
ts
2000-12-15 13:09:59+02
(1 row)
village=# set TimeZone TO PST;
SET VARIABLE
village=# select ts from tztest;
ts
2000-12-15 13:09:59+02
(1
row)
or maybe I just don't understand the whole picture...
P.S. Ofcourse I can use external functions, e.g. Date::Manip since I
code in Perl, but I'd prefer to leave this task to the database itself.
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
Re: [SQL] Confused by timezones
Karel Zak wrote: > Yes it's possible, but in freezed 7.1 *only*. It's 'TZ' and output is Thanks, on my experimental 7.1 works perfectly, another reason to switch as soon as possible. :-) > You must use same names (definitions) as are used in your OS > (an example on Linux at /usr/share/zoneinfo) In 7.1 works. Is it supposed to work also in 7.0? Because then it would be a configuration problem on my main system. Since I have a website with registered users, with associated timezone, I would like to show all timestamps based on the user's timezone, and the best way would be to set a session configuration. My only fear is that Apache::DBI (which reuses the same connection for different pages) could intermix such information. Any comment on this would be appreciated. Thanks -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Re: [SQL] Confused by timezones
Tom Lane wrote: > I'm guessing that's not a legal timezone name on your platform. > On my box I have to spell it "PST8PDT" ... note that the displayed > abbreviation is not the same as the name used to set the timezone. I used PST as an example, but it doesn't work with any other zone, including GMT (our localtime is EET). I suspect it's a configuration problem on our Digital machine (with Digital Unix 4.0F): zoneinfos are under /etc/zoneinfo. Do the functions use those files or it's just an OS call? Is configure involved at all in this area? Thanks again -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Re: [SQL] Bounds checking on an alias
[EMAIL PROTECTED] wrote: > SELECT DISTINCT tbl_restaurant.restaurant, > tbl_restaurant_location.postal_code, tbl_restaurant_location.latitude > AS latitude, tbl_restaurant_location.longitude AS longitude, distance > (49.24894, -122.90419, latitude, longitude) AS distance FROM > tbl_restaurant, tbl_restaurant_location, tbl_restaurant_cuisine_link > WHERE tbl_restaurant.restaurant_id = > tbl_restaurant_location.restaurant_id AND tbl_restaurant.restaurant_id > = tbl_restaurant_cuisine_link.restaurant_id AND > tbl_restaurant_cuisine_link.cuisine_id = 14 AND > tbl_restaurant.price_id = 1 AND tbl_restaurant_location.latitude IS > NOT NULL AND tbl_restaurant_location.latitude > 49.113804 AND > tbl_restaurant_location.latitude < 49.384075 AND > tbl_restaurant_location.longitude > -123.03932 AND > tbl_restaurant_location.longitude < -122.76906 AND distance <= 15.0 > ORDER BY distance; > ERROR: Attribute 'distance' not found It's not related to your function or query, but it's a generic behaviour: changemaster=# select id + 1 as next, val from t; next | val --+-- 2 | Test (1 row) changemaster=# select id + 1 as next, val from t where next > 1; ERROR: Attribute 'next' not found changemaster=# select id + 1 as next, val from t where id + 1 > 1; next | val --+-- 2 | Test (1 row) The named expression isn't available in the WHERE clause. You have to copy the same expression in it. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925
Re: [SQL] "'" in SQL INSERT statement
Markus Wagner wrote:
> I have some data that I wish to transfer into a database using perl/DBI.
If you use Perl DBI you should issue statements like
$dbh->do ('INSERT INTO table (field1, field2) VALUES (?,?)',
undef, $value1, $value2);
This binding takes care of quoting and escapes all characters that may
cause problems in the database backed (e.g. "that's" becomes "that''s"
etc.)
There is a DBI mailing list where you can find more info and support:
see <http://www.isc.org/dbi-lists.html>
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
[SQL] 'Include' function in SQL scripts
I was wondering if PostgreSQL supports some kind of #include between SQL script files, to split a long script in different files. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] MySql 'REPLACE'
I am working in porting the Slash software from MySql to PostgreSQL. I stepped in MySql's REPLACE command (a SQL command) that to my knowledge is not supported by PostgreSQL and it's not standard SQL. According to MySql's manual: "REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a unique index, the old record is deleted before the new record is inserted. See section 7.21 INSERT syntax." REPLACE INTO table (column, column...) VALUES (value, value...) Has anyone had any experience about how to simulate it under PostgreSQL? I am using Perl and I can move most of the thing into application anyway. Thanks. -- Alessio F. Bragadini[EMAIL PROTECTED] APL Financial Services http://village.albourne.com Nicosia, Cyprus phone: +357-2-755750 "It is more complicated than you think" -- The Eighth Networking Truth from RFC 1925 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] MySql 'REPLACE'
Thomas Swan wrote:
> You should be able to do this with two separate queries inside a
> transaction.
Yes, sorry, I was not clear enough. Unfortunately, the function I need
to write is a "generic" one that takes a number of fields/values and
generate a SQL instruction. So, there is no previous information about
the underlining table structure. What I did is a query to pg_class and
other pg_* tables to get a list of unique indexes and build the DELETEs
needed prior the INSERT.
> The only part I'm not clear on is whether to use an 'and' or an 'or'
> on the delete. Check and see if all values have to match or if
> partial matches, i.e. only one of the columns, are acceptable. If it
> does partial matching, then use the 'or', otherwise use the 'and'.
I went for AND.
You're welcome to check if my (Perl) code is completely wrong :-)
This is the MySql version:
sub sqlReplace {
my($self, $table, $data) = @_;
my($names, $values);
foreach (keys %$data) {
if (/^-/) {
$values .= "\n $data->{$_},";
s/^-//;
} else {
$values .= "\n " . $self->{_dbh}->quote($data->{$_}) . ',';
}
$names .= "$_,";
}
chop($names);
chop($values);
my $sql = "REPLACE INTO $table ($names) VALUES($values)\n";
$self->sqlConnect();
return $self->sqlDo($sql) or errorLog($sql);
}
and my PostgreSQL version:
sub sqlReplace {
my($self, $table, $data) = @_;
my($names, $values);
foreach (keys %$data) {
if (/^-/) {
$values .= "\n $data->{$_},";
s/^-//;
} else {
$values .= "\n " . $self->{_dbh}->quote($data->{$_}) . ',';
}
$names .= "$_,";
}
chop($names);
chop($values);
# We study the table structure - this code comes from psql -E
my $cols = $self->{_dbh}->selectcol_arrayref
(q{SELECT a.attname
FROM pg_class c, pg_attribute a
WHERE c.relname = ? AND a.attnum > 0 AND a.attrelid = c.oid
ORDER BY a.attnum}, undef, $table) || [];
unshift @$cols, ''; # To have values starting at index 1
my $all_uniq = $self->{_dbh}->selectcol_arrayref
(q{SELECT indkey
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = ? AND c.oid = i.indrelid AND
i.indexrelid = c2.oid AND indisunique IS TRUE},
undef, $table) || [];
$self->{_dbh}->{AutoCommit} = 0; # BEGIN TRANSACTION
foreach (@$all_uniq) {
my @acols = @$cols[split];
my $check = 1;
map {$check &&= defined $data->{$_}} @acols;
next unless $check;
my $sql = "DELETE FROM $table WHERE " .
join (' AND ',
map "$_ = " . $self->{_dbh}->quote($data->{$_}), @acols);
$self->{_dbh}->do ($sql);
}
my $sql = "INSERT INTO $table ($names) VALUES ($values)";
$self->{_dbh}->do($sql);
$self->{_dbh}->commit; # END TRANSACTION
# return $self->sqlDo($sql) or errorLog($sql);
}
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
---(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
