[SQL] Question regarding multibyte.
Hi, I am new to postgres. We are using 8.2 release of postgres. Recently we converted our database to multibyte on our dev machine. we want to test the following. 1) How will I insert multibyte from our php? Do we need to use any special encoding? 2) I am using psql command line to get the data into a flat file and push to our data warehouse We use IBM redbrick warehouse. 3) If I have multibyte, I know the output from the psql will scatter to many lines. How can we avoid that. What is the sql command ? I need 1 record per row to supply to our warehouse. Can somebody help me? Regards skarthi _ Valentines Day -- Shop for gifts that spell L-O-V-E at MSN Shopping http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptnrdata=24095&tcode=wlmtagline ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question regarding multibyte.
Hi, I am new to postgres. I asked a question regarding multibyte display. I got only one response. Hence, I am re-iterating the question again to a larger audience. We are using 8.2 release of postgres. Recently we converted our database to multibyte on our dev machine. we want to test the following. 1) How will I insert multibyte from our php? Do we need to use any special encoding? 2) I am using psql command line to get the data into a flat file and push to our data warehouse We use IBM redbrick warehouse. 3) If I have multibyte, I know the output from the psql will scatter to many lines. How can we avoid that. What is the sql command ? I need 1 record per row to supply to our warehouse. Can somebody help me? Regards skarthi _ Valentines Day -- Shop for gifts that spell L-O-V-E at MSN Shopping http://shopping.msn.com/content/shp/?ctId=8323,ptnrid=37,ptnrdata=24095&tcode=wlmtagline ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Symbol lookup error
Hi Gurus, I hae installed postgres 8.2 recently and when I open the psql command line prompt and say \d . The psql abort abruptly with an error message given below. What is the cause and how to rectify it? Error Message = Welcome to psql 8.2.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit podcast=> \d channel psql: symbol lookup error: psql: undefined symbol: PQescapeStringConn Regards skarthi _ Check out all that glitters with the MSN Entertainment Guide to the Academy Awards® http://movies.msn.com/movies/oscars2007/?icid=ncoscartagline2 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] sql
try this: select entry_user_id, sum(decode(entry_user_id,'VC',1,0) as vc, sum(decode(entry_user_id,'VE',1,0) as ve, sum(decode(entry_user_id,'CV',1,0) as cv, sum(decode(entry_user_id,'SC',1,0) as SC from vigilance_master group where entry_user_id=78 group by entry_user_id From: "Shyju Narayanan" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: [SQL] sql Date: Fri, 2 Feb 2007 13:09:09 +0530 Hi All this is my table ; | ID|entry_user_id_int | category_id_chv | -- | 1|78|CV | -- | 2|78|VC | -- | 3|78|CV| -- | 4|78|CV| -- | 5|78|CV| -- | 6|78|CV| -- | 7|78|CV| -- | 8|78|CV| -- | 9|78|VE| -- | 10|78|CV| -- | 11|78|SC| -- WHEN "select entry_user_id_int, category_id_chv,count(category_id_chv) from vigilance_master group by category_id_chv,entry_user_id_int having" entry_user_id_int=78 result is : ID entry_user_id_int category_id_chv count 178 VC 1 278 VE 1 378 CV 8 478 SC 1 BUT I NEED THE RESULT AS entry_user_id_int COUNT(VC) COUNT(VE) COUNT(CV) COUNT(SC) TOTAL 781 1 8 1 11 _ Get in the mood for Valentine's Day. View photos, recipes and more on your Live.com page. http://www.live.com/?addTemplate=ValentinesDay&ocid=T001MSN30A0701 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] question on passing parameter in sql query
Hi, I don't want to compare with Oracle and postgres. But I have a situation. I am using psql command line tool supplied by postgres. In Oracle I can say select * from emp where emp_id = &1 Oracle will ask: Enter a value for 1: If I enter 10, then Oracle will get the empid=10 What is the equal command in postgres ? Regards skarthi _ Invite your Hotmail contacts to join your friends list with Windows Live Spaces http://clk.atdmt.com/MSN/go/msnnkwsp007001msn/direct/01/?href=http://spaces.live.com/spacesapi.aspx?wx_action=create&wx_url=/friends.aspx&mkt=en-us ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] How to analyse the indexes in postgres?
Hi, I am new to postgres. I need some kind of template script or advise on how to analyse the indexes. In our database, we do delete, insert, update tons of rows. Regards skarthi _ Refi Now: Rates near 39yr lows! $430,000 Mortgage for $1,399/mo - Calculate new payment http://www.lowermybills.com/lre/index.jsp?sourceid=lmb-9632-17727&moid=7581 ---(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
[SQL] system tables inquiry & db Link inquiry
Hi, We are using Postgres 8.1.0 Question No 1: = There are lots of system tables that are available in postgres. For example pg_tables will have all the information about the tables that are present in a given schema. pg_views will have all the information about the views for the given schema. I want to find all the sequences. What is the system tables that have the information about all the sequences? Question No 2: = I have 2 postgres instance located in two different servers. I want to create a DBlink (like in Oracle) between these 2. What are the steps involved to create this. Any examples? Please advise. Regards skarthi _ Win a Zunemake MSN® your homepage for your chance to win! http://homepage.msn.com/zune?icid=hmetagline ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] pg_dump error
Hi, I am using 8.2.1 on my dev server. When I do a pg_dump, I am getting an error message. pg_dump -U postgres podcast -t channel pg_dump: symbol lookup error: pg_dump: undefined symbol: PQescapeStringConn How can I resolved this? What may be the problem? Because of this, I am not able to dump anything. Regards skarthi _ Play Flexicon: the crossword game that feeds your brain. PLAY now for FREE. http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [ADMIN] pg_dump error
Hi Joshua, Thanks for your reply. No, I recently installed (fresh installation) from scratch. Regards skarthi From: "Joshua D. Drake" <[EMAIL PROTECTED]> To: Karthikeyan Sundaram <[EMAIL PROTECTED]> CC: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org Subject: Re: [ADMIN] pg_dump error Date: Wed, 28 Feb 2007 11:31:01 -0800 Karthikeyan Sundaram wrote: > Hi, > > I am using 8.2.1 on my dev server. > > When I do a pg_dump, I am getting an error message. > > pg_dump -U postgres podcast -t channel > > pg_dump: symbol lookup error: pg_dump: undefined symbol: PQescapeStringConn > > How can I resolved this? What may be the problem? > >Because of this, I am not able to dump anything. Sounds like you have two different versions of pg_dump on your box. Did you recently try to upgrade? Sincerely, Joshua D. Drake > > > Regards > skarthi > > _ > Play Flexicon: the crossword game that feeds your brain. PLAY now for > FREE. http://zone.msn.com/en/flexicon/default.htm?icid=flexicon_hmtagline > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate _ Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month. Intro*Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] pg_dump inquiry
Hi, I have to dump only 10 tables out of 100 tables. In the pg_dump utility given by postgres there is an option called -t followed by table name. In that option, if I give more than 1 table, it's not accepting. How can I get the dump in one stroke for all the 10 tables? Please advise. Regards skarthi _ Mortgage rates as low as 4.625% - Refinance $150,000 loan for $579 a month. Intro*Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h27f6&disc=y&vers=743&s=4056&p=5117 ---(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
[SQL] Running in single instance mode
Hi Everybody, We are using postgres 8.1.0. I want to do some maintenance work. Hence, I want to run postgres in single user mode so that external people won't be able to access the database. How can I run the postgres in single user mode?. Any idea? Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] create view with check option
Hi Everybody, I have 2 versions of postgres 8.1.0 is my production version and 8.2.1 is my development version. I am trying to create a view in my development version (8.2.3) create view chnl_vw as select * from channel with check option; I am getting an error message: [Error] Script lines: 1-1 -- ERROR: WITH CHECK OPTION is not implemented Line: 1 what does this mean? I looked at the 8.2.1 manual and found the create view has check option. But it says before 8.2 those options are unsupported. How can I make this command to work. Regards skarthi _ 5.5%* 30 year fixed mortgage rate. Good credit refinance. Up to 5 free quotes - *Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2a5d&s=4056&p=5117&disc=y&vers=910 ---(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] [ADMIN] create view with check option
Hi everybody, I have implemented in a different way as advised in the manual. I thought this will be useful for everbody. We don't have check option in the view. Instead we can create a rule to make the view as insertable, updatable or delete. Here is the script. create table test_tbl (a int4, b int4, c varchar(30)); create or replace view test_vw as select * from test_tbl; create or replace rule test_rule_ins as on insert to test_vw do instead insert into test_tbl values (new.a, new.b, new.c); insert into test_vw (a, b) values (1,2); insert into test_vw (a, b) values (3,4); create or replace rule test_rule_upd as on update to test_vw do instead update test_tbl set a=new.a, b=new.b, c=new.c where a=new.a; update test_vw set c='good' where a=1; select * from test_vw; regards skarthi From: "Karthikeyan Sundaram" <[EMAIL PROTECTED]> To: pgsql-admin@postgresql.org, pgsql-sql@postgresql.org Subject: [ADMIN] create view with check option Date: Sun, 18 Mar 2007 22:38:05 -0700 Hi Everybody, I have 2 versions of postgres 8.1.0 is my production version and 8.2.1 is my development version. I am trying to create a view in my development version (8.2.3) create view chnl_vw as select * from channel with check option; I am getting an error message: [Error] Script lines: 1-1 -- ERROR: WITH CHECK OPTION is not implemented Line: 1 what does this mean? I looked at the 8.2.1 manual and found the create view has check option. But it says before 8.2 those options are unsupported. How can I make this command to work. Regards skarthi _ 5.5%* 30 year fixed mortgage rate. Good credit refinance. Up to 5 free quotes - *Terms https://www2.nextag.com/goto.jsp?product=10035&url=%2fst.jsp&tm=y&search=mortgage_text_links_88_h2a5d&s=4056&p=5117&disc=y&vers=910 ---(end of broadcast)--- TIP 6: explain analyze is your friend _ i'm making a difference. Make every IM count for the cause of your choice. Join Now. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=hmtagline ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] ERROR: invalid byte sequence for encoding "UTF8": 0x92
Hi, I am using postgres 8.2.3. I have recently converted my database from sql-ascii to UTF8. I have a portal which calls a perl program to insert the data into the database. While inserting, I am getting an error message DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding "UTF8": 0x92 [for Statement "INSERT INTO longdescs (bug_id, who, bug_when, thetext, isprivate) How can I set the client encoding in perl. Can somebody help me? Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] growth of the database
Hi, Our database is growing fast. I want to create a cronjob that should tell me what is the current size of the database on each day. How can I find this from the database? Is there any pre-written scripts written by somebody to share? Regards skarthi _ Live Search Maps find all the local information you need, right when you need it. http://maps.live.com/?icid=hmtag2&FORM=MGAC01 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Monitor what command is executing at the backend
Hi everybody, Is there a way to see from the log files on what sql statement is currently by which user? In other words, I want to monitor the DB activity. How can I find it? Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0050001411mrt/direct/01/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Monitor what command is executing at the backend
George, How will I enable command string to see the commands? Regards skarthi From: "George Pavlov" <[EMAIL PROTECTED]> To: "Karthikeyan Sundaram" <[EMAIL PROTECTED]>,, Subject: Re: [SQL] Monitor what command is executing at the backend Date: Wed, 21 Mar 2007 14:56:50 -0700 >Is there a way to see from the log files on what sql statement is > currently by which user? In other words, I want to monitor > the DB activity. for a current snapshot you don't need the logs, try: select * from pg_stat_activity; (command string needs to be enabled for your database.) ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate _ Its tax season, make sure to follow these few simple tips http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=HMMartagline ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] function return array
Hi, I am using Postgres 8.1.0. I have a requirement. I will create a function accepting few parameters. This will check into various tables and give back an array of values. Is it possible to return an array from the function? Please guide me. Regards skarth _ Your friends are close to you. Keep them that way. http://spaces.live.com/signup.aspx
[SQL] plpgsql function return array
Hi, I am using Postgres 8.1.0. I have a requirement. I will create a function accepting few parameters. This will check into various tables and give back an array of values. I want to use the pgpsql block. I know that we can create using language sql. Is it possible to return an array from the function? Please guide me. Regardsskarthi _ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07
[SQL] plpgsql function question
Hi, I am having a requirement here. 1) I need to write a plpgsql function where it takes the input parameter of a structure of a table. 2) The table has 15 columns 3) It does lots of validation based on the parameter and finally returns an integer as output parameters Q) How will I passe the table structure as as parameter 2) Do I need to create a type? Please help me. Regards skarthi _ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07
Re: [SQL] plpgsql function question
Hi,
I guess there is some misunderstanding from my question
Let me elaborate more clearly.
My Table is
Create table a (
i int,
j varchar(20),
k date);
Create or replace function a_func (in p_i int, in p_j varchar, in p_k date)
returns int as
$$
- do the validation
return 1;
$$
language 'plpgsql';
This works fine
What I want is something like this
create or replace functinon a_func (in a%rowtype) returns int as
$$
do the validation
$$
language 'plpgsql';
execute a_func(1, 'good','04/02/2007');
> Date: Tue, 3 Apr 2007 20:18:43 +0200> From: [EMAIL PROTECTED]> To:
> pgsql-sql@postgresql.org> Subject: Re: [SQL] plpgsql function question> >
> Karthikeyan Sundaram <[EMAIL PROTECTED]> schrieb:> > > > > Hi,> > > > I am
> having a requirement here.> > > > 1) I need to write a plpgsql function
> where it takes the input> > parameter of a structure of a table.> > Because?
> To build this table? You can pass an ascii-text with the> table-definition
> and EXECUTE this string.> > > > 2) The table has 15 columns> > Okay. And the
> problem is?> > > > 3) It does lots of validation based on the parameter and
> finally> > returns an integer as output parameters> > Okay. create function
> ... returns int as $$ ... return 1; end; $$> language plpgsql;> > > > > > Q)
> How will I passe the table structure as as parameter> > As i said, for
> instance as simple text and EXECUTE this.> > > > 2) Do I need to create a
> type?> > No.> > > Andreas> -- > Really, I'm not out to destroy Microsoft.
> That will just be a completely> unintentional side effect. (Linus Torvalds)>
> "If I was god, I would recompile penguin with --enable-fly." (unknow)>
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°> >
> ---(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'm making a difference. Make every IM count for the cause of your choice. Join
Now.
http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=wlmailtagline
[SQL] exception handling in postgres plpgsql
Hi, I am having a function like this create or replace function audio_format_func (in p_bitrate audio_format.audio_bitrate%TYPE,in p_sampling_rate audio_format.sampling_rate%type,in p_bit_per_sample audio_format.bit_per_sample%type,in p_audio_codec audio_format.audio_codec%type,in p_mimetype audio_format.mimetype%type, in p_mono_stero audio_format.number_of_channel%type) returns int as$$DECLARE p_audio_id audio_format.audio_id%type;begin select audio_id into a from audio_format where audio_bitrate = p_bitrate and sampling_rate = p_sampling_rate and mimetype = p_mimetype and number_of_channel = p_mono_stero and audio_code = p_audio_codec; return 1; exception when NO_DATA_FOUND then return 100;end;$$language 'plpgsql'; When I compile, I am getting an error message ERROR: unrecognized exception condition "no_data_found"CONTEXT: compile of PL/pgSQL function "audio_format_func" near line 15 How will I handle exceptions in postgres? Please advise. Regards skarthi _ i'm making a difference. Make every IM count for the cause of your choice. Join Now. http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=wlmailtagline
[SQL] rowcount function in postgres???
Hi,
I am using 8.1.0 postgres and trying to write a plpgsql block. In that I am
inserting a row. I want to check to see if the row has been inserted or not.
In oracle we can say like this
begin
insert into table_a values (1);
if sql%rowcount > 0
then
dbms.output.put_line('rows inserted');
else
dbms.output.put_line('rows not inserted');
end if;
end;
Is there something equal to sql%rowcount in postgres? Please help.
Regards
skarthi
_
It’s tax season, make sure to follow these few simple tips
http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=WLMartagline
[SQL] Question on pgpsql function
Hi Everybody, I am using Postgres 8.1.0 and I have a requirement. I have a table create table weekly_tbl (id int, week_flag bit(7) not null default '111'); I want to create a function like this create function week_func (int) returns weekly_tbl as $$ select id, substr(week_flag,1,1) as monday_flag, substr(week_flag,2,1) as tuesday_flag, substr(week_flag,3,1) as wednesday_flag, substr(week_flag,4,1) as thursday_flag, substr(week_flag,5,1) as friday_flag, substr(week_flag,6,1) as saturday_flag, substr(week_flag,7,1) as sunday_flag from weekly_tbl where id=$1; $$ language SQL; I am getting an error message ERROR: function substr(bit, integer, integer) does not existHINT: No function matches the given name and argument types. You may need to add explicit type casts.CONTEXT: SQL function "week_func" I know I can do this in view. But for a purpose, I don't want to do it in view. Can somebody help me? Regards skarthi _ It’s tax season, make sure to follow these few simple tips http://articles.moneycentral.msn.com/Taxes/PreparationTips/PreparationTips.aspx?icid=WLMartagline
[SQL] Another question in functions
Hi team,
I have a requirement like this.
create table valid_lovs (code_id int not null,lov_value int not null
,description varchar(256),status bit(1) not null default '1',constraint lov_pk
primary key (code_id,lov_value));
insert into valid_lovs (code_id,lov_value,description) values (1,1,'1000
downloads');insert into valid_lovs (code_id,lov_value,description) values
(1,2,'2000 downloads');insert into valid_lovs (code_id,lov_value,description)
values (10,1,'US Dollar');insert into valid_lovs
(code_id,lov_value,description) values (10,2,'Singapore dollar');insert into
valid_lovs (code_id,lov_value,description) values (20,1,'Audio');insert into
valid_lovs (code_id,lov_value,description) values (20,2,'Video');insert into
valid_lovs (code_id,lov_value,description) values (20,3,'Overlay');
insert into valid_lovs (code_id, lov_value,description) values
(1000,1,'IMPRESSION_LOV');insert into valid_lovs (code_id,
lov_value,description) values (1000,10,'CURRENCY_LOV');insert into valid_lovs
(code_id, lov_value,description) values (1000,20,'MEDIA_FORMAT');
I need to write 2 functions.
1) Find_LOV. In this function I will pass only a text message but should return
an array.
create or replace function find_lov_func(in p_1 anyelement, out p_2 anyarray)
as$$ select array[x.code_id, x.lov_value] from valid_lovs x, valid_lovs y
where y.description = $1 and x.code_id = y.lov_value;$$language sql;
select find_lov_func('CURRENCY_LOV'::text);
I should get an output of
{10,1}
{10,2}
instead I am getting
ERROR: return type mismatch in function declared to return text[]DETAIL:
Actual return type is integer[].CONTEXT: SQL function "find_lov_func" during
startup
Q) How will I resolve this. I need to get array of integer only.
2) get_lov function: In this function, I will pass a text field and I should
get an integer and the text as output
for example
create or replace function get_lov_func(in p_1 varchar) returns setof
valid_lovs as$$ select x.lov_value, x.description from valid_lovs x,
valid_lovs y where y.description = $1 and x.code_id =
y.lov_value;$$language sql;
ERROR: return type mismatch in function declared to return valid_lovsDETAIL:
Final SELECT returns character varying instead of integer at column 2.CONTEXT:
SQL function "get_lov_func"
Can somebody help me in this?
Regards
skarthi
_
Take a break and play crossword puzzles - FREE!
http://games.msn.com/en/flexicon/default.htm?icid=flexicon_
wlmemailtaglinemarch07
[SQL] Urgent help in bit_string data type
Hi Gurus,
I have a table with datatype as bitstrings
create table test_a (b bit(3));
insert into test_a values ('111');
This will convert a bit to a number
==
select to_number((substring(b,1,1)::int),9)+3 from test_a;
How will I convert a Number to a bit
--
insert into test_a values (to_char(1,'9'));
ERROR: column "b" is of type bit but expression is of type textHINT: You will
need to rewrite or cast the expression.
Please help me
regards
skarthi
_
i'm making a difference. Make every IM count for the cause of your choice. Join
Now.
http://clk.atdmt.com/MSN/go/msnnkwme008001msn/direct/01/?href=http://im.live.com/messenger/im/home/?source=wlmailtagline
Re: [ADMIN] [SQL] Urgent help in bit_string data type
Thanks Joe, Here is my next question. create table test_a (b bit(3)); create view test_vw (b1, b2, b3)as select to_number(substring(b,1,1)::int,'9') as b1,to_number(substring(b,2,1)::int,'9') as b2,to_number(substring(b,3,1)::int,'9') as b3 from test_a;create or replace rule test_a_ins as on insert to test_vwdo insteadinsert into test_a (b) values (COALESCE(new.b1::bit,'1')||COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit); ERROR: cannot cast type numeric to bit How will I resolve this? Regards skarthi > Date: Wed, 11 Apr 2007 17:44:01 -0400> From: [EMAIL PROTECTED]> Subject: Re: > [ADMIN] [SQL] Urgent help in bit_string data type> To: [EMAIL PROTECTED]> CC: > pgsql-admin@postgresql.org; pgsql-sql@postgresql.org> > Hi skarthi,> > On > Wed, 2007-04-11 at 13:30 -0700, Karthikeyan Sundaram wrote:> > insert into > test_a values (to_char(1,'9'));> > > > ERROR: column "b" is of type bit but > expression is of type> > text> > HINT: You will need to rewrite or cast the > expression.> > As suggested by the error, you should use a cast, e.g.,> > > insert into test_a values 9::bit(3);> > This will result in binary '001' > being inserted because you need 4 bits> to represent decimal 9.> > Joe> > > > ---(end of broadcast)---> TIP > 6: explain analyze is your friend _ Take a break and play crossword puzzles - FREE! http://games.msn.com/en/flexicon/default.htm?icid=flexicon_ wlmemailtaglinemarch07
Re: [ADMIN] [SQL] Urgent help in bit_string data type
Joe, The reason why I am asking is, we are building an interface layer where all our users will have a view. They shouldn't know anything about how and where the data is stored in the table. They can be seen only by the portal which will use view. That's the reason. Regards skarthi> Date: Wed, 11 Apr 2007 19:00:23 -0400> From: [EMAIL PROTECTED]> Subject: Re: [ADMIN] [SQL] Urgent help in bit_string data type> To: [EMAIL PROTECTED]> CC: pgsql-admin@postgresql.org; pgsql-sql@postgresql.org> > Hi skarthi,> > On Wed, 2007-04-11 at 15:01 -0700, Karthikeyan Sundaram wrote:> > create table test_a (b bit(3));> > > > create view test_vw (b1, b2, b3)> > as select > > to_number(substring(b,1,1)::int,'9') as b1,> > to_number(substring(b,2,1)::int,'9') as b2,> > to_number(substring(b,3,1)::int,'9') as b3 from test_a;> > > > create or replace rule test_a_ins as on insert to test_vw> > do instead> > insert into test_a (b) values (COALESCE(new.b1::bit,'1')||> > COALESCE(new.b2::bit,'0')||COALESCE(new.b3::bit,'0')::bit);> > > > ERROR: cannot cast type numeric to bit> > > > How will I resolve this?> > *My* question is why are you doing such convoluted conversions, from bit> string to text, then to int, etc.? It seems to me like you want to> manipulate bits and if that's the case, you should be using the bit> string operators, as someone pointed out a couple of days ago. In case> you haven't looked at them, please see:> > http://www.postgresql.org/docs/8.2/static/functions-bitstring.html> > Joe> > > ---(end of broadcast)---> TIP 6: explain analyze is your friend _ Live Search Maps – find all the local information you need, right when you need it. http://maps.live.com/?icid=wlmtag2&FORM=MGAC01
[SQL] question on plpgsql block
Hi Gurus, I tried a plpgsql block from the php. example a='begin insert into table a values (); insert into table b values () insert into table c values (...) select into p_var ... from table where condtion update a set column = where condition; commit; end;'; I works fine when I call this block from php, whereas, I tried to copy the same value from the command line prompt or PGadmin GUI utility. Then it says "ERROR: current transaction is aborted, commands ignored until end of transaction block" Why? Did we need to do any special step up to execute a block or blocks won't execue from command line mode? Please advise. Regards skarthi _ Your friends are close to you. Keep them that way. http://spaces.live.com/signup.aspx
[SQL] setting up a mirroring or replication database
Hi Team, We are using Postgres 8.1.0 and in the plans to migrate to 8.2.3. Ours is a OLTP application. Publishers, advertisers and consumers use our system world wide. Right now it's not a very big database. But we are expanding our operations to Europe and US where we are expecting a moster growth. I want to setup a mirroring database or replications database so that if one database crash, the load balancing database should take care or it. Not only that, if I am upgrading one database, the other should take the load. I have never set this kind of replication or mirroring the database before. What are the steps and procedures to do this kind of setup. Please advise. Regards skarthi _ Live Search Maps – find all the local information you need, right when you need it. http://maps.live.com/?icid=wlmtag2&FORM=MGAC01
[SQL] quesion on functions calling from php
Hi, I have written a plpgsql function media_format_func(p_in overlay_format_type) which returns int $sql='select * from media_format_func((200,400,'640*481','jpg')::overlay_format_type)'; $result = $this->objDB->query($sql); print_r($result,1); I should get a value as 10, instead I am getting the Resource ID as 128 which is wrong I guess I am getting the output as an object and I am not getting the expected resul that I should get. What may be the problem? I am using PEAR object module for the db connections. Please advise. Regards skarthi _ Get a FREE Web site, company branded e-mail and more from Microsoft Office Live! http://clk.atdmt.com/MRT/go/mcrssaub0540002919mrt/direct/01/
[SQL] pguuid for windows
Hi team, Again the same old question. I have a Linux version of postgres where I installed the tsearch2 and the pguuid (from the pguuid.tar.gz from the gborg project). Now, I want to install the same postgres 8.2.3 into windows version, when I installed, I installed the contrib version where I found the tsearch2. Now, I need to install the pguuid for windows, I don't see the windows version of pguuid in the gborg. Can you body guide me, where to download and how to install the pguuid in windows, My project heavily use the pguuid alphanumeric character as the primary key. Regards skarthi _ Make every IM count. Download Windows Live Messenger and join the i’m Initiative now. It’s free. http://im.live.com/messenger/im/home/?source=TAGWL_June07
