pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
text -> char 2+ messages / 2 participants [nested] [flat]
* text -> char @ 2000-02-12 23:57 Peter Stamfest <peter.stamfest@eunet.at> 0 siblings, 1 reply; 2+ messages in thread From: Peter Stamfest @ 2000-02-12 23:57 UTC (permalink / raw) To: pgsql-sql I want to extract portions of a TEXT column into a CHAR(8) column: > create table one (descr text); > create view two as select substr(descr, 2, 4) as d from one; > \d two View = two Query = SELECT "substr"("one"."descr", '2'::"int4", '4'::"int4") AS "d" FROM "one"; +-----------------------+------------------------------+-------+ | Field | Type | Length| +-----------------------+------------------------------+-------+ | d | text | var | +-----------------------+------------------------------+-------+ > create view three as select CAST(substr(descr, 2, 4) as char(8)) as d from one; > \d three View = three Query = SELECT "substr"("one"."descr", '2'::"int4", '4'::"int4") AS "d" FROM "one"; +-----------------------+------------------------------+-------+ | Field | Type | Length| +-----------------------+------------------------------+-------+ | d | text | var | +-----------------------+------------------------------+-------+ SELECT INTO behaves the same way. It seems impossible to cast a TEXT into a CHAR. The char() function works neither: > create view four as select char(substr(descr, 2, 4)) as d from one; ERROR: parser: parse error at or near "substr" Any ideas? This is 6.5.2 (RH Linux 6.1) Thanks in advance peter ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] text -> char @ 2000-02-26 21:27 Tom Lane <tgl@sss.pgh.pa.us> parent: Peter Stamfest <peter.stamfest@eunet.at> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2000-02-26 21:27 UTC (permalink / raw) To: Peter Stamfest <peter.stamfest@eunet.at>; +Cc: pgsql-sql Peter Stamfest <peter.stamfest@eunet.at> wrote a couple weeks ago: > create view three as select CAST(substr(descr, 2, 4) as char(8)) as > d from one; That should work, and as of today it does work in current sources: regression=# create view three as select CAST(substr(descr, 2, 4) as char(8)) regression-# as d from one; CREATE 278003 1 regression=# \d three View "three" Attribute | Type | Modifier -----------+---------+---------- d | char(8) | View definition: SELECT (substr(one.descr, 2, 4))::char(8) AS d FROM one; Releases before 7.0 had a tendency to just discard casts :-( in a lot of corner cases, and this is one of 'em. I've cleaned up the cast problems I know about, but there may be some left... > The char() function works neither: > create view four as select char(substr(descr, 2, 4)) as d from one; > ERROR: parser: parse error at or near "substr" That's not a bug. CHAR is an SQL reserved word for a type --- the parser is expecting a type length indicator, like "char(8)", when it sees CHAR(. While there is a function named "char", you can only get at it by double-quoting the name so it no longer looks like a keyword: select "char"(substr(descr, 2, 4)) as d from one; Also note that this converts to the internal single-byte char type, which is presumably not what you wanted anyway. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-02-26 21:27 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-02-12 23:57 text -> char Peter Stamfest <peter.stamfest@eunet.at> 2000-02-26 21:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox