[SQL] ask syntax sql to get length of field
Here Question :I have table A with structure : Number Character(20), Description Character(50)I want to get length of field Number (20)What is syntax sql to get length of field ?thx Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates.
Re: [SQL] Field length ??
Hi, Louise, Louise Catherine wrote: > Could anyone explain, why the field length must be add by 4 : > result 1 : 10 + 4 =14 > result 2 : 5 + 4 = 9 I guess that it is because all variable length datatypes (and text types are such) internally contain a 4 bytes length field. http://www.postgresql.org/docs/8.1/interactive/xtypes.html might be a start if you want to read more on this topic. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Moving around in a SQL database
Hello Willem, if its not too much data you could do an select * from table an store the result in an array of rows. Then you can iterate over it. If this is too bloated then create a cursor with: declare (refer to http://www.postgresql.org/docs/8.1/interactive/sql-declare.html ). Then use fetch to get the desired row. After you are finished use CLOSE to close it. With kind regards Florian Reiser -- http://www.ra-bc.de RA Unternehmensberatung Führen durch präzise Daten "WillemF" <[EMAIL PROTECTED]> schrieb im Newsbeitrag news:[EMAIL PROTECTED] >I am a novice to SQL. Hope my naiivity does not show so clearly. After > consulting the Internet extensively and studying the two thick > reference books that I have, I cannot find an answer to my problem, > which I suspect has no immediate solution. I need to move through a > table and extract the records sequentially one by one and display the > contents on screen. The order of the records is never changed. I can > easily create an index and find a record with an instructuion such as > SELECT * FROM tablename WHILE record_id = 20 , or some similar way. > But if I need to progress to the next record this is an extremely > inefficient way to do it since one has to serach the whole database > just to get to the next record. It would be much easier to have a > pointer to each of the records in the table and use these pointers to > directly jump to the appropriate record. In the old days of DBase there > was an instruction such as GOTO recordnumber. That would set the > pointer to the appropriate record which could then be accessed. With > PgSQL being so much more sophisticated than DBase, I am convinced there > must be an elegant way of solving this problem. Kind regards. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] At time zone madness!
I've been trawling the net trying to find an answer to this, so sorry if this is a common problem - I have tried looking before posting!
My problem is that when trying to SELECT a timestamp using "AT TIME ZONE 'Europe/London", it's subtracting an hour instead of adding it from a column storing UTC dates.
The session time zone is set to UTC, and the field is a timestamp without time zone.
However, if I do the same thing on now() instead of the result of my query, it works!
More details below:
johngoss_cs=> set session time zone 'UTC';SET
=> select now(); now--- 2006-04-20 13:19:04.585175+00(1 row)
=> select now() at time zone 'Europe/London'; timezone 2006-04-20 14:19:12.535543(1 row)
At the time of running this the time was 14:10 in the UK (13:10 UTC)
=> select posttime from tbldiscussionreplies where rid = 300284; posttime 2006-04-20 13:10:51.160939(1 row)
Ok, so this is the raw posttime - which should always be UTC. Works fine.
=> select posttime at time zone 'UTC' from tbldiscussionreplies where rid = 300284; timezone--- 2006-04-20 13:10:51.160939+00(1 row)
Try getting it at UTC - again fine - the session time zone is set to UTC, so it doesn't change anything.
=> select posttime at time zone 'Europe/London' from tbldiscussionreplies where rid = 300284; timezone--- 2006-04-20 12:10:51.160939+00(1 row)
The problem!
For some reason this has subtracted an hour - making it two hours wrong!
The field is described as: posttime | timestamp without time zone | not null default timezone('utc'::text, now())
Any ideas?
Thanks!
John
Re: [SQL] Field length ??
Markus Schaber <[EMAIL PROTECTED]> writes: > Louise Catherine wrote: >> Could anyone explain, why the field length must be add by 4 : > I guess that it is because all variable length datatypes (and text types > are such) internally contain a 4 bytes length field. It's an ancient decision that no longer has much of anything to do with reality, but changing it would break more code than it's worth to have a slightly cleaner definition of atttypmod ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] ask syntax sql to get length of field
On Thu, Apr 20, 2006 at 00:37:08 -0700, Mariya Yuniarti <[EMAIL PROTECTED]> wrote: > Here Question : > > I have table A with structure : > > Number Character(20), >Description Character(50) > > I want to get length of field Number (20) > > What is syntax sql to get length of field ? Are you looking to get 20 or the length of a particular string in that column? If you are looking for the maximum length (20), that should be available in the catalogs. For the string length you can look in the string functions section of the document. I am not sure whether or not trailing spaces are counted in char columns. varchar makes more sense if you actually have variable length strings. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Field length ??
At 05:16 AM 4/20/06, Markus Schaber wrote: Hi, Louise, Louise Catherine wrote: > Could anyone explain, why the field length must be add by 4 : > result 1 : 10 + 4 =14 > result 2 : 5 + 4 = 9 I guess that it is because all variable length datatypes (and text types are such) internally contain a 4 bytes length field. Except that the original fields were neither variable length datatypes, not type text. create table test(] satu char(10), dua char(5) ); ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Field length ??
Hi, Frank, Frank Bax wrote: > Except that the original fields were neither variable length datatypes, > not type text. > create table test(] > satu char(10), > dua char(5) > ); char is a textual type (in opposite to numeric types), and they are handled as variable length datatypes internally (even if given a limit). See http://www.postgresql.org/docs/8.1/static/datatype-character.html HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Field length ??
Markus Schaber <[EMAIL PROTECTED]> writes: > Frank Bax wrote: >> Except that the original fields were neither variable length datatypes, >> not type text. > char is a textual type (in opposite to numeric types), and they are > handled as variable length datatypes internally (even if given a limit). char(n) *is* a variable-length type, not merely "handled as such", because the limit N is measured in characters not bytes. To support variable-length encodings such as UTF8 we have to treat it as variable length. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] At time zone madness!
"John Goss" <[EMAIL PROTECTED]> writes: > My problem is that when trying to SELECT a timestamp using "AT TIME ZONE > 'Europe/London", it's subtracting an hour instead of adding it from a column > storing UTC dates. I think you've got a misunderstanding about the behavior of AT TIME ZONE. It's really two distinct operations depending on whether the input is a timestamp with or without timezone. 1) If input is timestamp with timezone: rotate the time to what it would be locally in the specified timezone, and output that as timestamp without timezone. 2) If input is timestamp without timezone: interpret the given time as being local time in the specified timezone, and output as timestamp WITH timezone. Internally, that means rotating to UTC because timestamp with timezone is always stored as UTC internally. So in fact one case adds the UTC offset of the given timezone, and the other case subtracts it. I suspect that you should be storing your posttime column as timestamp with timezone, not without. In general, if a stored timestamp value is meant to represent an absolute time instant, timestamp with tz is the correct datatype for it. Timestamp without tz isn't very well-defined. If you wanted to stick with timestamp-without-tz as the column datatype, having the convention that it's always represented in UTC, then the correct way to derive the equivalent time in some other zone would be SELECT posttime AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London' The first AT TIME ZONE has the effect of specifying what the stored timestamp really means, and the second does the rotation to London time. regards, tom lane ---(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
