SEO

Is it possible to update a timestamp with Postgresql?


I've looked around with google and it looks as though this should work:


exec sql begin declare section;
char api[18];
char expiration_time[80];
exec sql end declare section;


memcpy(api, ap->api, sizeof(api));
sprintf(expiration_time, "%s + \"interval\"('0.01sec')",
ap->expiration_time);


puts(expiration_time);


exec sql update accesspoint
set sequence_number = expiration_time = :expiration_time,
where api = :api;


However, I just get this:
2005-08-02 10:37:41.198479 + "interval"('0.01sec')
'ERROR: invalid input syntax for type timestamp: "2005-08-02 10:37:4

-Nigel


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

I have a simple query:

select true,78,'Here is a value' as stringfield, testname from tbltest;

it returns:
true as bool
78 as int4
and the string 'Here is a value' as Unknown

Why is it that all the values besides the string come back with the
correct type?
what is a simple string being returned as unknown instead of varchar or
text?


Thanks,

Tony

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

I have a Red Hat EL 4 server and they only ship 7.x with it, but I want
to run 8.x on it.
The server is running in 64bit mode and was wondering if there is a RPM
available? (I could only find 32bit ones on the Postgresql web page)

Thanks,

Tony

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Tony Caduto wrote:
> I have a simple query:
>
> select true,78,'Here is a value' as stringfield, testname from tbltest;
>
> it returns:
> true as bool
> 78 as int4
> and the string 'Here is a value' as Unknown
>
> Why is it that all the values besides the string come back with the
> correct type?
> what is a simple string being returned as unknown instead of varchar or
> text?

Because it doesn't know what you want it as For example in 8.0.3:


template1=# select true,78,'Here is a value' as stringfield;
bool | ?column? | stringfield
------+----------+-----------------
t | 78 | Here is a value
(1 row)

Or

template1=# select true,78::int4,'Here is a value'::text as stringfield;
bool | int4 | stringfield
------+------+-----------------
t | 78 | Here is a value
(1 row)


Sincerely,

Joshua D. Drake


>
>
> Thanks,
>
> Tony
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Tony Caduto wrote:
> I have a Red Hat EL 4 server and they only ship 7.x with it, but I want
> to run 8.x on it.
> The server is running in 64bit mode and was wondering if there is a RPM
> available? (I could only find 32bit ones on the Postgresql web page)

Just download the source RPM and:

rpmbuild -bb --target=i686 src.rpm

Sincerely,

Joshua D. Drake


>
> Thanks,
>
> Tony
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hi,
I don't think I was clear enough.

I know about using the AS keyword, that is not the problem.
The query in issue is:

select true,78,'Here is a value' as stringfield, testname from tbltest

Is returning the string as TYPE Unknown, not the column name. The
column name comes back as stringfield because of the AS keyword.

You can see the type in tools such as PG Admin etc

Thanks,

Tony

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hi Joshua,

Forgive me but I am not normally a redhat user (normally Gentoo)

So it will build a 64bit binary by default, there is no editing of the
spec file or anything else required?

Thanks,

Tony



>
> Just download the source RPM and:
>
> rpmbuild -bb --target=i686 src.rpm
>
> Sincerely,
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tony Caduto wrote:
> Hi Joshua,
>
> Forgive me but I am not normally a redhat user (normally Gentoo)
>
> So it will build a 64bit binary by default, there is no editing of the
> spec file or anything else required?

Oh if you are using 64bit then just:

rpmbuild -bb --target=x86_64 src.rpm

You may need to add some rpms in advance such as:

openssl-devel
rpm-build

But once your depends are resolved yes you should be good to go.

Sincerely,

Joshua D. Drake


>
> Thanks,
>
> Tony
>
>
>
>>
>> Just download the source RPM and:
>>
>> rpmbuild -bb --target=i686 src.rpm
>>
>> Sincerely,
>>


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Mon, Aug 08, 2005 at 03:05:13PM -0500, Tony Caduto wrote:
> Hi,
> I don't think I was clear enough.
>
> I know about using the AS keyword, that is not the problem.
> The query in issue is:
>
> select true,78,'Here is a value' as stringfield, testname from tbltest
>
> Is returning the string as TYPE Unknown, not the column name. The
> column name comes back as stringfield because of the AS keyword.

What about the query:

select 't','78','Here is a value' as stringfield, testname from tbltest

This gives you three Unknown fields. PostgreSQL can't know that the
first is to be a bool or that the second is to be an int until you
actually give it a type. Usually via casting or by inserting into a
table/

Single quotes denote an untyped constant, not a string.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFC98FeIB7bNG8LQkwRAlihAKCGNROirl6Nn5kR3pLj8q M1B99C7QCfcUok
UxO04L4j3PMKTS96z3a56kg=
=Wb61
-----END PGP SIGNATURE-----

Thanks for your help Joshua.

I will give it a try.

Tony

>> o it will build a 64bit binary by default, there is no editing of the
>> spec file or anything else required?
>
>
> Oh if you are using 64bit then just:
>
> rpmbuild -bb --target=x86_64 src.rpm
>
> You may need to add some rpms in advance such as:
>
> openssl-devel
> rpm-build
>
>
>


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend