Implementation difference between PostgreSQL and MS SQL Server in varchar with no length.

Today , I learned something new  in PostgreSQL. Surprised  to see the implementation difference between  PostgreSQL and MS SQL Server . 

In PostgreSQL and SQL Server , I can define a 'varchar' or 'char' datatype  with no width . In PostgreSQL , 'char' datatype with no length means char(1) , whereas 'varchar' datatype with no length means that it can accommodate any size  

Here is the example from PostgreSQL

 sales=# SELECT version();
                                                   version
 -----------------------------------------------------------------------------------------------------------
  PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit
(1 row)

sales=# create table t( c char) ;
CREATE TABLE
 sales=# insert into t values ('AA');
ERROR:  value too long for type character(1)
sales=# insert into t values ('A');
INSERT 0 1
sales=# select * from t;
 c
---
 A
(1 row)


sales=# drop table t;
DROP TABLE
sales=#
sales=# create table t( c varchar) ;
CREATE TABLE
sales=# insert into t values ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
sales-# ;
INSERT 0 1
sales=# Select * from t;
                                           c
---------------------------------------------------------------------------------------
 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


Here is the example from SQL Server.


C:\>sqlcmd -W
1> use test
2> go
Changed database context to 'test'.
1> Select @@version ;
2> go

-
Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)
        May 27 2016 15:06:08
        Copyright (c) Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 14393: )


(1 rows affected)
1> drop table t;
2> go
1> create table t( c char) ;
2> go
1> insert into t values ('AA');
2> go
Msg 8152, Level 16, State 14, Server DOCS-0961, Line 1
String or binary data would be truncated.
The statement has been terminated.
1> insert into t values ('A');
2> go

(1 rows affected)
1> Select * from t;
2> go
c
-
A

(1 rows affected)
1> drop table t ;
2> create table t( c varchar) ;
3> go
1> insert into t values ('AA');
2> go
Msg 8152, Level 16, State 14, Server DOCS-0961, Line 1
String or binary data would be truncated.
The statement has been terminated.
1> insert into t values ('A');
2> go

(1 rows affected)
1> Select * from t;
2> go
c
-
A

(1 rows affected)

1>

As can be seen from the script above , PostgresQL and SQL Server behave the same way when a column is defined 'char' with no length. And it is so drastically different when it comes to 'varchar' with no length. 

In Oracle , this is not allowed . Any 'char' or 'varchar' has to have a length defined . 

Learning something new every day.