Table foo:
create table table_foo
(
foo_id integer,
foo_some_timestamp timestamp without timezone
)
Table bar:
create table table_bar
(
bar_id integer,
foo_id integer,
bar_some_timestamp timestamp without timezone
)
When I do an insert like so, it fails:
insert into table_bar (foo_id, bar_some_timestamp) (
select foo_id, foo_some_timestamp as bar_some_timestamp
from foo
left join table_bar on table_foo.foo_id = table_bar.foo_id
);
But I get an error:
column "foo_sime_timestamp" is of type timestamp without time zone but expression is of type text Hint: You will need to rewrite or cast the expression.
I have tried to_timestamp which weirdly throws the following error:
to_timestamp(foo_some_timestamp, 'YYYY-MM-DD HH24-MI-SS.US')::timestamp without time zone as bar_some_timestamp
ERROR: function to_timestamp(timestamp without time zone, unknown) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Since it's a left join, the result contains a lot of null values. But using COALESCE yields the same errors.
What am I missing? (Postgres v11)
Your target table has three columns, but your select only provides two. You should always qualify the target columns in an INSERT statement, but if you provide less than than the target table has, this is mandatory. The columns are matched by position, not by name.
insert into table_bar (foo_id, bar_some_timestamp)
select tf.foo_id, tf.foo_some_timestamp
from table_foo tf
left join table_bar tb on tf.foo_id = tb.foo_id
;