Re: [GENERAL] cast issue in WITH RECURION

> when i create a recursive query and try to add the distances i get a message: > ERROR:  recursive query "edges" column 3 has type numeric(7,3) in non-recursive term but type numeric overall. > My exercise is almost identical to the example in the docs: > WITH RECURSIVE

Re: [GENERAL] cast issue in WITH RECURION

> when i create a recursive query and try to add the distances i get a message: > ERROR:  recursive query "edges" column 3 has type numeric(7,3) in non-recursive term but type numeric overall. > My exercise is almost identical to the example in the docs: > WITH RECURSIVE

Re: [GENERAL] cast issue in WITH RECURION

> On 3 Aug 2017, at 20:22, k b wrote: > > when i create a recursive query and try to add the distances i get a message: > ERROR: recursive query "edges" column 3 has type numeric(7,3) in > non-recursive term but type numeric overall. > My exercise is almost identical to the

Re: [GENERAL] cast issue in WITH RECURION

Den tors 2017-08-03 skrev k b : Ämne: cast issue in WITH RECURION Till: pgsql-general@postgresql.org Datum: torsdag 3 augusti 2017 20:22 Hi. i use postgresql 9.6.3. I have made a small graph with nodes and edges. Each edge

[GENERAL] cast issue in WITH RECURION

Hi. i use postgresql 9.6.3. I have made a small graph with nodes and edges. Each edge has a distance numeric (7,3) attribute. when i create a recursive query and try to add the distances i get a message: ERROR: recursive query "edges" column 3 has type numeric(7,3) in non-recursive term but

[GENERAL] Cast hstore type to bytea (and later to hex possibly)

Hello, I would like to achieve something like the following: COPY (select 'a=>x, b=>y'::hstore::bytea) TO STDOUT; I have implemented an hstore value iterator that works with pqxx::result::field.c_str() (which has its own binary serialisation format) and I would like to reuse it to work with

Re: [GENERAL] Cast SRF returning record to a table type?

On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 4/18/15 12:47 AM, David G. Johnston wrote: If you could find a way to pass a value of type some_table into the function - instead of the name/text 'some_table‘ - you could possibly use polymorphic

Re: [GENERAL] Cast SRF returning record to a table type?

On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 4/18/15 12:47 AM, David G. Johnston wrote: If you could find a way to pass a value of type some_table into the function - instead of

Re: [GENERAL] Cast SRF returning record to a table type?

On Mon, Apr 20, 2015 at 9:40 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 4/18/15 12:47 AM, David G. Johnston wrote: If

Re: [GENERAL] Cast SRF returning record to a table type?

On Mon, Apr 20, 2015 at 11:40 AM, David G. Johnston david.g.johns...@gmail.com wrote: On Mon, Apr 20, 2015 at 7:57 AM, Merlin Moncure mmonc...@gmail.com wrote: On Sat, Apr 18, 2015 at 5:37 PM, Jim Nasby jim.na...@bluetreble.com wrote: On 4/18/15 12:47 AM, David G. Johnston wrote: If you

Re: [GENERAL] Cast SRF returning record to a table type?

On 4/18/15 12:47 AM, David G. Johnston wrote: If you could find a way to pass a value of type some_table into the function - instead of the name/text 'some_table‘ - you could possibly use polymorphic pseudotypes...just imagining here... Oh, I didn't think about that. Maybe I'll try it. What

Re: [GENERAL] Cast SRF returning record to a table type?

On 4/17/15 7:39 PM, David G. Johnston wrote: On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com mailto:jim.na...@bluetreble.com wrote: I'm working on a function that will return a set of test data, for unit testing database stuff. It does a few things, but ultimately

Re: [GENERAL] Cast SRF returning record to a table type?

On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com wrote: On 4/17/15 7:39 PM, David G. Johnston wrote: On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com mailto:jim.na...@bluetreble.com wrote: I'm working on a function that will return a set of test data, for

[GENERAL] Cast SRF returning record to a table type?

I'm working on a function that will return a set of test data, for unit testing database stuff. It does a few things, but ultimately returns SETOF record that's essentially: RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name; Because it's always going to return a real relation, I'd like to

Re: [GENERAL] Cast SRF returning record to a table type?

On Friday, April 17, 2015, Jim Nasby jim.na...@bluetreble.com wrote: I'm working on a function that will return a set of test data, for unit testing database stuff. It does a few things, but ultimately returns SETOF record that's essentially: RETURN QUERY EXECUTE 'SELECT * FROM ' ||

[GENERAL] cast hex to int in plpgsql

Hi, How can I realize the line? v_res := cast(x'v_tmp' as bigint); v_tmp is a text variable with hex digits. this works: v_res := cast(x'6de14a8b478ac' as bigint); I think its about quoting Janek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] cast hex to int in plpgsql

Hello this transformation is implemented inside parser - so you can not use a parameters there. You can use a dynamic SQL trick http://postgres.cz/wiki/PostgreSQL_SQL_Tricks_II#Conversion_between_hex_and_dec_numbers Regards Pavel 2013/12/31 Janek Sendrowski jane...@web.de Hi, How can I

Re: [GENERAL] cast hex to int in plpgsql

Thanks! That's what I'm searching for.   Janek   -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Cast user defined type to composite type

Hello! I need use user defined type and operate it with low-level functions on language C. In databasepreferred using composite type. Can i write more performance casting that vector3_cast_vector3c? // in C typedef struct { x, y, z double; } vector3; input, output and other

Re: [GENERAL] Cast double precision to integer check for overflow

On 2013-01-26, Gavan Schneider pg-...@snkmail.com wrote: On Saturday, January 26, 2013 at 08:13, Ian Pilcher wrote: I need to cast a double precision into an integer, and I want to check that the value will actually fit (modulo rounding). Coming from a C/Java background, this seems like

[GENERAL] Cast double precision to integer check for overflow

I need to cast a double precision into an integer, and I want to check that the value will actually fit (modulo rounding). Coming from a C/Java background, this seems like something that should be utterly trivial. In my searching, however, I can't seem to find any SQL equivalent of INT_MAX,

[GENERAL] Re: [GENERAL] Cast double precision to integer check for overflow

Just cast to integer. Decimal portion will be lost. Sent from my smartphone - Reply message - From: Ian Pilcher arequip...@gmail.com To: pgsql-general@postgresql.org Subject: [GENERAL] Cast double precision to integer check for overflow Date: Sat, Jan 26, 2013 3:13 pm I need to cast

Re: [GENERAL] Cast double precision to integer check for overflow

On 01/26/2013 05:06 PM, Alexander Gataric wrote: Just cast to integer. Decimal portion will be lost. That part I've got. :-) It's checking that the double precision value will actual fit within the range of the integer type (-2147483648 to +2147483647). I could certainly hard-code these

Re: [GENERAL] Cast double precision to integer check for overflow

On Saturday, January 26, 2013 at 08:13, Ian Pilcher wrote: I need to cast a double precision into an integer, and I want to check that the value will actually fit (modulo rounding). Coming from a C/Java background, this seems like something that should be utterly trivial. In my searching,

Re: [GENERAL] Cast double precision to integer check for overflow

On 01/26/2013 03:09 PM, Ian Pilcher wrote: On 01/26/2013 05:06 PM, Alexander Gataric wrote: Just cast to integer. Decimal portion will be lost. That part I've got. :-) It's checking that the double precision value will actual fit within the range of the integer type (-2147483648 to

Re: [GENERAL] Cast double precision to integer check for overflow

On 2013-01-26, Ian Pilcher arequip...@gmail.com wrote: I need to cast a double precision into an integer, and I want to check that the value will actually fit (modulo rounding). Coming from a C/Java background, this seems like something that should be utterly trivial. In my searching,

Re: [GENERAL] cast name to oid

Hi, On Wed, Aug 15, 2012 at 1:02 AM, Little, Douglas douglas.lit...@orbitz.com wrote: Can someone let me know the correct way to do this? This is failing where p.oid = cast(proname as regproc); NOTICE: found dba_work.pg_get_functiondef2 WARNING: sqlstate 42846 WARNING: sqlerrm

[GENERAL] cast name to oid

I got my function dump function to work. Enhancing to handle errors if the object doesn't exist. I want to add an exception block, to trap the object not found error. But when I changed the input parameter type from regproc to text, I was no longer getting matches. I am trying to explicitly

[GENERAL] Cast timestamptz to/from integer?

I work a lot with Unix times as integers, but would like to store them in Postgres as 'timestamp(0) with time zone' for convenience and readability. Unfortunately the syntax to translate between the two is a little cumbersome, so I'm looking at hiding it away behind a function - or a cast.

Re: [GENERAL] Cast timestamptz to/from integer?

I think You can use epoch there is an example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second'; Regards, Bartek 2012/4/3 Chris Angelico ros...@gmail.com I work a lot with Unix times as integers,

Re: [GENERAL] Cast timestamptz to/from integer?

On Tue, Apr 3, 2012 at 7:11 PM, Bartosz Dmytrak bdmyt...@gmail.com wrote: I think You can use epoch there is an example: http://www.postgresql.org/docs/9.1/static/functions-datetime.html SELECT TIMESTAMP WITH TIME ZONE 'epoch' + 982384720.12 * INTERVAL '1 second'; Yep, but when you do that

Re: [GENERAL] Cast timestamptz to/from integer?

There is a build in function which encapsulates that statement: SELECT to_timestamp (982384720); EXPLAIN ANALYZE shows: Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.003 rows=1 loops=1) so this looks cheap Regards, Bartek 2012/4/3 Chris Angelico ros...@gmail.com On Tue,

[GENERAL] cast type bytea to double precision

HI All, I have a database column which type is bytea. It contains floats converted as byte array (4 bytes per one float) and encoding is Escape. I would be able to get corresponding bytea string using substring function. My question is how can I convert bytea string to float inside a SQL

[GENERAL] cast list of oid

is it possible to cast a list of oids . i.e something like below. Or I need to write a procedure SELECT groname, grolist::regclass::textFROM pg_group; Regards Best Regard Eng. Salah Al Jubeh PalestinePolytechnic University College of Applied Science Computer Science P.O. Box 198

Re: [GENERAL] cast list of oid

Hello 2011/4/8 salah jubeh s_ju...@yahoo.com: is it possible to cast a list of  oids  . i.e something like below. Or I need to write a procedure SELECT groname, grolist::regclass::text FROM pg_group; what is list? Is it a array? you can use a unnest and array() postgres=# select

Re: [GENERAL] cast problem in Postgresql 9.0.1

On Monday, January 31, 2011 10:14:29 pm AI Rumman wrote: I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. I have a table testtab \d testtab id int, hours varchar When I execute the following: select sum(hours) from testtab I get cast error. Try: select sum(hours::int) from

Re: [GENERAL] cast problem in Postgresql 9.0.1

On 1 Feb 2011, at 7:14, AI Rumman wrote: I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. That's quite a big step up. You skipped 8.2, 8.3 and 8.4 - all major releases. My advise: Test very thoroughly for more differences in behaviour. One thing to start looking at right away is

Re: [GENERAL] cast problem in Postgresql 9.0.1

On Tue, 2011-02-01 at 12:14 +0600, AI Rumman wrote: I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. I have a table testtab \d testtab id int, hours varchar When I execute the following: select sum(hours) from testtab I get cast error. In 8.3, implicit casts were removed.

Re: [GENERAL] cast problem in Postgresql 9.0.1

Joshua D. Drake j...@commandprompt.com writes: On Tue, 2011-02-01 at 12:14 +0600, AI Rumman wrote: I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. I have a table testtab \d testtab id int, hours varchar When I execute the following: select sum(hours) from testtab I get cast

[GENERAL] cast problem in Postgresql 9.0.1

I migrated my DB from Postgresql 8.1 to Postgresql 9.0.1. I have a table testtab \d testtab id int, hours varchar When I execute the following: select sum(hours) from testtab I get cast error. Then, I created following IMPLICIT CAST functions in my DB = CREATE FUNCTION

[GENERAL] cast question: max double precision text double precision fails with out or range error

postgres=# select (((1.7976931348623157081e+308)::double precision)::text)::double precision; ERROR: 1.79769313486232e+308 is out of range for type double precision I can't think of too many practical use cases here, but I'm working on a pg driver and in my float data decoder functional tests, I

[GENERAL] Cast char to number

I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.com -- Sent via pgsql-general

Re: [GENERAL] Cast char to number

In response to Christine Penner christ...@ingenioussoftware.com: I have a character field I want to change to a number. The values in that field are all numbers that may or may not be padded with spaces or 0's. What is the best way to do that? Put the values in numeric fields to begin with

Re: [GENERAL] Cast char to number

I don't understand what you mean. This is a column in a table that is already a char and has numbers in it. I want it to be a number field not character. How can I change the data type of that column without loosing the data I have in it? Christine At 11:38 AM 24/02/2010, you wrote: In

Re: [GENERAL] Cast char to number

On 24/02/2010 19:53, Christine Penner wrote: I don't understand what you mean. This is a column in a table that is already a char and has numbers in it. I want it to be a number field not character. How can I change the data type of that column without loosing the data I have in it?

Re: [GENERAL] Cast char to number

On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop

Re: [GENERAL] Cast char to number

Raymond O'Donnell wrote: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column = CAST(trim(both ' 0' from old_column) as numeric) (iii) Drop the old column, as well as any constraints depending on it. (iv) Rename the new column

Re: [GENERAL] Cast char to number

On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate. (ii) update your_table set new_column

Re: [GENERAL] Cast char to number

In response to Raymond O'Donnell r...@iol.ie: On 24/02/2010 19:53, Christine Penner wrote: At 11:38 AM 24/02/2010, you wrote: In response to Christine Penner christ...@ingenioussoftware.com: I have a character field I want to change to a number. The values in that field are all

Re: [GENERAL] Cast char to number

In response to Joshua D. Drake j...@commandprompt.com: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or

Re: [GENERAL] Cast char to number

On Wed, Feb 24, 2010 at 1:27 PM, Joshua D. Drake j...@commandprompt.com wrote: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i)   Create a new column of

Re: [GENERAL] Cast char to number

On 24/02/10 20:27, Joshua D. Drake wrote: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: On 24/02/10 20:06, Raymond O'Donnell wrote: However, to address your immediate problem, you could try something like this: (i) Create a new column of type numeric or integer as appropriate.

Re: [GENERAL] Cast char to number

On Wed, 2010-02-24 at 13:35 -0700, Scott Marlowe wrote: You might want to clean up the values before doing this. That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table because char automatically pads.

Re: [GENERAL] Cast char to number

Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; That won't work in this case. char() can't be cast to int/numeric. Not only that it isn't possible to clean up the data in table

Re: [GENERAL] Cast char to number

On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joshua D. Drake j...@commandprompt.com writes: On Wed, 2010-02-24 at 20:22 +, Richard Huxton wrote: ALTER TABLE t ALTER COLUMN c TYPE integer USING c::integer; That won't work in this case. char() can't be cast to

Re: [GENERAL] Cast char to number

This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Thanks everyone for the help. Christine At 12:46 PM 24/02/2010, Scott Marlowe wrote: On Wed, Feb 24, 2010 at 1:43 PM, Tom Lane

Re: [GENERAL] Cast char to number

On 24/02/2010 21:42, Christine Penner wrote: This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Glad you got sorted. Bill's advice upthread is worth taking on board - if

Re: [GENERAL] Cast char to number

In response to Raymond O'Donnell r...@iol.ie: On 24/02/2010 21:42, Christine Penner wrote: This is what I did. set all blank columns to '0' because they were causing errors. alter table T alter column a type integer using a::integer That worked perfectly. Glad you got sorted.

Re: [GENERAL] Cast char to number

On 24/02/10 22:03, Bill Moran wrote: Then, a year later you find out that the serial number is really just a number, and you actually want to be able to do math on it because you can find out the year the part was designed by dividing by 1000 or something. You make the best decisions you can

[GENERAL] cast record to array in plpgsql

dear list, i'm trying to implement a general logging scheme with a plpgsql-trigger. The idea is, that after an update the trigger compares the elements of OLD and NEW and logs the changes made to the record columns. the trigger should be usable by any table, so the field names are not known

Re: [GENERAL] cast record to array in plpgsql

2009/11/17 Kurt wazke...@gmx.net: dear list, i'm trying to implement a general logging scheme with a plpgsql-trigger. The idea is, that after an update the trigger compares the elements of OLD and NEW and logs the changes made to the record columns. the trigger should be usable by any table,

Re: [GENERAL] cast numeric with scale and precision to numeric plain

Sim Zacks wrote: I'm using 8.2.4 Numeric with scale precision always shows the trailing zeros. Numeric plain only shows numbers after the decimal point that are being used. I would like to have the data in my table with scale and precision, but my views to be cast to numeric without any

Re: [GENERAL] cast numeric with scale and precision to numeric plain

Numeric with scale precision always shows the trailing zeros. Numeric plain only shows numbers after the decimal point that are being used. That statement is false: regression=# select 1234.000::numeric; numeric -- 1234.000 (1 row) I'm not sure offhand what is

Re: [GENERAL] cast numeric with scale and precision to numeric plain

On Tuesday 20 October 2009 3:39:22 am Sim Zacks wrote: Numeric with scale precision always shows the trailing zeros. Numeric plain only shows numbers after the decimal point that are being used. That statement is false: regression=# select 1234.000::numeric; numeric --

Re: [GENERAL] cast numeric with scale and precision to numeric plain

Sim Zacks s...@compulab.co.il writes: I'm not sure offhand what is the easiest way to suppress trailing zeroes, but casting to plain numeric is not the route to a solution. According to the documentation, numeric is stored without any leading or trailing zeros. That says *stored*; it doesn't

Re: [GENERAL] cast numeric with scale and precision to numeric plain

According to the documentation, numeric is stored without any leading or trailing zeros. That says *stored*; it doesn't say *displayed*. regards, tom lane If it displays them, it has to know they exist. That means it stores it somewhere. The part of the

Re: [GENERAL] cast numeric with scale and precision to numeric plain

2009/10/20 Sim Zacks s...@compulab.co.il: I guess that's a matter of interpretation. To me zeros after a decimal point without anything else after them are extra. From a mathematical perspective, they most certainly are not extra. 15.000 15.001 15.002 15.003 Each have three digits of

Re: [GENERAL] cast numeric with scale and precision to numeric plain

- Sim Zacks s...@compulab.co.il wrote: According to the documentation, numeric is stored without any leading or trailing zeros. That says *stored*; it doesn't say *displayed*. regards, tom lane If it displays them, it has to know they exist. That

[GENERAL] cast numeric with scale and precision to numeric plain

I'm using 8.2.4 Numeric with scale precision always shows the trailing zeros. Numeric plain only shows numbers after the decimal point that are being used. I would like to have the data in my table with scale and precision, but my views to be cast to numeric without any scale or precision.

Re: [GENERAL] cast numeric with scale and precision to numeric plain

Sim Zacks s...@compulab.co.il writes: Numeric with scale precision always shows the trailing zeros. Numeric plain only shows numbers after the decimal point that are being used. That statement is false: regression=# select 1234.000::numeric; numeric -- 1234.000 (1 row) I'm not

[GENERAL] cast needed - but where and why?

Hello list, I am getting the following error after upgrading from 7.4.6 to 8.3.6 and can't figure out what is wrong. Any help would be greatly appreciated. from our program: sqlcode=-400 errmsg='column event_ref_log_no is of type integer but expression is of type text' in line 4138. from

Re: [GENERAL] cast needed - but where and why?

Steve Clark scl...@netwolves.com writes: I am getting the following error after upgrading from 7.4.6 to 8.3.6 and can't figure out what is wrong. Any help would be greatly appreciated. 2009-04-02 10:45:10 EDT:srm2api:ERROR: column event_ref_log_no is of type integer but expression is of type

Re: [GENERAL] cast needed - but where and why?

Tom Lane wrote: Steve Clark scl...@netwolves.com writes: I am getting the following error after upgrading from 7.4.6 to 8.3.6 and can't figure out what is wrong. Any help would be greatly appreciated. 2009-04-02 10:45:10 EDT:srm2api:ERROR: column event_ref_log_no is of type integer but

[GENERAL] Cast for text-Integer missing in 8.3.5

I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

2009/1/13 Nykolyn, Andrew andrew.nyko...@ngc.com: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have many stored procedures that rely on those casts I'm

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

2009/1/13 Aleksander Kmetec aleksander.kme...@intera.si: Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

On Tue, Jan 13, 2009 at 8:23 AM, Nykolyn, Andrew andrew.nyko...@ngc.com wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I have

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

Scott Marlowe scott.marl...@gmail.com writes: On Tue, Jan 13, 2009 at 8:23 AM, Nykolyn, Andrew andrew.nyko...@ngc.com wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

2009/1/13 Aleksander Kmetec aleksander.kme...@intera.si: Nykolyn, Andrew wrote: I am trying to upgrade my Postgres server from 8.2.3 to 8.3.5 and have found that the cast text-integer and integer-text are missing. Is there a reason why they are not there and how can I get them back. I

Re: [GENERAL] Cast for text-Integer missing in 8.3.5

Nykolyn, Andrew andrew.nyko...@ngc.com writes: The above has all worked great to get me past the two issues described so far. Now I am having a problem with: function quote_literal is not unique. And you're going to have a few other problems after you get past that. You can't just insert

[GENERAL] CAST(integer_field AS character) truncates trailing zeros

I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? -- Thanks, Warren Bell -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to

Re: [GENERAL] CAST(integer_field AS character) truncates trailing zeros

On Thu, Jul 31, 2008 at 4:03 PM, Warren Bell [EMAIL PROTECTED] wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? Please supply the exact syntax that you're

Re: [GENERAL] CAST(integer_field AS character) truncates trailing zeros

On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell [EMAIL PROTECTED] wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character without loosing the trailing zeros? Here is what I get when I try: postgres=#

Re: [GENERAL] CAST(integer_field AS character) truncates trailing zeros

--- On Thu, 7/31/08, Warren Bell [EMAIL PROTECTED] wrote: From: Warren Bell [EMAIL PROTECTED] Subject: [GENERAL] CAST(integer_field AS character) truncates trailing zeros To: pgsql-general@postgresql.org Date: Thursday, July 31, 2008, 8:03 PM I am trying to cast an int to a character

Re: [GENERAL] CAST(integer_field AS character) truncates trailing zeros

On Thu, Jul 31, 2008 at 4:17 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Thu, Jul 31, 2008 at 1:03 PM, Warren Bell [EMAIL PROTECTED] wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not 1000. How do I cast from int to character

Re: [GENERAL] CAST(integer_field AS character) truncates trailing zeros

I guess it would help if I cast to the correct type. I was doing cast( cast( 1000 as integer ) as char ) instead of character varying, char(n) or text. Thanks, Warren Warren Bell wrote: I am trying to cast an int to a character. The int is the number 1000 it gets cast down to 1 and not

