agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedExecute format Insert sql failing with query string argument of EXECUTE is null for column with timestamp with timezone
3+ messages / 2 participants
[nested] [flat]
* Execute format Insert sql failing with query string argument of EXECUTE is null for column with timestamp with timezone
@ 2017-08-26 05:38 anand086 <anand086@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: anand086 @ 2017-08-26 05:38 UTC (permalink / raw)
To: pgsql-sql
I am a Postgres Newbie and looking for some help on how I can achieve to have
null in child table column with "timestamp with time zone" when using
EXECUTE format insert sql.I have a range partitioned table by day and want
to insert data into the current day's partition. Trigger is available on the
master table based on which the data would be directed to current day's
partition. But I want to directly insert data into the child table rather
than using trigger to have it done.We have a table which maintains the child
table names along with its low and high date values.Below is kind of what I
am doing --
CREATE OR REPLACE FUNCTION test_tab_func (BIGINT, TIMESTAMP WITH TIME ZONE,
TIMESTAMP WITH TIME ZONE)RETURNS booleanAS $BODY$DECLARE _id ALIAS FOR $1;
_time ALIAS for $2; _parttime ALIAS for $3; _partition text; _z
RECORD;BEGIN SELECT child_table_name into STRICT _partition from
child_tables where parent_table_name='test_tab' and part_low=_parttime; FOR
_z IN SELECT table_id, analyzed_on FROM
test_tab WHERE schemaname = 'app' LOOP EXECUTE
format('INSERT INTO dev.%s (id,
time, table_id,
analyzed_on) VALUES ('||_id|||',
'''||_time ||''',
'||_z.table_id||',
'||_z.analyzed_on||')',_partition); END LOOP; RETURN true;END;$BODY$
LANGUAGE plpgsql;
The table "test_tab" has few rows with analyzed_on as null. When I run the
function I get the below errorERROR: query string argument of EXECUTE is
null.I way which I know is using COALESCE. But how do I achieve null value
in my child table using COALESCE. I am not able to figure it out.
--
View this message in context: http://www.postgresql-archive.org/Execute-format-Insert-sql-failing-with-query-string-argument-of-EX...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Execute format Insert sql failing with query string argument of EXECUTE is null for column with timestamp with timezone
@ 2017-08-26 05:50 Pavel Stehule <pavel.stehule@gmail.com>
parent: anand086 <anand086@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pavel Stehule @ 2017-08-26 05:50 UTC (permalink / raw)
To: anand086 <anand086@gmail.com>; +Cc: pgsql-sql
2017-08-26 7:38 GMT+02:00 anand086 <anand086@gmail.com>:
> I am a Postgres Newbie and looking for some help on how I can achieve to
> have null in child table column with "timestamp with time zone" when using
> EXECUTE format insert sql. I have a range partitioned table by day and want
> to insert data into the current day's partition. Trigger is available on
> the master table based on which the data would be directed to current day's
> partition. But I want to directly insert data into the child table rather
> than using trigger to have it done. We have a table which maintains the
> child table names along with its low and high date values. Below is kind of
> what I am doing --
>
> CREATE OR REPLACE FUNCTION test_tab_func (BIGINT, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE)
> RETURNS boolean
> AS
> $BODY$
> DECLARE
> _id ALIAS FOR $1;
> _time ALIAS for $2;
> _parttime ALIAS for $3;
> _partition text;
> _z RECORD;
> BEGIN
>
> SELECT child_table_name into STRICT _partition from child_tables where parent_table_name='test_tab' and part_low=_parttime;
>
> FOR _z IN SELECT table_id,
> analyzed_on
> FROM test_tab
> WHERE schemaname = 'app' LOOP
> EXECUTE format('INSERT INTO dev.%s (id,
> time,
> table_id,
> analyzed_on)
> VALUES ('||_id|||',
> '''||_time ||''',
> '||_z.table_id||',
> '||_z.analyzed_on||')',_partition);
> END LOOP;
> RETURN true;
> END;
> $BODY$
> LANGUAGE plpgsql;
>
> The table "test_tab" has few rows with analyzed_on as null. When I run the
> function I get the below error ERROR: query string argument of EXECUTE is
> null. I way which I know is using COALESCE. But how do I achieve null value
> in my child table using COALESCE. I am not able to figure it out.
any parameter can be NULL: _id, _time, _z.table_id, _z.analyzed_on
NULL is same as empty string on Oracle, but on PostgreSQL NULL is NULL
every where.
You can use USING clause for better readability and robustness against NULLs
EXECUTE format('INSERT INTO dev.%s (id,
time,
table_id,
analyzed_on)
VALUES ($1, $2, $3,
$4)',_partition)
USING _id, _time, _z.table_id, _z.analyzed_on;
Regards
Pavel
>
> ------------------------------
> View this message in context: Execute format Insert sql failing with
> query string argument of EXECUTE is null for column with timestamp with
> timezone
> <http://www.postgresql-archive.org/Execute-format-Insert-sql-failing-with-query-string-argument-of-EX...;
> Sent from the PostgreSQL - sql mailing list archive
> <http://www.postgresql-archive.org/PostgreSQL-sql-f2142323.html; at
> Nabble.com.
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Execute format Insert sql failing with query string argument of EXECUTE is null for column with timestamp with timezone
@ 2017-08-26 08:04 anand086 <anand086@gmail.com>
parent: Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: anand086 @ 2017-08-26 08:04 UTC (permalink / raw)
To: pgsql-sql
Awesome. That helps, thank you!!
--
View this message in context: http://www.postgresql-archive.org/Execute-format-Insert-sql-failing-with-query-string-argument-of-EX...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-08-26 08:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-26 05:38 Execute format Insert sql failing with query string argument of EXECUTE is null for column with timestamp with timezone anand086 <anand086@gmail.com>
2017-08-26 05:50 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-08-26 08:04 ` anand086 <anand086@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox