Postgresql – Creating a composite type with “char”

datatypespostgresql

PostgreSQL has a type called char that's stored as an signed 1 byte int

The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used in the system catalogs as a simplistic enumeration type.

Can we create composite types with "char"? I can create a table with it…

CREATE TABLE pixel ( r "char", g "char", b "char" );

Internally that creates a type which I can use elsewhere,

CREATE TABLE f ( mypixel pixel );

But, can I create a simple TYPE (no table) from it?

CREATE TYPE pixel ( r "char", g "char", b "char" );
ERROR:  syntax error at or near ""char""
LINE 1: CREATE TYPE pixel ( r "char", g "char", b "char" );

Best Answer

It's just the missing keyword AS:

CREATE TYPE pixel AS (r "char", g "char", b "char");

The data type "char" is a non-standard type, primarily meant for internal purposes. But it's just another type - except for the peculiar spelling of the name including the double-quotes to disambiguate against char.

Related Solutions

PostgreSQL CREATE TABLE error

You can use reserved words by quoting them:

CREATE TABLE mail_user (
   "user" char(50) NOT NULL,
   domain char(50) NOT NULL,
   password char(50) NOT NULL
);

Effect of Changing Data Types with Row Compression Enabled in SQL Server

As the documentation you alredy have linked states, ROW compression uses only the bytes that are needed. Once ROW compression is in use, the CPU cycles used to convert from/to int or bigint are the same: I would not worry about that.

BTW, if you're not sure whether int/bigint has an impact on the database size or not (it hasn't), you can see for yourself with a quick and dirty repro:

USE tempdb;
GO

CREATE TABLE SomeTable (
    SomeColumn bigint
)
GO

ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);

INSERT INTO SomeTable 
SELECT TOP 10000000 ROW_NUMBER() OVER (ORDER BY(SELECT NULL))
FROM sys.all_columns AS A1
CROSS JOIN sys.all_columns AS A2;
GO

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable 
REBUILD 
WITH 
(
    MAXDOP = 1, 
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO

SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;


-- 21009 pages used


ALTER TABLE [dbo].[SomeTable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = ROW);
GO


SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used


ALTER TABLE SomeTable ALTER COLUMN SomeColumn int;

-- Rebuild the heap, so that pages compact nicely
ALTER TABLE dbo.SomeTable 
REBUILD 
WITH 
(
    MAXDOP = 1, 
    ONLINE = OFF,
    FILLFACTOR = 100,
    PAD_INDEX = OFF
);
GO


SELECT SUM(page_count)
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID('SomeTable'),
        DEFAULT,
        DEFAULT,
        'detailed'
    ) AS ips;

-- 13587 pages used (same as bigint)

Best Answer

Related Solutions

PostgreSQL CREATE TABLE error

Effect of Changing Data Types with Row Compression Enabled in SQL Server

Related Question