Re: [GENERAL] Error in crosstab using date_trunc
On 6 Dec 2009, at 4:13, Scott Marlowe wrote:
> On Sat, Dec 5, 2009 at 12:11 PM, Jose Maria Terry Jimenez
> wrote:
>> Hello all,
>>
>> I'm trying to do a crosstab from data that row names are times.
>>
>> These times are timestamps and i want to use they truncating to minutes
>> this works for me:
>>
>> select distinct date_trunc('minute',"timestamp") as "timestamp" from
>> historico order by "timestamp";
>>
>> Getting times "normalized" without seconds.
>>
>> If i do a crosstab using that date_trunc function i get errors. If i do:
>>
>> select *
>> from crosstab
>> (
>> 'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
>> historico order by 1,2'
>> )
>> as
>> (anet timestamp without time zone,
>> re1 numeric,
>> re2 numeric,
>> re3 numeric
>> )
>> ;
>
> Looks like an escaping issue. Try replacing your outer ' with $outer$
> or something like that:
It _is_ an escaping issue. The part inside the crosstab consists of 3 literals,
namely:
'select date_trunc('
minute
',"timestamp") as "timestamp",remota,valor from historico order by 1,2'
You need to escape the quotes around 'minute', either by typing ''minute'' or
by using a different quote method for the outer literal as Scott suggested.
Your last option is the non-standard \' escaping.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4b1b914911734630115167!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql_autodoc in Python?
Hello, has anyone ever implemented something similar to postgresql_autodoc in Python? TIA, Sincerely, Wolfgang -- NO "Courtesy Copies" PLEASE! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in crosstab using date_trunc
Richard Broersma escribió:
On Sat, Dec 5, 2009 at 11:11 AM, Jose Maria Terry Jimenez
wrote:
select distinct date_trunc('minute',"timestamp") as "timestamp" from
historico order by "timestamp";
Notice the example from the documentation:
http://www.postgresql.org/docs/8.4/interactive/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
SELECT date_trunc('hour', TIMESTAMP '2001-02-16 20:38:40');
Result: 2001-02-16 20:00:00
In this case the keyword the second argument of the date_trunc() function is:
TIMESTAMP '2001-02-16 20:38:40'
This text is a special kind of cast that exists for time based datatypes.
This could be re-written as:
CAST( '2001-02-16 20:38:40' AS TIMESTAMP)
or
'2001-02-16 20:38:40'::TIMESTAMP
Other examples would be:
SELECT date_trunc( 'hour', now())
or
SELECT date_trunc( 'hour', myTimestampColumn)
FROM MyTable
LIMIT 1;
I hope this helps.
Thanks you by your answer, but the problem is date_func didn't worked in
a crosstab query. It is solved by escaping with two ' the keyword minute
' 'minute' ' as others reply me.
Best,
Jose Maria
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10114 - Fri Dec 4 23:54:13 2009
by Markus Madlener @ http://www.copfilter.org
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in crosstab using date_trunc
Scott Marlowe escribió:
On Sat, Dec 5, 2009 at 12:11 PM, Jose Maria Terry Jimenez
wrote:
Hello all,
I'm trying to do a crosstab from data that row names are times.
These times are timestamps and i want to use they truncating to minutes
this works for me:
select distinct date_trunc('minute',"timestamp") as "timestamp" from
historico order by "timestamp";
Getting times "normalized" without seconds.
If i do a crosstab using that date_trunc function i get errors. If i do:
select *
from crosstab
(
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2'
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;
Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:
select *
from crosstab
(
$outer$ select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2 $outer$
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;
And see if that helps.
Thank you very much. This worked, also worked with ' instead $outer$ BUT
escaping the ' in minute with two of them ' 'minute' '.
What does $outer$ or when i must use it?
Best,
Jose Maria
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10115 - Sun Dec 6 09:37:09 2009
by Markus Madlener @ http://www.copfilter.org
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in crosstab using date_trunc
Alban Hertroys escribió:
On 6 Dec 2009, at 4:13, Scott Marlowe wrote:
On Sat, Dec 5, 2009 at 12:11 PM, Jose Maria Terry Jimenez
wrote:
Hello all,
I'm trying to do a crosstab from data that row names are times.
These times are timestamps and i want to use they truncating to minutes
this works for me:
select distinct date_trunc('minute',"timestamp") as "timestamp" from
historico order by "timestamp";
Getting times "normalized" without seconds.
If i do a crosstab using that date_trunc function i get errors. If i do:
select *
from crosstab
(
'select date_trunc('minute',"timestamp") as "timestamp",remota,valor from
historico order by 1,2'
)
as
(anet timestamp without time zone,
re1 numeric,
re2 numeric,
re3 numeric
)
;
Looks like an escaping issue. Try replacing your outer ' with $outer$
or something like that:
It _is_ an escaping issue. The part inside the crosstab consists of 3 literals,
namely:
'select date_trunc('
minute
',"timestamp") as "timestamp",remota,valor from historico order by 1,2'
You need to escape the quotes around 'minute', either by typing ''minute'' or
by using a different quote method for the outer literal as Scott suggested.
Your last option is the non-standard \' escaping.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4b1b914911734630115167!
Thank you very much. This worked for me.
Best,
Jose Maria
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiVirus: ClamAV 0.95.2/10115 - Sun Dec 6 09:37:09 2009
by Markus Madlener @ http://www.copfilter.org
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] numeric cast oddity
Sim Zacks writes: > If I replace that column with -1::numeric(20,4) or - (1::numeric(20,4)) > the type that goes to the view is numeric without any scale or precision > and then I get an error that I cannot change the column type. You've still got the order of operations wrong. (-1)::numeric(20,4) is known to have typmod (20,4), because the cast operation enforces it. - (1::numeric(20,4)) is not known to have any particular typmod --- it actually does fit in (20,4), of course, but that fact depends on the detailed behavior of the minus operator, which is not known to the type machinery. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in crosstab using date_trunc
2009/12/6 José María Terry Jiménez : > > Thank you very much. This worked, also worked with ' instead $outer$ BUT > escaping the ' in minute with two of them ' 'minute' '. > > What does $outer$ or when i must use it? It's a type of quoting... Take a look here: http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html section 4.1.2.2 for more info. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] using column as 'mutex'
RE all below: (hand assembled reply)
I looked through all the documentation, and I did not find 'WITH UPDATE' as
part of any transaction type, so I didn't end up using that information.
For the record, (in the mail lists), I am using symfony/Doctrine ORM. I used
something like this:
try {
Doctrine query to update a row's timestamp for 5-10 minute window
where value=value in a unique column
Find same record and hydrate an object (creates exception if
object does not exist) and assign to class variable/attribute
} catch(Exception $e) {
return FALSE;
}
return FALSE;
The calling code will know if the record was saved fromt he garbage collection
routine. However, I may end up writing a custome SQL queiry (i.e., not using
the ORM) when I put this into action against a running garbage collection
daemon.
On Mon, Nov 30, 2009 at 9:02 PM, Dennis Gearon
Dennis Gearon
Merlin Moncure wrote:
Just make sure that when you inspect the record, do so: a. in a
transaction (preferably a brief one) and b. WITH UPDATE until your
operation completes. That way if two operations collide one will wait
for the other to complete. If you need to lock an external structure
(like a file) for a period shorter or longer than a single
transaction, use an advisory lock.
merlin
> I want to use a dbase record in Postgresql as a mutex in a garbage
> collection/file upload system.
>
> If I tranfer an uploaded file to a directory, write all its meta information
> and file name in a record, the user should have 5 minutes to finish inputting
> the required info for the record. (I want to store in file system instead of
> database table).
>
> So there will be two processes racing against each other in the database, the
> web page changing the status of the record to 'complete', and the cron job
> erasing files that have status 'in-process' and are older than ten minutes.
>
> So there will probably be 6 possible scenarios:
>
> 1/ Web site changes status before the cron job gets to the record and deltes
> the file, no problem.
> 2/ Cron job changes the record and deletes the file before the website gets
> to the record, no problem.
>
> 3/ Both access the the record relatively at the same time, the web site
> having the earlier, lower transaction number and also finishes first.
> 4/ Both access the the record relatively at the same time, the cron job
> having the earlier, lower transaction number and also finishes first.
>
> 5/ Both access the the record relatively at the same time, the web site
> having the later, HIGHER transaction number BUT finishes first.
> 6/ Both access the the record relatively at the same time, the cron job
> having the later, HIGHER transaction number BUT finishes first.
Dennis Gearon
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] What packages I need to install to get Postgres working
Hi, I have used Postgres on windows, but now I need to develop a website in Linux envoirnment. I have installed Ubuntu Desktop, but now I need to know what packages install to get Postgres working. Best Regards, André.
Re: [GENERAL] What packages I need to install to get Postgres working
On 06/12/2009 21:35, Andre Lopes wrote: > I have used Postgres on windows, but now I need to develop a website in > Linux envoirnment. > > I have installed Ubuntu Desktop, but now I need to know what packages > install to get Postgres working. I think Ubuntu's packaging system is based on the Debian one, so you should just need to do - apt-get postgresql-8.3 - to get PG and all it depends on. If you want 8.4, you'll need to get it from backports. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What packages I need to install to get Postgres working
Hi, Thanks for your reply. I have installed postgresql 8.3, and now, what I need to do to get things working? Install PGAdmin? Or there are other steps I need to do to get things working? For example, how can I define the password for access as sysadmin? Best Regads, On Sun, Dec 6, 2009 at 9:48 PM, Raymond O'Donnell wrote: > On 06/12/2009 21:35, Andre Lopes wrote: > > > I have used Postgres on windows, but now I need to develop a website in > > Linux envoirnment. > > > > I have installed Ubuntu Desktop, but now I need to know what packages > > install to get Postgres working. > > I think Ubuntu's packaging system is based on the Debian one, so you > should just need to do - > > apt-get postgresql-8.3 > > - to get PG and all it depends on. If you want 8.4, you'll need to get > it from backports. > > Ray. > > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie >
Re: [GENERAL] What packages I need to install to get Postgres working
Andre Lopes wrote: Hi, Thanks for your reply. I have installed postgresql 8.3, and now, what I need to do to get things working? Install PGAdmin? Or there are other steps I need to do to get things working? For example, how can I define the password for access as sysadmin? yourusername $ sudo su - postgres postgres $ psql ... postgres=# create user yourusername with createdb createrole password 'newpassword'; CREATE USER postgres= # create database yourusername with owner yourusername; CREATE DATABASE postgres=# \q postgres $ exit yourusername $ psql ... yourusername=# .. this will create a postgres user with the same username as your regular login, a password, and the privileges to create more databases and users you might need to add the ubuntu postgres bin directory to your user's path, I dunno (I don't use ubuntu) you will undoubtedly want to modify the pg_hba.conf file to suit your connection and authentication requirements. see the online documentation for more on this, its in the introduction/tutorial section -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What packages I need to install to get Postgres working
Andre Lopes a écrit : > Hi, > > Thanks for your reply. > > I have installed postgresql 8.3, and now, what I need to do to get > things working? Install PGAdmin? Or there are other steps I need to do Daemons are active as soon as installation is completed. > to get things working? For example, how can I define the password for > access as sysadmin? No (IF bubuntu is alike Debian): 'postgres' user don't have a password, just: su - (to become root) su postgres at this step you're postgres user and can make whatever you want (even crap: remember Linux consider if you have the right to become a superuser, you're considered as a responsible user and have the right to do everything...) -- Baltimore, n.: Where the women wear turtleneck sweaters to hide their flea collars. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to check status of bug (42804: structure of query does not match error where using RETURN QUERY) ?
Hi, I've encountered a serious Postgres bug which apparently has a fix that hasn't been released in a patch. How or where can I look up its status? This is a serious bug- it basically renders RETURN QUERY useless in any environment where you don't have the option of dropping and re-creating a table, so I'm surprised to see that it's been outstanding for this long. 42804: structure of query does not match error where using RETURN QUERY Tom Lane wrote: > Michal Szymanski writes: > >> Exactly what modifications did you make? > > > We have added few new columns and we delete one column. > > [ experiments... ] Hmm, looks like RETURN QUERY isn't too bright about > dropped columns in the function result type. I'm not too sure about how > hard this is to fix, but in the meantime you will need to dump and > recreate/reload that particular table in order to get it to work. Is this a TODO? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-b...@...) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs Re: Re: 42804: structure of query does not match error where using RETURN QUERY by Tom Lane-2 Jun 02, 2009; 07:47am :: Rate this Message:- Use ratings to moderate (?) Reply | Reply to Author | Print | View Threaded | Show Only this Message Bruce Momjian writes: > Tom Lane wrote: >> [ experiments... ] Hmm, looks like RETURN QUERY isn't too bright about >> dropped columns in the function result type. I'm not too sure about how >> hard this is to fix, but in the meantime you will need to dump and >> recreate/reload that particular table in order to get it to work. > Is this a TODO? It's already there. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
