Insert a row with NULL values from one table
I have a custom type:
CREATE TYPE public.relacion AS
(id integer,
codpadre character varying,
codhijo character varying,
canpres numeric(7,3),
cancert numeric(7,3),--this is usually NULL
posicion smallint);
After that, I need another type from relacion type:
CREATE TYPE public.borrarrelacion AS
(idborrar integer,
paso integer,
rel relacion);
Well, now into a function I need to copy some rows from a relacion type table to borrarrelacion type table:
This is a snippet of my code:
DECLARE:
r relacion%ROWTYPE;
------------
BEGIN
EXECUTE FORMAT ('CREATE TABLE IF NOT EXISTS "mytable" OF borrarrelacion (PRIMARY KEY (idborrar))');
EXECUTE FORMAT ('SELECT * FROM %I WHERE codpadre = %s AND codhijo = %s',
tablarelacion,quote_literal(codigopadre),quote_literal(codigohijo)) INTO r;
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,%s)',r);
But I get an error because the field r.cancert is NULL and it's trying to insert something like that:
INSERT INTO "mytable" VALUES(0,0,(0,'cod1','cod2',10,,0));
I can solve this problem reading every field of r and putting it values in the INSERT statement, like that: (and changing the NULL value by 0)
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,(%s,%s,%s,%s,%s,%s))',
r.id,quote_literal(r.codpadre),quote_literal(r.codhijo),r.canpres,COALESCE(r.cancert,0),r.posicion);
But I would like to know if I can avoid this way and I can insert exactly the same row from one table to other.
postgresql
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
add a comment |
I have a custom type:
CREATE TYPE public.relacion AS
(id integer,
codpadre character varying,
codhijo character varying,
canpres numeric(7,3),
cancert numeric(7,3),--this is usually NULL
posicion smallint);
After that, I need another type from relacion type:
CREATE TYPE public.borrarrelacion AS
(idborrar integer,
paso integer,
rel relacion);
Well, now into a function I need to copy some rows from a relacion type table to borrarrelacion type table:
This is a snippet of my code:
DECLARE:
r relacion%ROWTYPE;
------------
BEGIN
EXECUTE FORMAT ('CREATE TABLE IF NOT EXISTS "mytable" OF borrarrelacion (PRIMARY KEY (idborrar))');
EXECUTE FORMAT ('SELECT * FROM %I WHERE codpadre = %s AND codhijo = %s',
tablarelacion,quote_literal(codigopadre),quote_literal(codigohijo)) INTO r;
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,%s)',r);
But I get an error because the field r.cancert is NULL and it's trying to insert something like that:
INSERT INTO "mytable" VALUES(0,0,(0,'cod1','cod2',10,,0));
I can solve this problem reading every field of r and putting it values in the INSERT statement, like that: (and changing the NULL value by 0)
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,(%s,%s,%s,%s,%s,%s))',
r.id,quote_literal(r.codpadre),quote_literal(r.codhijo),r.canpres,COALESCE(r.cancert,0),r.posicion);
But I would like to know if I can avoid this way and I can insert exactly the same row from one table to other.
postgresql
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
"But I get an error because the field r.cancert is NULL" - what error exactly do you get?
– 404
Nov 27 '18 at 22:06
@eurotrash, I get something like that: INSERT INTO "algo" VALUES(0,0,(1,"CAP 1",P01,10.000,,0)) and ERROR: syntax error at or near "," SQL state: 42601
– user3733164
Nov 27 '18 at 22:10
Why do you use dynamic SQL at all? None of the statements shown would require that.
– a_horse_with_no_name
Nov 27 '18 at 22:10
@a_horse_with_no_name, sorry. It's only a simplify example. The real problem create the tables dinamically and I need to use dynamic SQL
– user3733164
Nov 27 '18 at 22:12
add a comment |
I have a custom type:
CREATE TYPE public.relacion AS
(id integer,
codpadre character varying,
codhijo character varying,
canpres numeric(7,3),
cancert numeric(7,3),--this is usually NULL
posicion smallint);
After that, I need another type from relacion type:
CREATE TYPE public.borrarrelacion AS
(idborrar integer,
paso integer,
rel relacion);
Well, now into a function I need to copy some rows from a relacion type table to borrarrelacion type table:
This is a snippet of my code:
DECLARE:
r relacion%ROWTYPE;
------------
BEGIN
EXECUTE FORMAT ('CREATE TABLE IF NOT EXISTS "mytable" OF borrarrelacion (PRIMARY KEY (idborrar))');
EXECUTE FORMAT ('SELECT * FROM %I WHERE codpadre = %s AND codhijo = %s',
tablarelacion,quote_literal(codigopadre),quote_literal(codigohijo)) INTO r;
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,%s)',r);
But I get an error because the field r.cancert is NULL and it's trying to insert something like that:
INSERT INTO "mytable" VALUES(0,0,(0,'cod1','cod2',10,,0));
I can solve this problem reading every field of r and putting it values in the INSERT statement, like that: (and changing the NULL value by 0)
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,(%s,%s,%s,%s,%s,%s))',
r.id,quote_literal(r.codpadre),quote_literal(r.codhijo),r.canpres,COALESCE(r.cancert,0),r.posicion);
But I would like to know if I can avoid this way and I can insert exactly the same row from one table to other.
postgresql
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
I have a custom type:
CREATE TYPE public.relacion AS
(id integer,
codpadre character varying,
codhijo character varying,
canpres numeric(7,3),
cancert numeric(7,3),--this is usually NULL
posicion smallint);
After that, I need another type from relacion type:
CREATE TYPE public.borrarrelacion AS
(idborrar integer,
paso integer,
rel relacion);
Well, now into a function I need to copy some rows from a relacion type table to borrarrelacion type table:
This is a snippet of my code:
DECLARE:
r relacion%ROWTYPE;
------------
BEGIN
EXECUTE FORMAT ('CREATE TABLE IF NOT EXISTS "mytable" OF borrarrelacion (PRIMARY KEY (idborrar))');
EXECUTE FORMAT ('SELECT * FROM %I WHERE codpadre = %s AND codhijo = %s',
tablarelacion,quote_literal(codigopadre),quote_literal(codigohijo)) INTO r;
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,%s)',r);
But I get an error because the field r.cancert is NULL and it's trying to insert something like that:
INSERT INTO "mytable" VALUES(0,0,(0,'cod1','cod2',10,,0));
I can solve this problem reading every field of r and putting it values in the INSERT statement, like that: (and changing the NULL value by 0)
EXECUTE FORMAT ('INSERT INTO "mytable" VALUES(0,0,(%s,%s,%s,%s,%s,%s))',
r.id,quote_literal(r.codpadre),quote_literal(r.codhijo),r.canpres,COALESCE(r.cancert,0),r.posicion);
But I would like to know if I can avoid this way and I can insert exactly the same row from one table to other.
postgresql
postgresql
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
asked Nov 27 '18 at 22:00
user3733164user3733164
1729
1729
"But I get an error because the field r.cancert is NULL" - what error exactly do you get?
– 404
Nov 27 '18 at 22:06
@eurotrash, I get something like that: INSERT INTO "algo" VALUES(0,0,(1,"CAP 1",P01,10.000,,0)) and ERROR: syntax error at or near "," SQL state: 42601
– user3733164
Nov 27 '18 at 22:10
Why do you use dynamic SQL at all? None of the statements shown would require that.
– a_horse_with_no_name
Nov 27 '18 at 22:10
@a_horse_with_no_name, sorry. It's only a simplify example. The real problem create the tables dinamically and I need to use dynamic SQL
– user3733164
Nov 27 '18 at 22:12
add a comment |
"But I get an error because the field r.cancert is NULL" - what error exactly do you get?
– 404
Nov 27 '18 at 22:06
@eurotrash, I get something like that: INSERT INTO "algo" VALUES(0,0,(1,"CAP 1",P01,10.000,,0)) and ERROR: syntax error at or near "," SQL state: 42601
– user3733164
Nov 27 '18 at 22:10
Why do you use dynamic SQL at all? None of the statements shown would require that.
– a_horse_with_no_name
Nov 27 '18 at 22:10
@a_horse_with_no_name, sorry. It's only a simplify example. The real problem create the tables dinamically and I need to use dynamic SQL
– user3733164
Nov 27 '18 at 22:12
"But I get an error because the field r.cancert is NULL" - what error exactly do you get?
– 404
Nov 27 '18 at 22:06
"But I get an error because the field r.cancert is NULL" - what error exactly do you get?
– 404
Nov 27 '18 at 22:06
@eurotrash, I get something like that: INSERT INTO "algo" VALUES(0,0,(1,"CAP 1",P01,10.000,,0)) and ERROR: syntax error at or near "," SQL state: 42601
– user3733164
Nov 27 '18 at 22:10
@eurotrash, I get something like that: INSERT INTO "algo" VALUES(0,0,(1,"CAP 1",P01,10.000,,0)) and ERROR: syntax error at or near "," SQL state: 42601
– user3733164
Nov 27 '18 at 22:10
Why do you use dynamic SQL at all? None of the statements shown would require that.
– a_horse_with_no_name
Nov 27 '18 at 22:10
Why do you use dynamic SQL at all? None of the statements shown would require that.
– a_horse_with_no_name
Nov 27 '18 at 22:10
@a_horse_with_no_name, sorry. It's only a simplify example. The real problem create the tables dinamically and I need to use dynamic SQL
– user3733164
Nov 27 '18 at 22:12
@a_horse_with_no_name, sorry. It's only a simplify example. The real problem create the tables dinamically and I need to use dynamic SQL
– user3733164
Nov 27 '18 at 22:12
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53508871%2finsert-a-row-with-null-values-from-one-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53508871%2finsert-a-row-with-null-values-from-one-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
"But I get an error because the field r.cancert is NULL" - what error exactly do you get?
– 404
Nov 27 '18 at 22:06
@eurotrash, I get something like that: INSERT INTO "algo" VALUES(0,0,(1,"CAP 1",P01,10.000,,0)) and ERROR: syntax error at or near "," SQL state: 42601
– user3733164
Nov 27 '18 at 22:10
Why do you use dynamic SQL at all? None of the statements shown would require that.
– a_horse_with_no_name
Nov 27 '18 at 22:10
@a_horse_with_no_name, sorry. It's only a simplify example. The real problem create the tables dinamically and I need to use dynamic SQL
– user3733164
Nov 27 '18 at 22:12