Cómo insertar los valores de otra tabla en PostgreSQL?

Tengo una tabla que hace referencia a otras tablas:

CREATE TABLE scratch
(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  rep_id INT NOT NULL REFERENCES reps,
  term_id INT REFERENCES terms
);
CREATE TABLE reps (
  id    SERIAL PRIMARY KEY,
  rep   TEXT NOT NULL UNIQUE
);
CREATE TABLE terms (
  id    SERIAL PRIMARY KEY,
  terms TEXT NOT NULL UNIQUE
);

Quiero agregar un nuevo registro a cero dada la nombre, el rep y la términos valores, es decir, que no he correspondiente rep_id ni term_id.

Ahora la única idea que tengo es:

insert into scratch (name, rep_id, term_id)
             values ('aaa', (select id from reps where rep='Dracula' limit 1), (select id from terms where terms='prepaid' limit 1));

Mi problema es este. Estoy tratando de usar la consulta con parámetros de la API (desde el nodo usando el nodo-paquete postgres), donde una consulta insert se parece a esto:

insert into scratch (name, rep_id, term_id) values ($1, $2, $3);

y, a continuación, una matriz de valores por $1, $2 y $3 se pasa como un argumento separado. Al final, cuando me siento cómodo con las consultas parametrizadas la idea es promover a las declaraciones preparadas para utilizar el más eficiente y más segura para la consulta de la base de datos.

Sin embargo, estoy desconcertado ¿cómo puedo hacer esto con mi ejemplo, donde las diferentes tablas tienen que ser subqueried.

P. S. estoy usando PostgreSQL 9.2 y no tienen ningún problema con PostgreSQL solución específica.

EDITAR 1

