Re: [SQL] Using SETOF in plpgsql function
On Wed, 23 Aug 2000, Jan Wieck wrote: > Before you ask: The mentioned redesign will NOT be done for > 7.1, and I'm not sure if we will be able to do it for 7.2 > yet. I hope that 7.2 :-), my query/plan cache is still outside current interest and if core developers not will work on something like query path redesign, the query/plan cache will still out.. :-( Or already test query/plan cache anyone? IMHO it is good merge-able to current source too. Karel PS. sorry of my small sigh for this :-)
Re: [SQL] 8K Limit, and Replication
On Tue, 5 Sep 2000, Poet/Joshua Drake wrote: > Hello, > > I have heard of this infamous 8k limit. I have a couple of questions. > 1. Does this mean that if I have a large object that I am inserting into a > table, like an image it has to be 8k or less? In current version is possible range 8--32Kb for block size, default is 8Kb. You can change it in sourses in the file include/config.h, other solution is use the large object interface (LO). > 2. When will this be fixed? It's already fixed in the current devel tree (see CVS) and it will available in 7.1 (1 Oct?). > 3. Does anyone know the status of the replication capabilities in PGSQL? Good question, bad answer ... (IMHO) not exist some standard replication for PG. Karel
Re: [SQL] C functions and int8?
On Thu, 21 Sep 2000, Forest Wilkinson wrote: > I have written a few Postgres extension functions in C, and want to modify > some of them to return an int8. However, I don't see any int8 definition > in postgres.h. (I have the 7.0.2 RPMs installed.) How should I > accomplish this? in the source tree: src/include/c.h typedef signed char int8; /* == 8 bits */ ... but I not sure if this file is included in the RPM package. Karel
Re: [SQL] Data Type precision
/* * Karel Zak * [EMAIL PROTECTED] * http://home.zf.jcu.cz/~zakkr/ * C, PostgreSQL, PHP, WWW, http://docs.linux.cz * */ On Tue, 26 Sep 2000, Jerome Raupach wrote: > CREATE TABLE TR (f1 FLOAT4, f2 INT4, f3 INT4) ; > > UPDATE TR SET f1=f2/f3::FLOAT4 ; > > f1 -> xx,xx - but I want f1 -> xx,xx. > (6,6) (6,2) See formatting functions in docs: test=# SELECT TO_CHAR( 123456.123456, '99.99')::float8; ?column? --- 123456.12 (1 row) Note, really float4 with 8 places (6,2)? test=# select '123456.12'::float4; ?column? -- 123456 (1 row) test=# select '1234.12'::float4; ?column? -- 1234.12 (1 row) test=# select '123456.12'::float8; ?column? --- 123456.12 (1 row) Karel
Re: Re(2): [SQL] Large Object dump ?
On Wed, 1 Nov 2000, pgsql-sql wrote: > You can try the script I made for exporting all my Pg database. > Ideas were borrowed from pg_dumplo-0.0.5. > Make it sure that you have "Perl5 extension for PostgreSQL - Pg.pm" > installed. Why you re-write pg_dumplo to perl and not use directly it? Perl love? :-) Karel
Re: [SQL] select ... for update
> > Hi, > > How can I use select ... for update to update limit to update what I > select?? First thing - the SELECT FOR UPDATE is not merge of SELECT and UPDATE but transaction option. The PostgreSQL use row-locking for UPDATEed rows. Standard SELECT ignore this lock, but SELECT FOR UPDATE wait until *other* transaction with UPDATE will commited. > somewhat like: > select url,id from mytable for update order by priority,id limit 5; ^^^ see the SELECT's syntax, ORDER BY must be before FOR UPDATE. > I want update the id in above return like: > update mytable set allocatedto='whatever' where id in above return set. Can't you in UPDATE's WHERE define some 'id' as in above SELECT? An example (via subselect): UPDATE mytable SET allocatedto='whatever' WHERE id IN ( SELECT id FROM mytable ORDER BY priority,id LIMIT 5 ); But it not will too much fast... better is define relevant 'id' inside UPDATE's WHERE without sub-select, but if you need define it via ORDER+LIMIT it's impossible. Karel
Re: [SQL] to_char() causes backend to close connection
On Thu, 14 Dec 2000, Kovacs Zoltan Sandor wrote: > Hi, this query gives different strange results: > > select to_char(now()::abstime,'YYMMDDHH24MI'); the result: ERROR: to_char/to_number(): not unique decimal poit is right, because we have two to_char(): to_char(int, text) to_char(timestamp, text) and for 'now()::abstime' function manager select "number" version instead timestamp. And in "number" version is D as decimal point. > I get e.g. a "backend closed the channel unexpectedly..." error with > successful or failed resetting attempt (indeterministic) Yes this is a bug to_char() ... I fix it today. > My machine works with PostgreSQL 7.0.2 + Red Hat 6.1. > Is this fixed in 7.0.3? A problem (IMHO it's not problem) with 'now()::abstime' is in 7.1 too. But why you not use directly now()? Thanks! Karel
Re: [SQL] to_timestamp, problem
On Fri, 15 Dec 2000, Marcin Mazurek wrote:
> Hi,
> Can anyone explain to me why this doesn't work. Seems to be some stupid (my)
> mistake:
>
> mtldb=# SELECT to_timestamp('05121445482000', 'MMDDHHMISS');
> to_timestamp
>
> 2000-05-12 14:45:48+02
> (1 row)
>
> mtldb=# SELECT to_timestamp('2512144548', 'MMDDHHMISS');
> to_timestamp
> --
> invalid
> (1 row)
>
> pg 7.0.3, linux 2.2
You are right. It is already know bug (feature:-) in 7.0.x, in this
version, to_timestamp() expect that year not must be exactly
4-digits but can be greater. A solution is use some separator like
'-MMDDHHMISS' or use as last in format template (as in your
first example).
In 7.1 is better analyse that fix it:
test=# SELECT version();
version
--
PostgreSQL 7.1devel on i686-pc-linux-gnu, compiled by GCC 2.95.2
(1 row)
test=# SELECT to_timestamp('2512144548', 'MMDDHHMISS');
to_timestamp
2000-05-12 14:45:48+02
(1 row)
Karel
Re: [SQL] Confused by timezones
On Fri, 15 Dec 2000, Alessio Bragadini wrote:
> 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?
Yes it's possible, but in freezed 7.1 *only*. It's 'TZ' and output is
abbreviation of timezone, +02 (digit version) is not supported.
test=# SELECT to_char(now(), 'DD/MM/ HH:MI:SS TZ');
to_char
-
15/12/2000 01:29:14 CET
(1 row)
> 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...
You must use same names (definitions) as are used in your OS
(an example on Linux at /usr/share/zoneinfo)
test=# set TimeZone TO 'Japan';
SET VARIABLE
test=# select now();
now
2000-12-15 21:40:52+09
(1 row)
test=# set TimeZone TO 'EST';
SET VARIABLE
test=# select now();
now
2000-12-15 07:41:18-05
(1 row)
test=# set TimeZone TO 'GMT';
SET VARIABLE
test=# select now();
now
2000-12-15 12:41:29+00
(1 row)
Karel
Re: [SQL] substring ..
On Tue, 19 Dec 2000, Jeff MacDonald wrote: > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; And why not to_char()? Karel
Re: [SQL] How to trim values?
On Thu, 28 Dec 2000, Oliver Elphick wrote: > [EMAIL PROTECTED] wrote: > >Hi, > > > >I'm trying to figure out how to take a value like 3.68009074974387 > >(that is calculated from values in my database) and have PostgreSQL > >hand me 3.68. Any suggestions would be appreciated. > > cast it to numeric(x,2) > > (where x is the total number of digits, and 2 is two decimal places). > > template1=# select 3.68009074974387::numeric(3,2); > ?column? > -- > 3.68 > (1 row) > > or use round(value,2) > > > template1=# select round(3.68009074974387, 2); > round > --- > 3.68 > (1 row) or test=# select to_char(3.68009074974387, '99.99'); to_char - 3.68 (1 row) Karel
Re: [SQL] Numeric and money
> Date: Wed, 3 Jan 2001 11:11:36 -0700 > From: Michael Davis <[EMAIL PROTECTED]> !> To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, !> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, !> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]>, !> "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> > Subject: [SQL] Numeric and money Man, where is limit between spam and question to mailing list?! Karel
Re: [SQL] Select 'Sunday' in month ??
On Wed, 17 Jan 2001, [EMAIL PROTECTED] wrote:
> E.g.
>
> create table mytable (created datetime);
> insert into mytable values ('01-01-2001');
> ...
> insert into mytable values ('01-31-2001');
>
> select created from mytable where date_part('dow', created) = 7 and
>date_part('month', created) = 1;
>
This will faster (one function call instead two):
. where to_char(created, 'D/MM') = '7/01';
Karel
Re: [SQL] Problem with Day of Week
On Mon, 29 Jan 2001, Keith Perry wrote:
> Greetings,
>
> I notice some talk about date problems and interestingly enough planning
> out an application in which I will need to be able to manipulate dates.
> I notice however that there seems to be a discrepancy with the day or
> week in 7.0.3
>
> ---
>
> pmhcc=# select date_part('dow','now'::timestamp);
> date_part
> ---
> 1
> (1 row)
>
> pmhcc=# select to_char('now'::timestamp,'D');
> to_char
> -
> 2
> (1 row)
>
See:
test=# select date_part('dow','2001-02-11'::timestamp);
date_part
---
0
test=# select to_char('2001-02-11'::timestamp, 'D');
to_char
-
1
date_part is based on zero - use range 0-6
to_char is based on one - use range 1-7
Karel
Re: [SQL] Problem with Day of Week
On Mon, 5 Feb 2001, Ross J. Reedstrom wrote:
> On Mon, Feb 05, 2001 at 05:15:47PM +0100, Karel Zak wrote:
> >
> > test=# select date_part('dow','2001-02-11'::timestamp);
> > date_part
> > ---
> > 0
> >
> > test=# select to_char('2001-02-11'::timestamp, 'D');
> > to_char
> > -
> > 1
> >
> >
> > date_part is based on zero - use range 0-6
> > to_char is based on one - use range 1-7
> >
>
> My understanding is that one is ISO, the other is for Oracle
> compatability, is that not right Karel?
You are right, Larry Ellison use 1-7 (he must, if the PostgreSQL to_char()
is 100% comapatible :-)
I don't know if 0-6 is like ISO (Thomas?), but surely it's like POSIX.
Karel
Re: [SQL] Problem with Day of Week
On Mon, 5 Feb 2001 [EMAIL PROTECTED] wrote: > Ok, so there is actually two standards then. Is this documented > anywhere? Is this is something that is going to change? I don't want > to write and app and have things "break" during and upgrade :) I mean you can be caseful. Not changes planned here. date_part() is not documented to much in detail, but formatting functions are described good. Karel
Re: [SQL] type casting: varchar to date
On Tue, 6 Feb 2001, J.Fernando Moyano wrote:
>
> Is there some way to do something like this ?? :
Yes,
select to_timestamp('hello 02-06-2001', '"hello "MM-DD-');
Karel
Re: [SQL] timestamp- milliseconds since epoch output
On Tue, 6 Feb 2001 [EMAIL PROTECTED] wrote: > Hi, > > I'm using a timestamp field called date_created. Whenever I select it > I get: > > select date_created from tbl_user; > date_created > > 2001-02-05 17:23:26-08 > 2001-02-05 17:45:39-08 > 2001-02-03 03:58:53-08 > (3 rows) > > I've tried using variations of to_char and to_timestamp but can't seem > to get the timestamp as a value of milliseconds since the Epoch (Jan to_char() and to_timestamp() are milliseconds ignorant routines. It's in my TODO list for next release -- now we in feature freeze state. Karel
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
On Mon, Mar 05, 2001 at 08:39:05PM -0800, Josh Berkus wrote:
> Bruce, Tom, et. al.,
>
> I can't find any documentation for what masks to use with the function
> TO_CHAR(INTERVAL, mask). Is there a TO_CHAR(INTERVAL)? If so, what
> masks are there? If not, how would you suggest I convert an interval
The 'interval' version of to_char() isn't implemented -- may be in 7.2
(it's high in my TODO list:-)
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: MySQLs Describe emulator!
On Tue, Mar 06, 2001 at 09:14:54AM -0500, Boulat Khakimov wrote: > Tom Lane wrote: > > > > Boulat Khakimov <[EMAIL PROTECTED]> writes: > > > Here is a nifty query I came up with > > > that provides a detailed information on any row of any table. > > > Something that is build into mySQL (DESC tablename fieldname) > > > but not into PG. > > > > Er, what's wrong with psql's "\d table" ? > > 2) as a programmer I need to be able to find out as much info as > possible about any given field >which is what "describe" for in mySQL. As a programmer you can see psql source and directly found how SQL query execute this tool. The PostgreSQL needn't non-standard statements like MySQL's SHOW, DESC -- the postgreSQL has system catalogs. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
On Tue, Mar 06, 2001 at 09:22:25AM -0800, Josh Berkus wrote: > Karel, > > > The 'interval' version of to_char() isn't implemented -- may be in 7.2 > > (it's high in my TODO list:-) > > Grazie. (One of the things I love about PostgreSQL is being able to > get definitive answers on functionality -- try asking Microsoft an "is > this implemented?" question!) :-) > Given the lack of to_char(interval), I'd like to write a PLPGSQL > function to fill the gap in the meantime. If you can answer a few > questions about how interval values work, it would be immensely helpful: > > 1. Hours, minutes, and seconds are displayed as "00:00:00". Days are > displayed as "0 00:00:00". How are weeks, months, and years displayed? > > 2. If months have their own placeholder in the Interval data type, how > many days make up a month? Is it a fixed value, or does it depend on > the calendar? A displayed format is external string alternate of a internal number based form. A interval/timestamp string that you use in SQL is parsed to 'tm' struct (see man ctime) where has each item like hours, minutes own field. For some date/time operation is used Julian date (..etc) -- internaly PG not works with strings for date/time. I mean is too much difficult write a 'interval' to_char() version in some procedural language without access to real (internal) form of 'interval'. Big date/time guru is Thomas (CC:), comments? Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] sql functions and triggers
On Wed, Mar 07, 2001 at 03:03:59PM +0100, Andrzej Roszkowski wrote:
> Hello!
>
> It is possible to pass ie. insert fields and values to sql function in
> trigger? I want to define a trigger on insert (select delete etc.) and log
> some values in different table(s).
Sure, see docs about SPI interface or you can try use RULEs for this.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] No Documentation for to_char(INTERVAL, mask)
On Wed, Mar 07, 2001 at 08:38:25AM -0800, Josh Berkus wrote: > Thomas, Karel, > > > I agree with Karel's point that it may be a pain to use a procedural > > language to manipulate a "stringy" interval value. If you use a C > > function instead, you can get access to the internal manipulation > > functions already present, as well as access to system functions to > > manipulate a tm structure. > > Ah, but this leaves out two important considerations of my particular > problem: > > 1. The interval I want to manipulate is limited to a relative handful of > possibilities (it's a billing interval): 1 week, 2 weeks, 2x/Month, 1 > month, 2 months, 3 months. > > 2. I don't do C. And I don't have the budget to hire somebody to di it > in C. > > If this was a bigger budget project, I'd simply take Karel's notes and > hire a programmer to create the to_char(Interval) function and thus > contribute to PostgreSQL ... but this project is over budget and behind > schedule already. Now I'm not writing to_char(interval), because current source (7.1) is freeze for new features and I'm waiting for 7.2 devel. cycle and I'm spending time with other things (PL/Python, the Mape project etc..). If it's *really important* for you I can write it next week(s), ... of course, my time is limited :-) May be try found some other solution. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] hierarchical order equivalent
On Sun, Mar 18, 2001 at 06:56:17PM -0500, Grant Furick wrote: > Is there an equivalent way to do this Oracle query in Postgres? > > Select category_id, parent_category_id, category_name > FROM Category > START WITH category_id = 6 > CONNECT BY PRIOR category_id = parent_category_id > Order by category_name > The PostgreSQL hasn't implemented Oracle's "walk tree" feature (yet??? -- speculate about it anyone?). If I good remember some discussion about hierarchy system in tables was before now, see archives at postgresql.org. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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
Re: [SQL] SQL Date help
On Mon, Mar 26, 2001 at 12:24:07PM +0400, waheed_rahuman wrote:
>
>
> Hi,
>
> I am a newbie to SQL and postgresql too.
> please guide me in the following...
Any problem with documentation?
> 1.How i can create date while i am inserting into the table ,in which the
> field is date data type.(date should not be a current date and its should be
> created
> as in java date(int,int,int)
INSERT INTO xxx VALUES (now());
INSERT INTO xxx VALUES ('2000-01-01');
> 2. How i can compare the date field.
via standard operators, en example '<' '>' '=' 'between' ..etc.
> For example ,
> how i can get the all the records between to two dates
SELECT WHERE col BETWEEN '2000-01-01' AND '2001-01-01';
Please(!), see
http://www.postgresql.org/users-lounge/docs/7.0/user/datatype.htm
http://www.postgresql.org/users-lounge/docs/7.0/user/operators.htm
http://www.postgresql.org/users-lounge/docs/7.0/user/operators2373.htm
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] function to format floats as money? (removing space padding)
On Thu, Apr 19, 2001 at 02:53:38PM -0500, Mark Stosberg wrote: > > Now that I've figured out that numeric is good for storing money, and > that I can format with like this: > > to_char(price, '9,999,999.99') as price > > Then I discovered that sometimes this returns leading spaces I don't > want. I can get rid of them like this: > > trim(to_char(price, '9,999,999.99')) as price > > Is that the recommended money formatting style, for amounts less than > 9,999,999.99? (assuming I'll tack on my own currency symbol? ). Other > there other general styles that folks like for this? Thanks, May be try docs, what? :-) test=# select to_char(123456, '9,999,999.99'); to_char --- 123,456.00 (1 row) test=# select to_char(123456, 'FM9,999,999.99'); to_char - 123,456 (1 row) test=# select to_char(123456, 'FM9,999,999.00'); to_char 123,456.00 (1 row) test=# select to_char(123, 'FM0,999,999.00'); to_char -- 0,000,123.00 (1 row) test=# select to_char(123456, 'LFM9,999,999.00'); to_char $123,456.00 (1 row) FM fill mode, skip blank spaces and zeroes (if not set '0' instead '9') L currency symbol (from actual locales) Right? Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Case insensitive string comparison?
On Tue, May 15, 2001 at 02:48:24PM +0200, Borek Lupoměský wrote:
>Is there an operator for case insensitive string comparison, or
> should I use regular expression matching with ~* '^string$'?
possibility:
- use upper() / lower() inside query
- regex operators: ~* or !~*
- case insensitive 'like'
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Re: binary data
On Fri, Jun 22, 2001 at 10:22:32AM -0400, Tom Lane wrote:
> He did say the rules for escaping things are tricky ;-). You need to
> double the backslashes, because interpretation of the string literal
> takes off one level of backslashing before bytea ever sees it:
>
> regression=# INSERT INTO log (data) VALUES ('null \\000 null');
> INSERT 273181 1
> regression=# SELECT octet_length(data), data FROM log;
> octet_length | data
> --+--
>10 | plain text
>19 | special chars \012 \001 \002
> 5 | null
>11 | null \000 null
> (4 rows)
And what use some better encoding if you have a lot of binary chars
in data. For example base64, that code 2 chars to 3 instead \\000
that encode 1 char to 4.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [SQL] Arithmetic operation on DATE
On Mon, Aug 13, 2001 at 05:48:57PM +0800, macky wrote: > is it possible to add a date datatype column to a number resulting to a > date.. > > > theoretically it should do this,,, > > X is in months > > date + X = date > > --> 2001-08-20 + 6 = 2002-02-20 test=# select '2001-08-20'::date + '6months'::interval; ?column? 2002-02-20 00:00:00+01 (1 row) ..see docs about the "interval" datetype. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(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
Re: [SQL] to_date/to timestamp going to BC
On Wed, Oct 03, 2001 at 05:14:02PM -0400, Tom Lane wrote:
> [EMAIL PROTECTED] writes:
> > Can someone tell me if this is a bug with the date functions or am I using
> > them incorrectly?
>
> I get the right thing when I use the right format:
>
> regression=# select dt, to_timestamp(dt, 'FMMonth dd, ') from test_date;
>dt | to_timestamp
> +
> March 11, 1997 | 1997-03-11 00:00:00-05
> (1 row)
>
> However, I'd agree that this shows a lack of robustness in to_timestamp;
> it's not objecting to data that doesn't match the format.
The manual is transparent about this. I can add feauture that will
check everythig, but users who knows read manual and use already
debugged queries will spend CPU on non-wanted code.
Hmm.. I look at Oracle, and it allows parse queries like:
SVRMGR> select to_date('March 11, 1997', 'Month dd, ') from dual;
TO_DATE('
-
11-MAR-97
1 row selected.
.. well, I add it to my TODO for 7.3 (I plan rewrite several things
in to_* functions).
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] to_date/to timestamp going to BC
On Thu, Oct 04, 2001 at 07:44:14AM -0700, Josh Berkus wrote:
> Karel,
>
> > .. well, I add it to my TODO for 7.3 (I plan rewrite several things
> > in to_* functions).
>
> How about a to_char function for INTERVAL? Please, oh please?
oh, needn't please.. already in right now breeding 7.2 :-)
test=# SELECT to_char('5months 3sec 4h 1min'::interval, 'HH:MI:SS Mon');
to_char
--
04:01:03 May
(1 row)
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] to_char() and order-by
On Thu, Jun 27, 2002 at 12:27:11PM -0700, Jeff Boes wrote: > Perhaps this was covered in the 7.2.x release notes, but it's mystifying us: Please, how are data without to_char()? select time_link from stat_fetch where time_link is not null order by 1 desc limit 10; Karel > select to_char(time_link,'999.99') from stat_fetch > where time_link is not null > order by 1 desc limit 10; > > (time_link is a double-precision column.) Under 7.1.3, this returns results like: > > to_char > - > 9.99 > 9.99 > 9.99 > 9.99 > 9.99 > 9.99 > 9.99 > 9.99 > 9.99 > 9.99 > (10 rows) > > while under 7.2.1, you get the top 10 rows in *numerical* order: > > to_char > - > 278.78 > 261.07 > 240.25 > 180.24 > 173.26 > 160.35 > 159.02 > 144.57 > 134.21 > 131.66 > (10 rows) -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: datatype matrix (was: Re: [SQL] Sorry..)
On Tue, Jul 16, 2002 at 12:37:04PM -0500, Kevin Brannen wrote: > Josh Berkus wrote: > > Christopher, > > > > > >>In the bad old days when we couldn't distinguish explicit from implicit > >>cast functions, I was wary of adding new cast pathways. Too many > >>implicit casts and you have no type system at all. But in 7.3 there > >>should be no reason to object to an explicit-only cast from numeric > >>to text or vice versa. > > > > > > I'd suggest making the explicit cast of numeric to text be the exact > > equivalent of: > > > > SELECT btrim(to_char(numeric, '999,999,999,999.99')) > > or similar. Note: the btrim() call is needless. You can use Fill-Mode: SELECT to_char(numeric, 'FM999,999,999,999.99'); ^^^ It's in docs :-) Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Formatting current_time output
On Thu, Oct 03, 2002 at 10:46:19AM -0400, Thomas Good wrote: > > SELECT TO_CHAR(SYSDATE,'HH:MI AM') FROM DUAL; -- Oracle > SELECT TIME_FORMAT(current_time,'%l:%i %p'); -- MySQL SELECT TO_CHAR(now(),'HH:MI AM'); -- PostgreSQL :-) -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problems with to_char(created, 'WW')
On Thu, Oct 24, 2002 at 12:51:35PM +0200, Andreas Joseph Krogh wrote: > > janerik=# select created, to_char(created, 'WW') as week from session WHERE > username IS NULL ORDER BY week; Please, see docs and 'IW' (ISO week). Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> >
> > I just discovered that to_date() function does not check if supplied
> > date is correct, giving surprising (at least for me) results:
> >
> > fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
> > to_date
> >
> > 2003-12-01
> >
> > or even
> >
> > fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
> > to_date
> >
> > 2007-01-03
> >
> > to_timestamp() seems to work the same way. It's probably useful sometimes,
> > but not in my case... Is it how it supposed to work?
> > If so, how can I do such a validity check?
> > If not, has something changed in 7.4?
No change in 7.4. Maybe in 7.5 or in some 7.4.x.
> As far as I know these results are correct in terms of the underlying
> C-library function mktime(). This function is intended to be used when
> adding/subtracting intervals from a given timestamp.
> I don't know of any postgres function doing the check you're looking for.
> But I can't believe this is the first time this topic is brought up.
> You may search the archives on "date plausibility" are related terms.
The others PostgreSQL stuff which full parse (means check ranges)
date/time is less optimistic with this:
# select '31.11.2003'::date;
ERROR: date/time field value out of range: "31.11.2003"
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Display format for column
On Fri, May 07, 2004 at 05:08:21PM +0800, Abdul Wahab Dahalan wrote:
> Hi there!
>
> How do we format column size for displaying data in postgresql.
> for example my data type for customername is varchar(100) and I want to
> display the first 25 chars.
> How should I do that?
"display" a data is a client problem only, but you can select part of
string:
# select substring('qwertyuiopasdfg' from 0 for 8);
substring
---
qwertyu
or for your case:
select substring(customername from 0 for 25) FROM yourtab;
http://www.postgresql.org/docs/7.4/static/functions-string.html
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Formatting problems with negative intervals, TO_CHAR
On Sun, Jun 06, 2004 at 06:40:56PM -0400, Tom Lane wrote:
> Jeff Boes <[EMAIL PROTECTED]> writes:
> > This seems ... well, counter-intuitive at least:
> > (using Pg 7.4.1)
>
> > # select to_char('4 minutes'::interval -
> > '5 minutes 30 seconds'::interval, 'mi:ss');
>
> > to_char
> > -
> > -1:-3
> > (1 row)
>
> > Why is the trailing zero lost? Why are there two minus signs?
>
> > I would expect '-1:30'.
>
> Yeah, me too. The underlying interval value seems right:
>
> regression=# select '4 minutes'::interval - '5 minutes 30 seconds'::interval;
> ?column?
> ---
> -00:01:30
> (1 row)
>
> so I think this is a to_char() bug. Possibly it's platform-dependent
> --- the roundoff behavior for division with a negative input varies
> across machines. However I do see the bug on HPUX 10.20 with CVS tip.
Please, read PostgreSQL docs.
http://www.postgresql.org/docs/7.4/static/functions-formatting.html
Warning: to_char(interval, text) is deprecated and should not be
used in newly-written code. It will be removed in the next version.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Formatting problems with negative intervals, TO_CHAR
On Mon, Jun 07, 2004 at 11:08:37AM +0200, Stefan Weiss wrote:
> On Monday, 07 June 2004 09:52, Karel Zak wrote:
> > http://www.postgresql.org/docs/7.4/static/functions-formatting.html
> >
> > Warning: to_char(interval, text) is deprecated and should not be
> > used in newly-written code. It will be removed in the next version.
>
> This is news for me. Are there any suggestions what we should replace
> TO_CHAR with? For example, we were using TO_CHAR to print timestamp
> values in ISO format without milliseconds ("-MM-DD HH24:MI:SS" style),
> regardless of the current datestyle setting.
Ah.. ONLY the INTERVAL version of TO_CHAR() is deprecated! All others
versions for numbers, timestamp or date are supported now and in future
versions too.
Karel
--
Karel Zak <[EMAIL PROTECTED]>
http://home.zf.jcu.cz/~zakkr/
---(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
Re: [SQL] Formatting an Interval
On Fri, 2004-12-31 at 12:41 -0700, Michael Fuhr wrote: > On Fri, Dec 31, 2004 at 11:37:32AM -0500, Jamison Roberts wrote: > > > All of the functions that i've looked at seem to only extract parts > > from Intervals. What I need to do is to format the interval. For > > instance, I have a Interval with the value 1 day 07:57:52. I would > > like that in HH:MM:SS. So in the example the output would be > > 31:57:52. > > I'm not aware of a built-in way to get such a format; somebody > please correct me if I'm mistaken. > > You could write a function to format the interval. For example, > with PL/pgSQL you could use EXTRACT(epoch FROM interval_value) to > convert the interval to a number of seconds; convert that to hours, > minutes, and seconds; and use TO_CHAR to format the return value. to_char() works with standard date/time ranges, for example 1-24 -- so there is no way how convert to anything like "31:57:52". Karel -- Karel Zak <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Pg/PLSQL Errors!!
On Tue, 30 May 2000 [EMAIL PROTECTED] wrote: > I am running PostgreSQL 6.5.3 on RedHat Linux 6.1 on a PC. > I am trying to use Pg/PLSQL. > > I even tried being conservative enough to copy the example code from > http://www.postgresql.org/docs/user/c40874340.htm as follows: > CREATE FUNCTION add_one (int4) RETURNS int4 AS ' > BEGIN > RETURN $1 + 1; > END; > ' LANGUAGE 'plpgsql'; > > > though, even that results in "ERROR: Unrecognized language specified in a CREATE >FUNCTION: 'pl-pgsql'. Recognized languages are sql, C, internal and > the created procedural languages." > > I have tried variants including PLSQL,PG/PLSQL,PL/SQL,PGSQL and even 'internal > procedural language(s)' and 'created procedural language(s)' though with the same >error. > > Does anyone know of the name of the postgreSQL's procedural language of form like > CREATE function funcName(arguments) returns returntype AS 'BEGIN > statement block END;' LANGUAGE '??'; Do you install this language in your DB? See the script 'createlang' in PG binary tree. Karel
[SQL] ANNOUNCE: pg_dumplo 0.0.5
The PostgreSQL pg_dumplo 0.0.5; in brief: --- available at: * ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.5.tar.gz * or will available in the PostgreSQL contrib tree in the CVS --- features: pg_dumplo [option] -h --helpthis help -u --user= username for connection to server -p --password= password for connection to server -d --db= database name -t --host= server hostname -s --space= directory with dump tree (for export/import) -i --import import large obj dump tree to DB -e --export export (dump) large obj to dump tree -l dump attribute (columns) with LO to dump tree -a --all dump all LO in DB (default) -r --remove if is set '-i' try remove old LO -q --quiet run quietly -w --shownot dump, but show all LO in DB Example (dump): pg_dumplo -d my_db -s /my_dump/dir -l t1.a t1.b t2.a pg_dumplo -a -d my_db -s /my_dump/dir Example (import): pg_dumplo -i -d my_db -s /my_dump/dir Example (show): pg_dumplo -w -d my_db Note: * option '-l' must be last option! * option '-i' without option '-r' make new large obj in DB not rewrite old, the '-i' UPDATE oid numbers in table.attr only! * if is not set option -s, the pg_dumplo use $PWD Karel
Re: [SQL] ANNOUNCE: pg_dumplo 0.0.5
On Thu, 15 Jun 2000, The Hermit Hacker wrote: > On Thu, 15 Jun 2000, Karel Zak wrote: > > > > > > > The PostgreSQL pg_dumplo 0.0.5; in brief: > > > > --- available at: > > > > * ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.5.tar.gz > > > > * or will available in the PostgreSQL contrib tree in the CVS > > What license is it under? Should we make it part of the regular bin > directory, like pg_dump and pg_dumpall? License? Total hard, like Oracle --- you must say my how CPU and number of user in your system and I will count.. Sorry of my irony, it is like the others things which I wrote for PG. FREE, FREE, FREE and (C) The PostgreSQL Devel. Team :-) Karel
Re: [SQL] trigger or something else?
On Wed, 28 Jun 2000, Emils Klotins wrote:
> Hello,
>
> I have a table that has to have several fields with different names,
> but equal content. Sounds stupid, but it is because I have 2
> different programs querying the same table for user information and
> each of them uses differently named fields.
Why you not use any VIEW, for example:
CREATE TABLE xxx (a text);
CREATE VIEW v_xxx AS select a as field1, a as field2 from xxx;
test=> INSERT INTO xxx VALUES ('q');
INSERT 380446 1
test=> SELECT * FROM v_xxx;
field1 | field2
+
q | q
(1 row)
Karel
Re: [SQL] Bug in to_char()
On Wed, 12 Jul 2000, Brian Powell wrote: > Greetings, > > Working with PostGreSQL 7.02, I found the following problem: PM/AM is already known bug and is already fixed in current CVS. In 7.1 it will correct. Karel
Re: [SQL] a question about dates and timestamp
> my question is how can i convert the numeric or the int4 value to > a date value? test=# select abstime(965293003); abstime 2000-08-03 10:56:43+02 (1 row) Karel
Re: [SQL] a question about dates and timestamp
On Thu, 3 Aug 2000, Ange Michel POZZO wrote:
> Le jeu, 03 aoű 2000, Karel Zak a écrit :
> > > my question is how can i convert the numeric or the int4 value to
> > > a date value?
> >
> >
> > test=# select abstime(965293003);
> > abstime
> >
> > 2000-08-03 10:56:43+02
> > (1 row)
> >
> > Karel
>
>
> this is the result of the query for me :
>
> test=> select abstime(965293003);
> ?column?
> -
> 965293003
> (1 row)
Ooops I use CVS version, but you have probably 6.5...
Sorry, Now I haven't time check what is 7.1 / 7.0 / 6.5 feature.
in current code:
test=# select '965293003.01'::int;
ERROR: pg_atoi: error in "965293003.01": can't parse ".01"
test=# select '965293003.01'::numeric(9, 0);
?column?
---
965293003
(1 row)
test=# select '965293003.01'::numeric(9, 0)::int;
?column?
---
965293003
(1 row)
test=# select abstime('965293003.01'::numeric(9, 0)::int);
abstime
2000-08-03 10:56:43+02
(1 row)
BTW. The postgreSQL has good date/time support; why you use slow and
expensive numeric?
Re: [SQL] Extracting data by months
On Fri, 4 Aug 2000, Sandis wrote:
> I do it like this:
>
> SELECT datums FROM jaunumi
> WHERE date_part('year',datetime(datums)) = '2000' AND
> date_part('month',datetime(datums)) = '08';
>
> Where datums is a timestamp field.
Or (in 7.0):
SELECT datums FROM jaunum WHERE to_char('/MM', datums) = '2000/08';
becuase it more simple and it *will faster*.
Karel