Re: [GENERAL] cast affects use of indexes ?

Alex Vinogradovs [EMAIL PROTECTED] writes: Is it possible to make it work properly without use of explicit casts ? Thanks! Read http://www.postgresql.org/docs/8.3/static/xindex.html particularly the discussion of cross-type index operators. regards, tom lane -- Sent

[GENERAL] cast affects use of indexes ?

Guys, I've got a table with an indexed column of a numeric user type (implemented in C). When I run a select on that table in a form of : select * from sometable where column = 89464; sequential scan is used... When I rewrite the query to use cast like this : select * from sometable where

Re: [GENERAL] cast problem 8.3.1

Charles Simard wrote: snip | | postgres error log: | ERROR: operator does not exist: text = integer | HINT: No operator matches the given name and argument type(s). You | might need to add explicit type casts. | STATEMENT: update T_MON_DEVICE_STATUS set device_ip = $1 :: inet | ,

[GENERAL] Cast character to boolean

I'm currently refactoring a database that somebody else designed. When the database was designed he used character columns with a length of 1 char to represent some values that really should have been represented as booleans. He used 'y' for true and 'n' for false. I want to cast these columns

Re: [GENERAL] Cast character to boolean

On Mar 18, 9:18 am, Gordon [EMAIL PROTECTED] wrote: I'm currently refactoring a database that somebody else designed. When the database was designed he used character columns with a length of 1 char to represent some values that really should have been represented as booleans. He used 'y' for

Re: [GENERAL] Cast character to boolean

On Mar 19, 5:52 pm, [EMAIL PROTECTED] wrote: On Mar 18, 9:18 am, Gordon [EMAIL PROTECTED] wrote: I'm currently refactoring a database that somebody else designed. When the database was designed he used character columns with a length of 1 char to represent some values that really should

Re: [GENERAL] Cast character to boolean

: Tuesday, March 18, 2008 10:18 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Cast character to boolean I'm currently refactoring a database that somebody else designed. When the database was designed he used character columns with a length of 1 char to represent some values

Re: [GENERAL] Cast character to boolean

Roberts, Jon wrote: A case statement will work just fine: select case when sub.col1 = 'y' then true else false end as col1_boolean, sub.col1 from (select cast('y' as varchar) as col1) sub Be aware of the danger here. What happens if col1 is NULL? -- Sent via pgsql-general mailing list

[GENERAL] cast problem 8.3.1

Hello List, I can't seem to figure out what is this code that worked on 7.4.x. I've added cast to everything but still get: postgres error log: ERROR: operator does not exist: text = integer HINT: No operator matches the given name and argument type(s). You might need to add explicit type

Re: [GENERAL] cast problem 8.3.1

Steve Clark escribió: Hello List, I can't seem to figure out what is this code that worked on 7.4.x. I've added cast to everything but still get: postgres error log: ERROR: operator does not exist: text = integer HINT: No operator matches the given name and argument type(s). You might

Re: [GENERAL] cast problem 8.3.1

snip | | postgres error log: | ERROR: operator does not exist: text = integer | HINT: No operator matches the given name and argument type(s). You | might need to add explicit type casts. | STATEMENT: update T_MON_DEVICE_STATUS set device_ip = $1 :: inet | , status = $2 :: integer ,

[GENERAL] cast time interval to seconds

Hi, I have two columns start_time stop_time declared as TIME. I'd like to compute the difference between the two times in seconds, all in db: SELECT (CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS)) % 86400; Unfortunately AS SECONDS causes parse error. Any hints? Thanks.

Re: [GENERAL] cast time interval to seconds

On Sep 10, 2007, at 10:44 , rihad wrote: SELECT (CAST(stop_time AS SECONDS) + 86400 - CAST(start_time AS SECONDS)) % 86400; seconds isn't a datatype. Try extract(epoch from (stop_time - start_time)) The manual is quite extensive:

  1   2   >