[SQL] HOW TO HANDLE ZEROS IN DATE FIELD?
I have a project that is taking input from another system. I have certain columns defined as 'Date' Columns. On input I will get '00' in this field which causes the insert to fail. I have read the docs on default and it is unclear to me if this will work. Does anyone have experience in solving this problem. The other alternative I thought of was to write a trigger to fix it ?? James M Doherty [EMAIL PROTECTED] Georgetown, TX 78626 "There is no luck without discipline" IRISH PROVERB ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] quote_literal() fails with unmatched quotes
I have had the following problem and been unable to determine the cause !
nmostlikemat := iso_date(nmostlikemat);
EXECUTE ''insert into scenario_inv (''
|| ''inv_id,''
|| ''record_date,''
|| ''scenario_type,''
|| ''mostlikemat,''
|| ''mktpr,''
|| ''mktyld )''
|| '' values ( ''
|| quote_literal(ID)
|| '',''
|| quote_literal(nnrecord_date)
|| '',''
|| quote_literal(nscid)
|| '',''
|| quote_literal(nmostlikemat)
|| '',''
|| quote_literal(nmktpr)
|| '',''
|| quote_literal(nmktyld)
|| '');'';
The following error returns from postgres 8.1
ERROR: unterminated quoted string at or near "'2009-10-03" at character 122
QUERY: insert into scenario_inv
(inv_id,record_date,scenario_type,mostlikemat,mktpr,mktyld ) values (
'405832','2005-06-30','1','2009-10-03
CONTEXT: PL/pgSQL function "scenario_set" line 502 at execute statement
PL/pgSQL function "fix_invest_set_scenario" line 18 at if
LINE 1: ...mktpr,mktyld ) values ( '405832','2005-06-30','1','2009-10-0...
James M Doherty
Principal
JMD CONSULTING
411 Thunderbay Dr
Georgetown, TX 78626
A reputation is something others give you HONOR is something you give
yourself!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Quote_literal()
declare SQL text; NSQLtext; SQL := ''10/3/2009''; NSQL := quote_literal(SQL); The result is '10/3/2009 with no closing quote. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give yourself! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] quote_literal() fails with unmatched quotes
Nmostlikemat := ''10/3/2009'';
What happens is that quote_literal works for everyone in the example but
this one.
Jim
James M Doherty
Principal
JMD CONSULTING
411 Thunderbay Dr
Georgetown, TX 78626
A reputation is something others give you HONOR is something you give
yourself!
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of James M Doherty
Sent: Wednesday, November 30, 2005 2:33 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] quote_literal() fails with unmatched quotes
I have had the following problem and been unable to determine the cause !
nmostlikemat := iso_date(nmostlikemat);
EXECUTE ''insert into scenario_inv (''
|| ''inv_id,''
|| ''record_date,''
|| ''scenario_type,''
|| ''mostlikemat,''
|| ''mktpr,''
|| ''mktyld )''
|| '' values ( ''
|| quote_literal(ID)
|| '',''
|| quote_literal(nnrecord_date)
|| '',''
|| quote_literal(nscid)
|| '',''
|| quote_literal(nmostlikemat)
|| '',''
|| quote_literal(nmktpr)
|| '',''
|| quote_literal(nmktyld)
|| '');'';
The following error returns from postgres 8.1
ERROR: unterminated quoted string at or near "'2009-10-03" at character 122
QUERY: insert into scenario_inv
(inv_id,record_date,scenario_type,mostlikemat,mktpr,mktyld ) values (
'405832','2005-06-30','1','2009-10-03
CONTEXT: PL/pgSQL function "scenario_set" line 502 at execute statement
PL/pgSQL function "fix_invest_set_scenario" line 18 at if
LINE 1: ...mktpr,mktyld ) values ( '405832','2005-06-30','1','2009-10-0...
James M Doherty
Principal
JMD CONSULTING
411 Thunderbay Dr
Georgetown, TX 78626
A reputation is something others give you HONOR is something you give
yourself!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(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
Re: [SQL] Quote_literal()
Tom, I found the problem. It seems that a function I was calling date_add_days(date,int) Returned for some reason a text date + [some unprintable garbage] two characters worth This in turn caused the quote_literal() to fail by returning only the initial single quote. I resolved the problem by replacing the date_add_days() function with date_pli() [comes with 8.1], After doing this my problem disappeared. I am still investigating what the cause of of the Failure in date_add_days() was [part of my 'c' library that I ported from gnumeric. I suspect A memory leak [lack of pfree] in the one of the functions. Any way thanks for taking the time to look at my issue. Jim James M Doherty Principal JMD CONSULTING 411 Thunderbay Dr Georgetown, TX 78626 A reputation is something others give you HONOR is something you give yourself! -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 30, 2005 4:28 PM To: James M Doherty Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Quote_literal() "James M Doherty" <[EMAIL PROTECTED]> writes: > declare > SQL text; > NSQLtext; > SQL := ''10/3/2009''; > NSQL := quote_literal(SQL); > The result is '10/3/2009 with no closing quote. Works for me: regression=# create function foo() returns text as ' regression'# declare SQL text; regression'# NSQLtext; regression'# begin regression'# SQL := ''10/3/2009''; regression'# NSQL := quote_literal(SQL); regression'# return NSQL; regression'# end' language plpgsql; CREATE FUNCTION regression=# select foo(); foo - '10/3/2009' (1 row) Again, I suggest giving a *complete* example, because you are obviously not looking in the right place for your problem. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