C:\Users\markk>psql -U postgres
psql (9.2.4)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \c dummy
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
You are now connected to database "dummy" as user "postgres".
dummy=# DROP TABLE scratch;
DROP TABLE
dummy=# CREATE TABLE scratch
dummy-# (
dummy(#   id SERIAL NOT NULL PRIMARY KEY,
dummy(#   name text NOT NULL UNIQUE,
dummy(#   rep_id integer NOT NULL,
dummy(#   term_id integer
dummy(# );
NOTICE:  CREATE TABLE will create implicit sequence "scratch_id_seq" for serial column "scratch.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "scratch_pkey" for table "scratch"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "scratch_name_key" for table "scratch"
CREATE TABLE
dummy=# DEALLOCATE insert_scratch;
ERROR:  prepared statement "insert_scratch" does not exist
dummy=# PREPARE insert_scratch (text, text, text) AS
dummy-# INSERT INTO scratch (name, rep_id, term_id)
dummy-# SELECT $1, r.id, t.id
dummy-# FROM reps r, terms t
dummy-# WHERE r.rep = $2 AND t.terms = $3
dummy-# RETURNING id, name, $2 rep, $3 terms;
PREPARE
dummy=# DEALLOCATE insert_scratch2;
ERROR:  prepared statement "insert_scratch2" does not exist
dummy=# PREPARE insert_scratch2 (text, text, text) AS
dummy-# INSERT INTO scratch (name, rep_id, term_id)
dummy-#              VALUES ($1, (SELECT id FROM reps WHERE rep=$2 LIMIT 1), (SELECT id FROM terms WHERE terms=$3 LIMIT 1))
dummy-# RETURNING id, name, $2 rep, $3 terms;
PREPARE
dummy=# EXECUTE insert_scratch ('abc', 'Snowhite', '');
 id | name | rep | terms
----+------+-----+-------
(0 rows)


INSERT 0 0
dummy=# EXECUTE insert_scratch2 ('abc', 'Snowhite', '');
 id | name |   rep    | terms
----+------+----------+-------
  1 | abc  | Snowhite |
(1 row)


INSERT 0 1
dummy=# EXECUTE insert_scratch ('abcd', 'Snowhite', '30 days');
 id | name |   rep    |  terms
----+------+----------+---------
  2 | abcd | Snowhite | 30 days
(1 row)


INSERT 0 1
dummy=# EXECUTE insert_scratch2 ('abcd2', 'Snowhite', '30 days');
 id | name  |   rep    |  terms
----+-------+----------+---------
  3 | abcd2 | Snowhite | 30 days
(1 row)


INSERT 0 1
dummy=#

EDITAR 2

Podemos utilizar el hecho de que rep_id es necesario, aunque terms_id es opcional y se utiliza la siguiente versión de INSERTAR SELECCIONAR:

PREPARE insert_scratch (text, text, text) AS
INSERT INTO scratch (name, rep_id, term_id)
SELECT $1, r.id, t.id
FROM reps r
LEFT JOIN terms t ON t.terms = $3
WHERE r.rep = $2
RETURNING id, name, $2 rep, $3 terms;

Esta versión, sin embargo, tiene dos problemas:

  1. No se hace ninguna distinción entre una falta terms valor (es decir,») y una no válida terms valor (es decir, un no valor vacío que faltan en la tabla de términos de totalidad). Ambos son tratados como términos faltantes. (Pero INSERT con dos subconsultas sufre el mismo problema)
  2. La versión depende del hecho de que la rep es necesario. Pero lo que si rep_id era opcional demasiado?

EDITAR 3

Encontrado la solución para el elemento 2 – la eliminación de la dependencia de la rep que se requiere. Además de utilizar los lugares DONDE declaración tiene el problema de que el sql no fallar si el representante no es válido, simplemente inserta 0 filas, mientras que la quiero fallar de manera explícita en este caso. Mi solución es simplemente el uso de un maniquí de una fila CTE:

PREPARE insert_scratch (text, text, text) AS
WITH stub(x) AS (VALUES (0))
INSERT INTO scratch (name, rep_id, term_id)
SELECT $1, r.id, t.id
FROM stub
LEFT JOIN terms t ON t.terms = $3
LEFT JOIN reps r ON r.rep = $2
RETURNING id, name, rep_id, term_id;

Si el representante falta o no es válido, este sql intentará insertar NULO en el rep_id campo y dado que el campo es NOT NULL un error sería criado – precisamente lo que necesito. Y si me decido a hacer rep opcional – no hay problema, el mismo SQL funciona para eso.

InformationsquelleAutor mark | 2013-10-15

1 Kommentar

  1. 3
    INSERT into scratch (name, rep_id, term_id)
    SELECT 'aaa'
            , r.id 
            , t.id
    FROM reps r , terms t -- essentially an outer join
    WHERE r.rep = 'Dracula'
      AND t.terms = 'prepaid'
            ;

    Notas:

    • Usted no necesita el feo LIMITs, ya que la r.rep y t.los términos son los únicos (candidato teclas)
    • usted puede reemplazar la FROM a, b por un FROM a FULL OUTER JOIN b
    • la scratch tabla necesitará probablemente un UNIQUE restricción on (rep_id, term_it) (la capacidad de term_id es cuestionable)

    ACTUALIZACIÓN: el mismo preparado consulta como se encuentra en la Documentación

    PREPARE hoppa (text, text,text) AS
            INSERT into scratch (name, rep_id, term_id)
            SELECT $1 , r.id , t.id
            FROM reps r , terms t -- essentially an outer join
            WHERE r.rep = $2
            AND t.terms = $3
            ;
    EXECUTE hoppa ('bbb', 'Dracula' , 'prepaid' );
    
    SELECT * FROM scratch;

    UPDATE2: datos de prueba

    DROP SCHEMA tmp CASCADE;
    CREATE SCHEMA tmp ;
    SET search_path=tmp;
    
    CREATE TABLE reps ( id    SERIAL PRIMARY KEY, rep   TEXT NOT NULL UNIQUE);
    CREATE TABLE terms ( id    SERIAL PRIMARY KEY, terms TEXT NOT NULL UNIQUE);
    CREATE TABLE scratch ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, rep_id INT NOT NULL REFERENCES reps, term_id INT REFERENCES terms);
    
    INSERT INTO  reps(rep) VALUES( 'Dracula' );
    INSERT INTO  terms(terms) VALUES( 'prepaid' );

    Resultados:

    NOTICE:  drop cascades to 3 other objects
    DETAIL:  drop cascades to table tmp.reps
    drop cascades to table tmp.terms
    drop cascades to table tmp.scratch
    DROP SCHEMA
    CREATE SCHEMA
    SET
    CREATE TABLE
    CREATE TABLE
    CREATE TABLE
    INSERT 0 1
    INSERT 0 1
    INSERT 0 1
    PREPARE
    INSERT 0 1
     id | name | rep_id | term_id 
    ----+------+--------+---------
      1 | aaa  |      1 |       1
      2 | bbb  |      1 |       1
    (2 rows)
    • Podría usted cambiar su ejemplo para utilizar el PREPARE declaración? Me gustaría ver cómo su ejemplo se ve como con $1, $2, …
    • Por alguna extraña razón esta PLAQUITA con SELECCIONAR no inserte nada para mí. Has comprobado que en un base de datos PostgreSQL?
    • Bueno, yo no entiendo lo que está pasando. Por favor, examine EDITAR > 1 a mi post, que contiene un psql de la sesión donde se puede ver que la INSERCIÓN SELECCIONE la versión no hace nada, mientras que la INSERCIÓN de dos subconsultas funciona como se esperaba. Me encantaría saber cuál es el problema.
    • Usted no debe pasar los parámetros` $1 $2 $3 ` como un conjunto, sino como argumentos separados (como se llama a una función) ver mi código de ejemplo : EXECUTE hoppa ('bbb', 'Dracula' , 'prepaid' );
    • Uy, acabo de dar cuenta que se me olvidó publicar el EDITAR > 1. Lo hizo ahora.
    • Aha código de página/codificación de los problemas …. SET client_encoding = 1251; (o algo así) en la parte superior de la secuencia de comandos SQL. (o restringir su cadenas de caracteres ASCII solamente)
    • Se están ampliando la pregunta. Por favor no hagan eso. Mi respuesta obras, si usted tiene dificultad en expansión que a un completo sistema de CRM, que ese es su problema. La buena suerte.
    • Lo siento, no entiendo cómo soy yo la expansión de la pregunta. Yo podría estar haciendo, sin saberlo, por supuesto. ¿Podría ser más específico y señalan el problema con mi ejemplo? Es a causa de los otros campos? Bien, siempre estaban en el cero de la tabla, sólo pensé que eran irrelevantes y omite ellos por razones de brevedad cuando me envió la pregunta. Usted piensa que son relavant después de todo? Voy a volver a probar sin ellos y actualizar mi EDITAR > 1.
    • OK, he quitado todos los campos adicionales, realmente no importa. Al parecer, lo que importa es que el terms valor es opcional. La INSERCIÓN SELECCIONE dado no funciona cuando el terms falta el valor, mientras que la INSERCIÓN de dos subconsultas – hace el trabajo. El terms_id se especifica como opcional desde el principio, así que no hay expansión de la pregunta. Lamentablemente no, la capacidad de terms_id es un hecho y no cuestionable.
    • Usted tendrá que calificar plenamente los términos en los RETURNING cláusula (id no es única). IIRC RETURNING sólo puede hacer referencia a la tabla afectada. (el lvalue en C-hablar)
    • El id cosa no importa, porque no hay ninguna ambigüedad – no hay otra tabla que tiene la columna id. Además no hay ninguna fila es realmente creado, independientemente de la DEVOLUCIÓN de la declaración. He añadido el último en el orden que me sobra la necesidad de invocar una selección independiente instrucción para comprobar la inserción.
    • Acabo de acreditado tu respuesta, porque contiene suficiente como para responder a mi pregunta – ver EDITAR > 2. Podría ser una perfecta respuesta (con extra +1) si se dirigió a las dos preguntas planteadas en la edición.

Kommentieren Sie den Artikel

Bitte geben Sie Ihren Kommentar ein!
Bitte geben Sie hier Ihren Namen ein

Pruebas en línea