Why do the records in a Postgresql table keep blanks?

Question

Why do the records in a Postgresql table keep blanks?

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

Quetal friends, I'm doing a practice with PHP + PDO and postgresql, and I've realized that when I save the data it leaves me blank.

I attached an image

and this is the code.

<?php 
    include_once "conexion.php";
    try {  

      $pdo->beginTransaction();

      $result = $pdo->exec("insert into company (name, age, address, salary ) values (  'Juan Leo', '1','xxx','23.2')");


      if($result > 0){
        echo "Datos agregados! :)";
      }else{
        echo "No se agrego nada! :C";
      }

      $pdo->commit();

    } catch (Exception $e) {
      $pdo->rollBack();
      echo "Fallo: " . $e->getMessage();
    }

 ?>

and apparently in the name field, it saves it without spaces. apparently only in fields of type character Any suggestions, so that this does not happen? thanks

sql php pdo postgresql

asked by JuanL 30.06.2018 в 18:02

source

2 answers

1

I do not know postgresql but I've seen something similar in other implementations of SQL. It is by using the type character that will fill you with spaces until you reach the 50 characters that you have defined.

Use another type with variable length, in mysql we have varchar , for example.

answered by 30.06.2018 в 18:11

Send form with onClick. Only Javascript and HTML Can not read property 'length' of undefined JAVASCRIPT

score 3 · Accepted Answer

I will complete with something about PostgreSQL documentation, and in the process we have it as a source in Spanish.

In summary, it can be said that PostrgreSQL has three types of data for characters:

character varying : variable size with limit, you can use varchar as alias
character : fixed size, fill empty spaces blank, you can use char as alias.
text : unlimited variable size

The documentation says some interesting things about how it works each of these types of data and what advantages / disadvantages would have to use one or the other.

Here the textual translation:

SQL defines two main character types: character varying (n) and character (n) , where n is a positive integer. Both of them   types can store strings up to n characters (not bytes) of   length. An attempt to store a longer string in a column   of these types will result in an error, unless the   excess characters are all spaces, in which case the string is   will truncate to the maximum length. (This somewhat strange exception is   required by the SQL standard). If the chain to be stored   is shorter than the declared length, character values   they will be spaced; the variable type character values   they will simply store the shortest chain.

If one explicitly converts a value to character varying(n) or    character(n) , then an excessive length value will be truncated in   n characters without generating an error. (This is also required by the   standard SQL).

The notations varchar (n) and char (n) are aliases for character varying(n) and character(n) , respectively. character without   length specifier is equivalent to character (1) . Whether   uses character varying without the length specifier, the type   accepts chains of any size The latter is an extension of   PostgreSQL.

In addition, PostgreSQL provides the type text , which stores   chains of any length. Although the type text is not in the   SQL standard, so do other management systems.   SQL databases.

The values of type character are physically filled with   spaces with the width n specified, and are stored and displayed   that way. However, fill spaces are treated as   semantically insignificant. The final spaces are not taken into   account when comparing two values of type character , and they will be eliminated at   convert a value of character to one of the other chain types.   Note that the final spaces are semantically   significant in the values of type character varying and in the   values of text , and when pattern matching is used, for   example LIKE , regular expressions, etc.

The storage requirement for a short chain (up to 126 bytes)   is 1 byte plus the real string, which includes the space fill in   the case of character . The longest strings have 4 bytes of   overload instead of 1. Long chains are compressed by the   system automatically, so the physical requirement on the disk   It can be less. Very long values are also stored in tables   so that they do not interfere with fast access to values of   shorter column. In any case, the string of characters   as long as possible that can be stored is approximately 1 GB. (The   maximum value that will be allowed for n in the declaration of the type of   data is less than that. It would not be useful to change this because with the   Multibyte character encodings the number of characters and bytes   It can be quite different. If you want to store long strings without   specific upper limit, use text or character varying without a   length specifier, instead of compensating a length limit   arbitrary).

Advice : There is no difference in performance between these three types, apart from more storage space when using the   type of blank fill, and some additional CPU cycles for   Verify the length when stored in a length column   limited Although character (n) has performance advantages in   some other database systems, there is no such advantage in   PostgreSQL; in fact, the character (n) is usually the slowest of   All three due to their additional storage costs . In the   In most situations, you should use text or character varying   instead.