Re: [SQL] return records with more than one occurrences
Hi, this is a good point to start. select t2.* from table2 t2 inner join ( select id from table1 group by id having count(*) > 1 ) t1 on t2.id_table1 = t1.id 2011/1/5 Tarsis Lima > how would the SELECT to return only records with more than one > occurrences of id_table1? example: > -- Table1 > -- id -- > - >1 >2 > > > -- Table2 > id --- | id_table1 | name > -- > 4 | - 1 -- | Tom > 5 | - 1 -- | Luci > 6 | - 2 -- | Cleber -->this can not return > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Re: [SQL] pattern matching with dates?
On Wed, Jan 5, 2011 at 11:39 AM, Good, Thomas wrote:
> This dubious query worked well previously:
> select * from db_log where log_date LIKE '2011-01-%';
> (currently works on bluehost.com where they run 8.1.22)
>
> Can someone offer a (preferably ANSI compliant) way to do this on 8.4.5?
>
> I realize that >= and so on work well (which may explain why the docs
> are pretty silent about pattern matching with dates) but sometimes it's
> nice to
> treat the (ISO) date as a string.
>
>
I'd think that avoiding treating the date as a string would lend itself to
using indexes so would get much faster results, but even without indexes,
handling dates as binary types is likely faster than regex comparisons on
the column cast to a string.
I made your original query work on my 8.4.x database by just explicitly
casting the column to text like this:
select * from db_log where log_date::text LIKE '2011-01-%'
Are you looking for all rows where log_date is in january, 2011 or where
log_date is in current month or something else? I can think of lots of
potential ways to solve this:
where log_date >= date_trunc('month', '2011-01-01'::date)
where date_trunc('month', log_date) = '2011-01-01'
where log_date >= date_trunc('month', current_date)
where date_trunc('month', log_date) = date_trunc('month', current_date)
where date_part('year', log_date) = 2011 and date_part('month', log_date) =
1
where date_part('year', log_date) = date_part('year', current_date) and
date_part('month', log_date) = date_part('month', current_date)
where log_date between x and y
where ('2011-01-01'::date, '2011-01-01'::date + interval '1 month') overlaps
(log_date, log_date)
where (current_date, current_date + interval '1 month') overlaps (log_date,
log_date)
http://www.postgresql.org/docs/8.4/static/functions-datetime.html
Re: [SQL] return records with more than one occurrences
Howdy, Tarsis. Please try this out. SELECT a.id, id_table1,a.name FROM "Table2" a NATURAL JOIN (SELECT id_table1 "Table2" GROUP BY id_table1 HAVING COUNT(*) > 1) b Tell me if it worked or not, and if it didn't the errors/uncorrect results. Best, Oliveiros - Original Message - From: "Tarsis Lima" To: Sent: Wednesday, January 05, 2011 2:18 PM Subject: [SQL] return records with more than one occurrences how would the SELECT to return only records with more than one occurrences of id_table1? example: -- Table1 -- id -- - 1 2 -- Table2 id --- | id_table1 | name -- 4 | - 1 -- | Tom 5 | - 1 -- | Luci 6 | - 2 -- | Cleber -->this can not return -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pattern matching with dates?
Hello Thomas, On 05.01.2011 20:39, Good, Thomas wrote: select * from db_log where log_date LIKE '2011-01-%'; The lazy way would be something like this: SELECT * from tab WHERE log_date::VARCHAR LIKE '2011-01-%'; The more proper way is: SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and EXTRACT(MONTH FROM log_date) = 1; Susanne -- Susanne Ebrecht - 2ndQuadrant PostgreSQL Development, 24x7 Support, Training and Services www.2ndQuadrant.com -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pattern matching with dates?
Sorry, but this examples not use index. On 1/7/11, Susanne Ebrecht wrote: > Hello Thomas, > > On 05.01.2011 20:39, Good, Thomas wrote: >> select * from db_log where log_date LIKE '2011-01-%'; > > The lazy way would be something like this: > SELECT * from tab WHERE log_date::VARCHAR LIKE '2011-01-%'; > > The more proper way is: > SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and > EXTRACT(MONTH FROM log_date) = 1; > > Susanne > > -- > Susanne Ebrecht - 2ndQuadrant > PostgreSQL Development, 24x7 Support, Training and Services > www.2ndQuadrant.com > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent from my mobile device pasman -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pattern matching with dates?
On 07/01/11 14:15, pasman pasmański wrote:
Sorry, but this examples not use index.
Looks like it does here (oh, and please try to post your replies at the
bottom of the message)
On 1/7/11, Susanne Ebrecht wrote:
The more proper way is:
SELECT * from tab WHERE EXTRACT(YEAR FROM log_date) = 2011 and
EXTRACT(MONTH FROM log_date) = 1;
CREATE TABLE d_table (d date);
INSERT INTO d_table SELECT '2001-01-01'::date + generate_series(1,);
CREATE INDEX d_extract_idx ON d_table ( extract('day' FROM d) );
EXPLAIN ANALYSE SELECT * FROM d_table WHERE extract('day' FROM d) = 3;
That ends up doing a bitmap index scan for me.
Of course, it's entirely possible an index on year+month returns too
many rows to be useful.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] return records with more than one occurrences
SELECT t.id t.id_table1 t.name FROM Table2 t INNER JOIN (SELECT t_inner.id_table1 FROM Table2 t_inner GROUP BY 1 HAVING COUNT(*) > 1) temp ON temp.id_table1 = t.id_table1 ; On Wed, Jan 5, 2011 at 8:18 AM, Tarsis Lima wrote: > how would the SELECT to return only records with more than one > occurrences of id_table1? example: > -- Table1 > -- id -- > - >1 >2 > > > -- Table2 > id --- | id_table1 | name > -- > 4 | - 1 -- | Tom > 5 | - 1 -- | Luci > 6 | - 2 -- | Cleber -->this can not return > > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
