Question 89405 in PostgreSQL Question posted in
The official documentation can be found here.

Postgres string truncation at the table-level schema – Postgresql

I have a table in Postgres with the following schema:

create table if not exists cars
(
  id bigserial not null,
  tyre_code varchar(8)
);

The tyre_code in the table should ideally not be more than 8 characters in length. But, in the case that someone does provide a string that is greater than 8 characters, is there a way at the table-level schema in Postgres to truncate the value of this string and insert it? The other option I have is to truncate the value in the Java code and then insert it. However, that is not ideal, and would prefer is I can do this at the table level itself.

For example:
tyre_code provided - ABCDEFGHIJK
tyre_code after truncation - ABCDEFGH

I am using Postgres, with Spring and Hibernate. Any help will be greatly appreciated! Thank you!

Tags:

2

Answers


  1. 0

    With your existing table definition OR after adding check constraints for length; If you try to insert column value more than 8 characters;
    It shall throw you error as :

    "value too long"

    But;

    You can achieve it using : BEFORE INSERT trigger as follows :

    CREATE OR REPLACE FUNCTION truncate_tyre_code()
RETURNS TRIGGER AS $$
BEGIN
    NEW.tyre_code = LEFT(NEW.tyre_code, 8);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER truncate_tyre_code_trigger
BEFORE INSERT ON cars
FOR EACH ROW
EXECUTE FUNCTION truncate_tyre_code();

    ===============================================================
    Also if you are looking for adding constraints to check column length in postgresql; Then you may try : CHECK constraint

    CREATE TABLE IF NOT EXISTS cars (
  id bigserial NOT NULL,
  tyre_code varchar(8) NOT NULL,
  CONSTRAINT truncated_tyre_code CHECK (LENGTH(tyre_code) <= 8)
);

    Here is a working demo using DBFIDDLE

    In more simplified way as :

    CREATE TABLE IF NOT EXISTS cars (
  id bigserial PRIMARY KEY,
  tyre_code varchar(8) CHECK (LENGTH(tyre_code) <= 8) NOT NULL
);

    Here is a working demo

    Assuming you already have a table structure as follows :

    create table if not exists cars
(
  id bigserial not null,
  tyre_code varchar(8)
);

    To add an extra constraint in alter table statement.

    ALTER TABLE cars ADD CONSTRAINT truncated_tyre_code CHECK (LENGTH(tyre_code) <= 8);

    ALTER (EXISTING) TABLE DEMO

    Login or Signup to reply.
  2. 0

    Define the column as type text (no length limit) with a check constraint that enforces the length. Then use a BEFORE trigger to truncate the value:

    CREATE TABLE varlen (
   id integer PRIMARY KEY,
   val text CHECK (length(val) <= 8) NOT NULL
);

CREATE FUNCTION trunc_val() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   NEW.val := left(NEW.val, 8);
   RETURN NEW;
END;$$;

CREATE TRIGGER trunc_val BEFORE INSERT ON varlen
   FOR EACH ROW EXECUTE FUNCTION trunc_val();

INSERT INTO varlen VALUES (1, 'a string that exceeds 8 characters');

TABLE varlen;

 id │   val    
════╪══════════
  1 │ a string
(1 row)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search