Re: [SQL] Poor document!!
Noodle wrote: > > I cannot believe that PostgreSQL has a so poor document, I cannot found any > useful information. I afraid I have to select MySQL. > > Does anybody know if PostgreSQL support Unicode and Full-Text Index? I > couldn't found any information about these from http://www.postgresql.org! Try typing "Unicode" into the search page and press the "Search" button. If you find this too difficult then another system might well be more suitable for you. The administrator's manual has a whole section on multibyte support. See the contrib/ directory for details on full-text indexing (fti). - Richard Huxton ---(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
[SQL] my pgsql error?
Please could a kind soul help me with this.
I can't find _any_ - however cryptic - docs about plpgsql.
create function nice_date(date) returns varchar as '
declare
t alias for $1;
d text;
m text;
y text;
begin
day := rtrim(to_char(\'t\'::timestamp, \'Day\'));
month := rtrim(to_char(\'t\'::timestamp, \'DD Month\'));
year := rtrim(to_char(\'t\'::timestamp, \'\' ));
nd := d || m || y;
end;
return nd;
end;' language 'plpgsql';
It seems to load ok, but on exection I get this message.
chris=# select nice_date(date('today'));
NOTICE: plpgsql: ERROR during compile of nice_date near line 12
ERROR: parse error at or near "return"
Please, what am I doing wrong?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] copy a record from one table to another (archive)
Hello there Is it possible to move a record from one table to another to do a archive with a simple command like move ? Thanks jr PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] my pgsql error?
From: "Christopher Sawtell" <[EMAIL PROTECTED]> > Please could a kind soul help me with this. > I can't find _any_ - however cryptic - docs about plpgsql. You need to look in the User's guide Chapter 9 (in the pre-release docs anyway) > create function nice_date(date) returns varchar as ' > declare > t alias for $1; > d text; > m text; > y text; > begin > day := rtrim(to_char(\'t\'::timestamp, \'Day\')); > month := rtrim(to_char(\'t\'::timestamp, \'DD Month\')); > year := rtrim(to_char(\'t\'::timestamp, \'\' )); > nd := d || m || y; > end; > return nd; > end;' language 'plpgsql'; Two "end;" lines - remove the first to fix the error you are getting. Also - you define d,m,y and use day,month,year You don't define nd The to_char lines you are using will try and convert the literal string 't' to a timestamp. You will want some spaces in the nd value. So, your script will become: create function nice_date(date) returns varchar as ' declare t alias for $1; d text; m text; y text; nd text; begin d := rtrim(to_char(t::timestamp, \'Day\')); m := rtrim(to_char(t::timestamp, \'DD Month\')); y := rtrim(to_char(t::timestamp, \'\' )); nd := d || \' \' || m || \' \' || y; return nd; end;' language 'plpgsql'; Note to readers: this is not a general service, I'm in a good mood ;-) For a moment I thought you could do to_char(now(),'Day DD Month ' but you're quite right you need to rtrim() the various pieces. - Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] copy a record from one table to another (archive)
- Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 13, 2001 12:45 PM Subject: [SQL] copy a record from one table to another (archive) > Hello there > > Is it possible to move a record from one table to another to do a archive > with a simple command like move ? > begin; insert into archive_foo (select * from foo where foo_id=1); delete from foo where foo_id=1; commit; Is probably the closest you could get. Alternatively, you could wrap the above up in a function and just go: select do_archive_foo(1); - Richard Huxton ---(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] my pgsql error?
On Tuesday, 13. March 2001 12:53, Christopher Sawtell wrote:
> Please could a kind soul help me with this.
> I can't find _any_ - however cryptic - docs about plpgsql.
Click here:
http://www.postgresql.org/devel-corner/docs/programmer/plpgsql.html
> create function nice_date(date) returns varchar as '
> declare
> t alias for $1;
> d text;
> m text;
> y text;
> begin
Well, use the declared variable names instead of day, month, year:
d := rtrim(to_char(\'t\'::timestamp, \'Day\'));
m := rtrim(to_char(\'t\'::timestamp, \'DD Month\'));
y := rtrim(to_char(\'t\'::timestamp, \'\' ));
Just remove the following three lines ...
> nd := d || m || y;
> end;
> return nd;
... and write instead:
return d || m || y;
> end;' language 'plpgsql';
>
> It seems to load ok, but on exection I get this message.
>
> chris=# select nice_date(date('today'));
> NOTICE: plpgsql: ERROR during compile of nice_date near line 12
> ERROR: parse error at or near "return"
>
> Please, what am I doing wrong?
There must be only one 'end;' at the, um, end of the function.
Hope that helps,
Christof
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg
Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] default value syntax - pg compared to?
Hello,
do you happen to know, if there is any sqlserver that WON'T handle
inserting default value if it is not specified in the INSERT field list? I
am particularly interested in ORACLE, INFORMIX and MYSQL .
Eg.
CREATE TABLE test
(
id integer default 5,
fld1 text
);
INSERT INTO test (fld1) VALUES ('blahblah');
So, is there any sqlserver that under these conditions will NOT
make id = 5 for the inserted row?
Background:
There is a package called phprojekt where the developer has
created some workarounds for different handlings of auto-
incrementing fields (sequences) for the above databases.
The only place he uses them, however, is in the INSERT
stataments (and he does not need to know the new id value for the
inserted row).
So I suggested he rewrite INSERTs explicitly specifying field
names and excluding the id field, rather than putting a server-
specific "default value" token (which postgres does not support
anyway). Will it work for the above other dbs?
TIA for answers!
Emils
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL] psql win32 version
Is there a win32 version for psql? JACK ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Silly question about numbering of rows?
Hi All, I haven't posted a silly question in a while, and didn't want to break my streak... I have a table like this: id | make | model | year | value -+---++---+--- 57 |2 |0 | 4 | 4750 57 |2 |3 | 4 | 4750 57 |2 |0 | 0 | 4750 57 |2 |0 | 3 | 4750 57 |2 |3 | 0 | 4750 57 |2 |3 | 3 | 4750 2 |2 |0 | 3 | 4750 2 |2 |3 | 3 | 4750 2 |2 |0 | 4 | 4350 2 |2 |3 | 4 | 4350 2 |2 |0 | 0 | 4750 2 |2 |0 | 5 | 4750 2 |2 |3 | 0 | 4750 2 |2 |3 | 5 | 4750 I want to be able to select from this table like this: select , id, value from mytable where make=2 model=3 and year=5 order by score desc; which would return this: nbring |id | value ---+-+--- 1 | 57 | 4750 2 | 2 | 4350 Everything is working swimmingly, except for the numbering magic. As you can see there are several variations of data in that table, so I can't use a serial on the table directly. Further, this table is likely to change on a minute by minute basis, so I don't want to create a permanent numbering that will just have to be changed. I thought about creating a temporary table with a serial and selecting into that so that a numbering is created and then returning rows from that, but that sounds like much busywork for the database. It sounds easiest to me to just punt and number the rows as they are returned in my calling application... What's the best approach here? -- Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Fwd: Silly question about numbering of rows?
Hi All, I haven't posted a silly question in a while, and didn't want to break my streak... I have a table like this: id | make | model | year | value -+---++---+--- 57 |2 |0 | 4 | 4750 57 |2 |3 | 4 | 4750 57 |2 |0 | 0 | 4750 57 |2 |0 | 3 | 4750 57 |2 |3 | 0 | 4750 57 |2 |3 | 3 | 4750 2 |2 |0 | 3 | 4750 2 |2 |3 | 3 | 4750 2 |2 |0 | 4 | 4350 2 |2 |3 | 4 | 4350 2 |2 |0 | 0 | 4750 2 |2 |0 | 5 | 4750 2 |2 |3 | 0 | 4750 2 |2 |3 | 5 | 4750 I want to be able to select from this table like this: select , id, value from mytable where make=2 model=3 and year=5 order by score desc; which would return this: nbring |id | value ---+-+--- 1 | 57 | 4750 2 | 2 | 4350 Everything is working swimmingly, except for the numbering magic. As you can see there are several variations of data in that table, so I can't use a serial on the table directly. Further, this table is likely to change on a minute by minute basis, so I don't want to create a permanent numbering that will just have to be changed. I thought about creating a temporary table with a serial and selecting into that so that a numbering is created and then returning rows from that, but that sounds like much busywork for the database. It sounds easiest to me to just punt and number the rows as they are returned in my calling application... What's the best approach here? -- Ken Corey, CTOAtomic Interactive, Ltd. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] != and <> operators
Patrik Kudo <[EMAIL PROTECTED]> writes: > I've for a while now been using the != operator in my sql queries, but in > almost all examples I see <> instead. Are there any good reason to use > any instead of the other? I prefer !=, but if you convince me, I'll change to > what you other guys are using ;-) "<>" is the SQL-standard name, and also the internal name of all these operators. There is a hardwired hack in the lexer to convert != to <>. So write whichever you feel like, but <> is what you'll see in dumps and so forth ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: Use of the LIMIT clause ?
On Mon, Mar 12, 2001 at 09:21:58PM -0500, Tom Lane wrote: > Spy <[EMAIL PROTECTED]> writes: > > Tom Lane a écrit : > >> Is that actually how MySQL interprets two parameters? We treat them > >> as count and offset respectively, which definition I thought was the > >> same as MySQL's. > > > But MySQL's syntax is different, as found on > > http://www.mysql.com/doc/S/E/SELECT.html : > > "SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] > > [SQL_BUFFER_RESULT] > > [...] > > [LIMIT [offset,] rows]" > > That's annoying; looks like we do it backwards from MySQL. Can anyone > confirm that this is how MySQL behaves (maybe it's a typo on this > documentation page)? Yes, it does behave as documented. > Should we consider changing ours if it is different? I don't know that it's worth it... it seems to inconvenience some people either way. I may soon be moving a moderately complex system from MySQL to Postgres and it wouldn't be the end of my world if I had to reverse all the LIMITs. Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] != and <> operators
How many of the other dbms' out there have this "hardwired hack"? Is something that's postgres-specific, or is it found in oracle and sqlserver too? Regards, Patrik Kudo ech`echo xiun|tr nu oc|sed 'sx\([sx]\)\([xoi]\)xo un\2\1 is xg'`ol Känns det oklart? Fråga på! On Tue, 13 Mar 2001, Tom Lane wrote: > Patrik Kudo <[EMAIL PROTECTED]> writes: > > I've for a while now been using the != operator in my sql queries, but in > > almost all examples I see <> instead. Are there any good reason to use > > any instead of the other? I prefer !=, but if you convince me, I'll change to > > what you other guys are using ;-) > > "<>" is the SQL-standard name, and also the internal name of all these > operators. There is a hardwired hack in the lexer to convert != to <>. > So write whichever you feel like, but <> is what you'll see in dumps and > so forth ... > > regards, tom lane > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL Dummy Needs Help
On Fri, Mar 09, 2001 at 11:05:38AM -0800, Alder wrote: > I'm pretty new to SQL and can't figure out how to write what should be a > simple query of two tables. Could someone here possibly help me out? > > Table 1 has two fields I'm interested in: TITLE_NO and EFFECT_DATE > Table 2 also has two fields of interest:TITLE_NO and PAIDTO_DATE > > TITLE_NO is unique in Table 1, meaning each TITLE will have a unique > EFFECT_DATE. Table 2 represents an accounting history of each TITLE, so for > each TITLE_NO there may be one or more PAIDTO_DATE values. In both Tables > the _DATE fields are stored as 9-character strings in the fomat MMDD. > In all cases, the MM and DD values in Table 2 should be identical with those > in Table 1. > > My intention is to produce a list that will contain the TITLE_NO of each > TITLE where the MMDD value for EFFECT_DATE differ from any of the > PAIDTO_DATE values for that TITLE_NO. The list must contain ONLY the > PAIDTO_DATE values that differ, and the corresponding TITLE_NO. I've seen a couple answers to this, but I think people aren't getting the question right. I'm presuming that the desired return values are the EFFECT_DATEs without matching PAIDTO_DATEs, rather than returning PAIDTO_DATEs, as stated, but that's just tweaking the return set. It seems to me you're looking for something like: select * from table1 t where not exists (select * from table2 where t.title_no = title_no and t.effect_date = paidto_date) Ross ---(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] error joining 2 views containing GROUP BYs
Thanks for your help. It runs blindlingly fast under beta5 so I'll keep my fingers crossed and hope it will be stable enough! Tom Lane wrote in message <[EMAIL PROTECTED]>... >"david morgan" <[EMAIL PROTECTED]> writes: >> I am having a few problems using a select query which joins two views >> containing aggregate functions (see query 1 below). > >I don't think there's any chance of making that work in pre-7.1 >Postgres. Sorry :-(. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Fwd: Silly question about numbering of rows?
Ken Corey writes: > It sounds easiest to me to just punt and number the rows as they are returned > in my calling application... The rows are automatically numbered. In libpq, you'd use PQgetvalue(result, rownumber, fieldnumber). Other interfaces have similar functions. If the interface has a "fetch"-like approach, it seems trivial to keep a counter. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] my pgsql error?
On Wed, 14 Mar 2001 01:38, Richard Huxton wrote: > From: "Christopher Sawtell" <[EMAIL PROTECTED]> > > > Please could a kind soul help me with this. [ ... ] > Note to readers: this is not a general service, I'm in a good mood ;-) In that case, thank you very much indeed, and may the blessings of the Deities be on you. > For a moment I thought you could do to_char(now(),'Day DD Month ' > but you're quite right you need to rtrim() the various pieces. Personally I think the fact that you have to do all that rtrim() stuff is very close to being a bug. What do you think? -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<-- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
