CODEWITHSUNDEEP
amazon-web-servicessql-insertamazon-redshift
FirstName
FirstName

Reputation: 417

Insert into as select in redshift

I have a table emp - 

empno int(5) identity(1,1),
ename varchar(50) default '-0',
deptname varchar(50) default '-0'

When I use the statement - 

insert into emp(ename, deptname) select ename, deptname from emp1;

I am getting all the values from emp1, but the null values are not substituted with -0 but rather with null.

What is the reason ?

Upvotes: 4

Views: 23988

Answers (2)

Saiyam Jain
Saiyam Jain

Reputation: 161

insert into table_name(column_list) (select column_list from table_name);

make sure you don't have any columns whose name is like datatype

ex : timestamp is datatype but by mistake if you have created column name using datatype use alias name in select query like timestamp as Timestamp_t

Upvotes: 0

moertel
moertel

Reputation: 1559

Intended Behaviour

According to the Redshift documentation:

DEFAULT default_expr

[...]

The default_expr expression is used in any INSERT operation that does not specify a value for the column. If no default value is specified, the default value for the column is null.

So when you execute insert into emp(ename, deptname) then you do specify values for both those columns: if nothing else, they are NULL. Only if you leave out the columns, the default values will be used. So default values are only really useful if you insert values directly:

insert into emp(ename, deptname) values('some_ename', default);
insert into emp(ename, deptname) values(default, 'some_deptname');
insert into emp(ename, deptname) values(default, default);

Which would give:

select * from emp order by empno;
 empno |   ename    |   deptname
-------+------------+---------------
     1 | some_ename | -0
     2 | -0         | some_deptname
     3 | -0         | -0
(3 rows)

Possible Solutions to Your Problem

Option 1: Two INSERT Statements

Pro: You don't need to know about the actual default values
Con: This might be tedious if you have many columns

insert into emp(ename) select ename from emp1 where deptname is null;
insert into emp(deptname) select deptname from emp1 where ename is null;

Option 2: Use COALESCE

Pro: One query to default them all
Con: You will have to know the default values

insert into emp(ename, deptname)
    select
        coalesce(ename, '-0')    as ename
      , coalesce(deptname, '-0') as deptname
    from
        emp1
;

Upvotes: 7

Related Questions