Postgresql: Insert into ... (Select * ...)

postgresql insert into from select

Just remove pk column from columns of query

insert into test_table1 (col2,..., coln) select col2,...,coln from table1;

If it still fails maybe you have not sequence on pk columns.
Create sequence on already existing pk column

create sequence test_table1_seq;
ALTER TABLE test_table1 
    ALTER COLUMN col1 SET DEFAULT nextval('test_table1_seq'::regclass);

And update sequence value to current

SELECT setval('test_table1_seq', (SELECT MAX(col1) FROM test_table1));

Postgres insert from select statement with multiple values

You probably want

INSERT INTO foo (x, y)
SELECT b1.id, 3
FROM boo b1;

A literal value must go into the SELECT clause, it's not another table in the FROM clause. What can also work is

INSERT INTO foo (x, y)
VALUES
  ( (SELECT b1.id FROM boo b1 WHERE …), 3 );

or

INSERT INTO foo (x, y)
SELECT (SELECT b1.id FROM boo b1 WHERE …), 3;

but these are only valid if the inner SELECT (a subquery) returns exactly one value, using an appropriate WHERE condition.

postgresql: INSERT INTO ... (SELECT * ...)

As Henrik wrote you can use dblink to connect remote database and fetch result. For example:

psql dbtest
CREATE TABLE tblB (id serial, time integer);
INSERT INTO tblB (time) VALUES (5000), (2000);

psql postgres
CREATE TABLE tblA (id serial, time integer);

INSERT INTO tblA
    SELECT id, time 
    FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, time integer)
    WHERE time > 1000;

TABLE tblA;
 id | time 
----+------
  1 | 5000
  2 | 2000
(2 rows)

PostgreSQL has record pseudo-type (only for function's argument or result type), which allows you query data from another (unknown) table.

Edit:

You can make it as prepared statement if you want and it works as well:

PREPARE migrate_data (integer) AS
INSERT INTO tblA
    SELECT id, time
    FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, time integer)
    WHERE time > $1;

EXECUTE migrate_data(1000);
-- DEALLOCATE migrate_data;

Edit (yeah, another):

I just saw your revised question (closed as duplicate, or just very similar to this).

If my understanding is correct (postgres has tbla and dbtest has tblb and you want remote insert with local select, not remote select with local insert as above):

psql dbtest

SELECT dblink_exec
(
    'dbname=postgres',
    'INSERT INTO tbla
        SELECT id, time
        FROM dblink
        (
            ''dbname=dbtest'',
            ''SELECT id, time FROM tblb''
        )
        AS t(id integer, time integer)
        WHERE time > 1000;'
);

I don't like that nested dblink, but AFAIK I can't reference to tblB in dblink_exec body. Use LIMIT to specify top 20 rows, but I think you need to sort them using ORDER BY clause first.

INSERT INTO with SELECT (only subset of columns and column ordering)

Just specify target and source columns:

INSERT INTO dummy_data (column_2, column_6, column_9, column_10)
SELECT t.col4, t.col2, t.col1, c.col3
FROM dummy_data_temp t
  LEFT OUTER JOIN dummy_data dd ON dd.id = t.id
WHERE dd.id IS NULL;

(A NOT EXISTS condition might be more efficient though)

Postgresql: INSERT INTO using SELECT and values

INSERT INTO MyTable (character, number) 
    SELECT character, '2' FROM MyTable WHERE number = '1'

to save the data in the same format on your example

PotgresSQL INSERT INTO SELECT CASE problem

union all can to help:

insert into logs (_timestap, _message, _mode, _user_id)

select :_timestamp, :_message, :_mode, id
from users
where :_mode = 'byGrade' and grade = :_grade

union all

select :_timestamp, :_message, :_mode, :_user_id
where :_mode = 'byIndividual'

union all

select :_timestamp, :_message, :_mode, id
from users
where :_mode = 'everyone';

PostgreSQL: Trigger INSERT INTO SELECT from other table

You should be using the NEW record in the trigger function to reference the newly inserted data instead of a select, i.e.:

CREATE OR REPLACE FUNCTION triger_function() RETURNS TRIGGER AS
$BODY$
BEGIN
INSERT INTO public.destination_table ( created_at, sale_id, product_id, product_name, url, shop_id, user_id)
VALUES(NEW.created_at,
    NEW.sale_id,
    NEW.product_id,
    NEW.product_name,
    split_part(NEW.url::text, '?'::text, 1),
    NEW.shop_id,
    ((((((((NEW.data #>> '{}'::text[])::jsonb) #>> '{}'::text[])::jsonb) -> 'local_storage'::text) -> 'data'::text) #>> '{}'::text[])::jsonb) ->> 'user_id'::varchar)
       RETURN new;
END;
$BODY$
language plpgsql;

---

Related Topics