Re: [SQL] index compatible date_trunc in postgres?
Scott Marlowe escribió: > On Thu, Dec 18, 2008 at 10:46 PM, Bryce Nesbitt wrote: > > I've got a legacy app that does 8.3 incompatible date searches like so: > > explain select count(*) from contexts where publication_date like '2006%'; > > explain select count(*) from contexts where publication_date like > > '2006-09%'; > You can create an index on date_trunc (on timestamp without timezone, > but not on timestamp with timezone since it's not immutable) But you can create an index on the timestamptz AT TIME ZONE 'GMT', I think. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] index compatible date_trunc in postgres?
Bryce Nesbitt writes: > I've got a legacy app that does 8.3 incompatible date searches like so: > explain select count(*) from contexts where publication_date like '2006%'; > explain select count(*) from contexts where publication_date like > '2006-09%'; > I've got my choice of refactoring, but all these share the same > sequential scan limitation: Why are you worrying? The old method surely didn't get indexed either. (At least, it didn't unless you had an index on publication_date::text, in which case just throwing in the cast will produce the same results in 8.3.) 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
[SQL] Downgrade database and problem with sequences
I am currently using postgresql 8.3 and whenever I want to get the last inserted ID use : Select currval(pg_get_serial_sequence(table, field)) Then, I had to downgrade a database for certain customer to use postgresql 8.1, and when I execute that same function I found that pg_get_serial_sequence returns nothing. Is there any way to dump my database and then restore it on a previous version and keep using that pg_get_serial_sequence() function ? Thanks, Sebastian -- 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] Downgrade database and problem with sequences
"Sebastian Rychter" writes: > I am currently using postgresql 8.3 and whenever I want to get the last > inserted ID use : > Select currval(pg_get_serial_sequence(table, field)) > Then, I had to downgrade a database for certain customer to use postgresql > 8.1, and when I execute that same function I found that > pg_get_serial_sequence returns nothing. Probably because the column wasn't actually declared SERIAL to 8.1. I imagine you ignored the failure of the ALTER SEQUENCE OWNED BY command in the 8.3 dump, but that is exactly what the missing piece is here. 8.1 did not have an explicit OWNED BY command, and the only way to make the right dependency magic happen was to use the SERIAL keyword while creating the table. 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] index compatible date_trunc in postgres?
Tom Lane wrote:
Why are you worrying? The old method surely didn't get indexed either.
Continuous improvement?
Since there already IS an index available, I figure I might as well use
it, especially since this DB had real performance issues. The table
itself is medium sized in our world, at 647596 rows.
Scott Marlowe wrote:
You can create an index on date_trunc (on timestamp without timezone,
but not on timestamp with timezone since it's not immutable)
That could work...
create index brycetest5 on contexts (date_trunc('month',publication_date));
(13 MB index, same size as the index on the non-truncated date).
Unfortunately complains about immutability:
create index brycetest5 on contexts (to_char(publication_date,'-MM'));
But in this case I think I'll rewrite the query into a range, and stick
with the present index.
Thanks for your help.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Invitation to connect on LinkedIn
LinkedIn Mahmoud Ewiwi requested to add you as a connection on LinkedIn: -- Marcin, I'd like to add you to my professional network on LinkedIn. - Mahmoud View invitation from Mahmoud Ewiwi http://www.linkedin.com/e/XCcEdFwZmSzO1q19PFca3OmGtDdI_FirNbrhE9/blk/901916387_2/cBYTe3cScjANc3ALqnpPbOYWrSlI/svi/ -- DID YOU KNOW you can conduct a more credible and powerful reference check using LinkedIn? Enter the company name and years of employment or the prospective employee to find their colleagues that are also in your network. This provides you with a more balanced set of feedback to evaluate that new hire. http://www.linkedin.com/e/rsr/inv-27/ -- (c) 2008, LinkedIn Corporation
