CODEWITHSUNDEEP
postgresql
Egidi
Egidi

Reputation: 1776

Forcing insert in Postgresql when INSERT has more expressions than target columns

I have a Postgreql table (first table):

CREATE TABLE public.getxo_todoseniales_hv
(
  id numeric(8,0),
  "GEOMETRY" geometry,
  "Comentario" character varying,
  "Codigo" character varying,
  "Leyenda" character varying,
  "FechaImpla" date,
  "FechaCambi" date,
  "FechaRetir" date,
  rotacion numeric,
  variacion numeric(10,0),
  fecha_mecanizacion timestamp without time zone,
  usuario_mecanizacion character varying,
  tipo_variacion character varying,
  variacion_posterior numeric(10,0),
  CONSTRAINT getxo_todoseniales_hv_id_key UNIQUE (id, variacion)
)
WITH (
  OIDS=TRUE
);

And I have another table (second table):

CREATE TABLE public.getxo_todoseniales_borrar
(
  id numeric(8,0),
  "GEOMETRY" geometry,
  "Comentario" character varying,
  "Codigo" character varying,
  "Leyenda" character varying,
  "FechaImpla" date,
  "FechaCambi" date,
  "FechaRetir" date,
  rotacion numeric,
  CONSTRAINT getxo_todoseniales_hv_id_key UNIQUE (id, variacion)
)
WITH (
  OIDS=TRUE
);

Ok, notice that the second table has the same fields except from the last 5 fields.

Now I want to insert all the rows from first table to second table but without having to specify the columns one by one. I will have to execute this query in a PL/SQL function and I it will be used for a lot of tables which fields won't be known to me. The only constant is that first table will have the same columns except from the last five colums (on the second table that I don't need them).

I tried: 

INSERT INTO getxo_todoseniales_borrar (SELECT * FROM getxo_todoseniales_hv);

But as expected, I am gettin the error:

Insert has more expressions than target columns

Is there any way to force postgresql to insert the data which is in the columns they have in common?

Upvotes: 1

Views: 818

Answers (1)

Saddam Khan
Saddam Khan

Reputation: 180

You can find the column name using this syntax

select string_agg(column_name,',') from information_schema.columns 
where table_name = 'table_name' limit ((select count(*) from table_name)-5) ;

now you find the column name in , separated value. After that you run the syntax.

insert into table_name select column_name from table_name

Upvotes: 1

Related Questions