INSERT INTO select distinct null PostgreSQL

Question

I'm having problem with the following:

create table tab1 (col1 double precision, col2 double precision)
Insert into tab1 
select distinct col1, null 
from tab1

I can't do that, because of error column "col2" is of type double precision but expression is of type text

But this has no error:

Insert into tab1 
select col1, null 
from tab1

Why do I have an error with distinct and no error without it?

Yes, adding ::double precision is helping here. but my question is WHY error is here? what is the difference between select distinct col1, null and select col1, null in logic of posgresql — Ispirer SQLWays Migrations
– Ispirer SQLWays Migrations, Commented Jul 1, 2019 at 15:07

Laurenz Albe · Accepted Answer · 2019-07-01 15:30:12Z

2

The DISTINCT is either calculated as a hash aggregate or by sorting, and both operations require that the type of the second column be determined (originally it is unknown).

So according to the rules for determining the type of output columns, PostgreSQL will resolve the NULL to text in this case.

You can easily override that with an explicit type cast.

answered Jul 1, 2019 at 15:30

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

INSERT INTO select distinct null PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related