[SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Good morning,
Could someone tell me the command to get the weekly day name and day
number please.
I am expecting something like:
sql> select data_part('day name', current_date);
sql> Monday
sql> select data_part('day number', current_date);
sql> 1
(Mon =1 ... Sun =7?)
Thanks a lot!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Emi Lu написа:
Good morning,
Could someone tell me the command to get the weekly day name and day
number please.
I am expecting something like:
sql> select data_part('day name', current_date);
sql> Monday
sql> select data_part('day number', current_date);
sql> 1
(Mon =1 ... Sun =7?)
You need "TO_CHAR"
(http://www.postgresql.org/docs/current/static/functions-formatting.html)
- "SELECT to_char(current_date, 'Dy')".
--
Milen A. Radev
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Hello
use to_char function
postgres=# select to_char(current_date, 'day');
to_char
---
wednesday
(1 row)
postgres=# select extract(dow from current_date);
date_part
---
3
(1 row)
regards
Pavel
2008/7/30 Emi Lu <[EMAIL PROTECTED]>:
> Good morning,
>
> Could someone tell me the command to get the weekly day name and day number
> please.
>
>
> I am expecting something like:
>
> sql> select data_part('day name', current_date);
> sql> Monday
>
> sql> select data_part('day number', current_date);
> sql> 1
>
> (Mon =1 ... Sun =7?)
>
> Thanks a lot!
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
Emi Lu wrote:
Good morning,
Could someone tell me the command to get the weekly day name and day
number please.
I am expecting something like:
sql> select data_part('day name', current_date);
sql> Monday
sql> select data_part('day number', current_date);
sql> 1
(Mon =1 ... Sun =7?)
Thanks a lot!
http://www.postgresql.org/docs/current/static/functions-formatting.html
select to_char(current_date,'Day');
select to_char(current_date,'D');
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get day name(Mon, Tue... Sun) and day number (1, 2...7) from a date
I am expecting something like:
sql> select data_part('day name', current_date);
sql> Monday
sql> select data_part('day number', current_date);
sql> 1
(Mon =1 ... Sun =7?)
Thanks a lot!
http://www.postgresql.org/docs/current/static/functions-formatting.html
This is exactly what I am looking for.
> select to_char(current_date,'Day');
> select to_char(current_date,'D');
1: Sunday
2: Monday
3: Tuesday
4: Wednesday
5: Thursday
6: Friday
7: Saturday
Thank you very much for all your inputs!
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] order function in aggregate
Hi,
I'm relatively new to object oriented databases and postgres (~ 4
months), so I'm not sure what is wrong with my custom aggregate
function. I'm using Postgres 8.3.3 (results are same on Windows and
Ubuntu versions).
Here is my data required to explain my problem:
--
CREATE TABLE aggr_test
(
id serial NOT NULL,
sub text,
grp text,
CONSTRAINT aggr_test_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
INSERT INTO aggr_test (id, sub, grp) VALUES (1, 'one', 'pom pom');
INSERT INTO aggr_test (id, sub, grp) VALUES (2, 'two', 'pom pom');
INSERT INTO aggr_test (id, sub, grp) VALUES (3, 'three', 'pom pom');
INSERT INTO aggr_test (id, sub, grp) VALUES (4, 'two', 'la la');
INSERT INTO aggr_test (id, sub, grp) VALUES (5, 'three', 'la la');
INSERT INTO aggr_test (id, sub, grp) VALUES (6, 'one', 'la la');
CREATE OR REPLACE FUNCTION concat(text, text)
RETURNS text AS
$BODY$DECLARE
t text;
BEGIN
IF $1 ISNULL OR $2 ISNULL THEN
t = COALESCE($1,$2);
ELSIF character_length($1) > 0 THEN
t = $1 ||', '|| $2;
ELSE
t = $2;
END IF;
RETURN t;
END;$BODY$
LANGUAGE 'plpgsql' IMMUTABLE
COST 100;
CREATE AGGREGATE commacat("text") (
SFUNC=concat,
STYPE=text,
SORTOP="<"
);
--
Now, I would like to group a summary of the strings in "sub", but they
must be ordered alphabetically. Here I attempt the SQL:
select commacat(sub), grp from aggr_test group by grp;
However, on my system it will output the "commacat" column as:
"one, two, three";"pom pom"
"two, three, one";"la la"
(notice that the items in the first column are ordered differently,
since the input values were entered in non-alphabetically)
This is where I get confused, since in the aggregate function, I have
specified `SORTOP="<"`, which according to the documentation
(http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html)
"must be equivalent to":
SELECT sub FROM aggr_test ORDER BY sub USING <; -- LIMIT 1;
or
SELECT sub FROM aggr_test ORDER BY sub USING >; -- LIMIT 1;
(I've modified the example to show the desired effect of ASC and DESC
sorting, respectively)
So my question is: why is `SORTOP="<"` in my "commacat" aggregate
function not working? Any suggestions? Is this a bug?
Note: I am aware that I can achieve my goal using a subquery:
SELECT commacat(sub), grp FROM
(SELECT * FROM aggr_test ORDER BY grp, sub) AS foo
GROUP BY grp;
however, I'm looking to see if this ordering can done naturally within
the aggregate function.
Thanks in advance!
+mt
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order function in aggregate
Michael Toews wrote: Now, I would like to group a summary of the strings in "sub", but they must be ordered alphabetically. Here I attempt the SQL: select commacat(sub), grp from aggr_test group by grp; However, on my system it will output the "commacat" column as: "one, two, three";"pom pom" "two, three, one";"la la" (notice that the items in the first column are ordered differently, since the input values were entered in non-alphabetically) This is where I get confused, since in the aggregate function, I have specified `SORTOP="<"`, which according to the documentation (http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html) "must be equivalent to": SELECT sub FROM aggr_test ORDER BY sub USING <; -- LIMIT 1; That's for things like min()/max() where you only want the first/last value of some set. You want all of them. You could accumulate the values in an array and then sort that with the final-func that create aggregate supports. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] order function in aggregate
Richard Huxton <[EMAIL PROTECTED]> writes: > Michael Toews wrote: >> This is where I get confused, since in the aggregate function, I have >> specified `SORTOP="<"`, which according to the documentation >> (http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html) >> "must be equivalent to": >> >> SELECT sub FROM aggr_test ORDER BY sub USING <; -- LIMIT 1; > That's for things like min()/max() where you only want the first/last > value of some set. You want all of them. Right: sortop is an assertion that the aggregate behaves like min() or max(), not a request for the system to do something strange to the aggregate's input. > You could accumulate the values in an array and then sort that with the > final-func that create aggregate supports. The traditional way to get presorted input to an aggregate is select myagg(x) from (select whatever as x from ... order by ...) ss; You can avoid that with an internal sort in the aggregate, as Richard suggests, but it might not be worth the trouble --- and in any case it'd be hard to sort internally on anything except the exact values being aggregated. With the subselect approach you can order on anything at all. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Identifying which column matches a full text search
Richard Huxton wrote:
>
> Failing that, where I've had many (a dozen) different sources but want
> to search them all I've built a textsearch_blocks table with columns to
> identify the source and have triggers that keep it up to date.
Once you've built the text search blocks table, how do you search it? Do you
perform
twelve separate queries or can you just do one?
Ryan
Ryan Wallace wrote:
>
> UPDATE pgweb SET textsearchable_index_col =
> to_tsvector('english', coalesce(title,'') || coalesce(body,''));
> WHERE textsearchable_index_col @@ to_tsquery('create & table')
> Using this approach. Is there any way of retrieving which of the original
> two columns the match was found in?
Afraid not - you're not indexing two columns, you're indexing one:
textsearchable_index_col.
You can add up to four weights to a tsvector though, typically for
title/body matching. See chapter 12.3 for details.
Failing that, where I've had many (a dozen) different sources but want
to search them all I've built a textsearch_blocks table with columns to
identify the source and have triggers that keep it up to date.
--
Richard Huxton
Archonet Ltd
No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.138 / Virus Database: 270.5.6/1579 - Release Date: 7/29/2008
6:43 AM
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Identifying which column matches a full text search
Ryan Wallace wrote: Richard Huxton wrote: Failing that, where I've had many (a dozen) different sources but want to search them all I've built a textsearch_blocks table with columns to identify the source and have triggers that keep it up to date. Once you've built the text search blocks table, how do you search it? Do you perform twelve separate queries or can you just do one? OK, you have a table something like: fulltext_blocks ( section varchar(32), itemid int4, wordstsvector,them PRIMARY KEY (section, itemid) ) Now assume two of the things I search are "news" and "faqs". I'm assuming they've both got a simple serial pkey - if not, "itemid" above needs to be text and you'll have to cast. For each target table (news, faqs) add a trigger that updates fulltext_blocks appropriately. This can include weighting title and body of a news article. Then, search the fulltext_blocks table, optionally filtering by section. If you're going to have lots of results put the ids into a (perhapd temporary) results-table. Then join your results back to the original tables with the appropriate UNION (if you need to - it might be you fetch results one at a time elsewhere in your app). SELECT n.id, n.title, n.body FROM news n JOIN results r ON n.id=r.id WHERE r.section='news' UNION ALL SELECT f.id, f,question, f.answer FROM faqs f JOIN results r ON f.id=r.id WHERE r.section='faqs' ; You'll probably want to set ownership/permissions on the triggers / fulltext_blocks table so you can't accidentally update it directly. In mine I even had a "documents" section which relied on an external cron-driven script to strip the first 32k of text out of uploaded documents (pdf,word) in addition to user-supplied metadata (title, summary). Note - this is basically simulating what we could do if you could index a view. The fulltext_blocks table is nothing more than a materialised view. HTH -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
