Обсуждение: insert a -1 into a bigint ?
Hi All: I have this table: # \d seg_id_tmp7 Table "public.seg_id_tmp7" Column | Type | Modifiers -----------------+-----------------------------+----------- customer_srcid | bigint | show_srcid | bigint | show_name | character varying | season_srcid | bigint | season_name | character varying | episode_srcid | bigint | episode_name | character varying | segment_type_id | bigint | segment_type | character varying | segment_srcid | bigint | segment_name | character varying | create_dt | timestamp without time zone | I want to insert some data along with a -1 for two of the bigint columns. like this: insert into seg_id_tmp7 ( customer_srcid, show_srcid, show_name, season_srcid, season_name, episode_srcid, episode_name, segment_type_id, segment_type, segment_srcid, segment_name, create_dt ) select t8.customer_srcid, t8.show_srcid, t8.show_name, t8.season_srcid, t8.season_name, t8.episode_srcid, t8.episode_name -1::bigint , 'none', -1::bigint , 'none', (select start_dt from load_start_date) from seg_id_tmp8 t8 ; I've also tried without the ::bigint cast on the above query. I always get this error: sql:load_sl_cd_segment_dim.sql:237: ERROR: operator does not exist: character varying - bigint LINE 22: -1::bigint , ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Thanks in advance for any help, advice, etc.. /Kevin
I forgot to mention - I'm running version 8.3.1 On May 20, 2008, at 12:14 PM, kevin kempter wrote: > Hi All: > > > I have this table: > > # \d seg_id_tmp7 > Table "public.seg_id_tmp7" > Column | Type | Modifiers > -----------------+-----------------------------+----------- > customer_srcid | bigint | > show_srcid | bigint | > show_name | character varying | > season_srcid | bigint | > season_name | character varying | > episode_srcid | bigint | > episode_name | character varying | > segment_type_id | bigint | > segment_type | character varying | > segment_srcid | bigint | > segment_name | character varying | > create_dt | timestamp without time zone | > > > > I want to insert some data along with a -1 for two of the bigint > columns. like this: > > insert into seg_id_tmp7 > ( customer_srcid, > show_srcid, > show_name, > season_srcid, > season_name, > episode_srcid, > episode_name, > segment_type_id, > segment_type, > segment_srcid, > segment_name, > create_dt ) > select > t8.customer_srcid, > t8.show_srcid, > t8.show_name, > t8.season_srcid, > t8.season_name, > t8.episode_srcid, > t8.episode_name > -1::bigint , > 'none', > -1::bigint , > 'none', > (select start_dt from load_start_date) > from > seg_id_tmp8 t8 > ; > > > > I've also tried without the ::bigint cast on the above query. > > > I always get this error: > > sql:load_sl_cd_segment_dim.sql:237: ERROR: operator does not exist: > character varying - bigint > LINE 22: -1::bigint , > ^ > HINT: No operator matches the given name and argument type(s). You > might need to add explicit type casts. > > > > > > Thanks in advance for any help, advice, etc.. > > > /Kevin > > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
kevin kempter wrote: > Hi All: > > I want to insert some data along with a -1 for two of the bigint > columns. like this: > > insert into seg_id_tmp7 > ( customer_srcid, > show_srcid, > show_name, > season_srcid, > season_name, > episode_srcid, > episode_name, > segment_type_id, > segment_type, > segment_srcid, > segment_name, > create_dt ) > select > t8.customer_srcid, > t8.show_srcid, > t8.show_name, > t8.season_srcid, > t8.season_name, > t8.episode_srcid, > t8.episode_name > -1::bigint , > 'none', > -1::bigint , > 'none', > (select start_dt from load_start_date) > from > seg_id_tmp8 t8 > ; > > > > I've also tried without the ::bigint cast on the above query. > > > I always get this error: > > sql:load_sl_cd_segment_dim.sql:237: ERROR: operator does not exist: > character varying - bigint Try a comma before the -1 You are trying to enter - "t8.episode_name -1::bigint" into the column hence - "character varying - bigint" does not work -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
wow, good catch. Thanks for taking the time to look. On May 20, 2008, at 1:22 PM, Shane Ambler wrote: > kevin kempter wrote: >> Hi All: > >> I want to insert some data along with a -1 for two of the bigint >> columns. like this: >> insert into seg_id_tmp7 >> ( customer_srcid, >> show_srcid, >> show_name, >> season_srcid, >> season_name, >> episode_srcid, >> episode_name, >> segment_type_id, >> segment_type, >> segment_srcid, >> segment_name, >> create_dt ) >> select >> t8.customer_srcid, >> t8.show_srcid, >> t8.show_name, >> t8.season_srcid, >> t8.season_name, >> t8.episode_srcid, >> t8.episode_name >> -1::bigint , >> 'none', >> -1::bigint , >> 'none', >> (select start_dt from load_start_date) >> from >> seg_id_tmp8 t8 >> ; >> I've also tried without the ::bigint cast on the above query. >> I always get this error: >> sql:load_sl_cd_segment_dim.sql:237: ERROR: operator does not >> exist: character varying - bigint > > Try a comma before the -1 > > You are trying to enter - "t8.episode_name -1::bigint" into the column > > hence - "character varying - bigint" does not work > > > > -- > > Shane Ambler > pgSQL (at) Sheeky (dot) Biz > > Get Sheeky @ http://Sheeky.Biz > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin