Postgresql Text Fields get '' when they are nulls

Question

I have a table called art_movimientos

CREATE TABLE public.art_movimientos
(
  cmovimiento bigint NOT NULL DEFAULT nextval('art_movimientos_cmovimiento_seq'::regclass),
  tipo character varying(3) NOT NULL, -- Tipos de Valores:...
  fecha_mov timestamp without time zone NOT NULL,
  documento integer NOT NULL,
  control integer,
  fecha_doc timestamp without time zone NOT NULL,
  corden integer NOT NULL DEFAULT 0,
  calmacen integer NOT NULL,
  calmacen2 integer,
  status character varying(13) NOT NULL DEFAULT 'PENDIENTE'::bpchar, -- PENDIENTE...
  donado integer NOT NULL DEFAULT 0,
  monto_mov numeric(11,2) NOT NULL DEFAULT 0.00,
  monto_desc numeric(11,2) NOT NULL DEFAULT 0.00,
  monto_total numeric(11,2) NOT NULL DEFAULT 0.00,
  observacion text,
  casiento integer,
  crea_user character varying(25),
  crea_date timestamp without time zone,
  mod_user character varying(25),
  mod_date timestamp without time zone,
  cproveedor integer NOT NULL DEFAULT 0,
  CONSTRAINT pk_cmovimiento_art_movimientos PRIMARY KEY (cmovimiento)
  USING INDEX TABLESPACE sistema_index,
  CONSTRAINT fk_calmacen_art_movimientos FOREIGN KEY (calmacen)
      REFERENCES public.almacen (calmacen) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT ck_donado_art_movimientos CHECK (donado = ANY (ARRAY[0, 1])),
  CONSTRAINT ck_monto_total_art_movimientos CHECK (monto_total > 0::numeric AND monto_total >= monto_mov),
  CONSTRAINT ck_status_art_movimientos CHECK (status::text = ANY (ARRAY['PENDIENTE'::character varying, 'PROCESADO'::character varying, 'APROBADO'::character varying, 'CONTABILIZADO'::character varying]::text[])),
  CONSTRAINT ck_tipo_art_movimientos CHECK (tipo::text = ANY (ARRAY['AJE'::character varying, 'AJS'::character varying, 'SI'::character varying, 'CSM'::character varying, 'COI'::character varying, 'COM'::character varying, 'DEV'::character varying, 'TRF'::character varying]::text[]))
)
WITH (
  OIDS=FALSE
);

The Controller get the data and it convert in array to pass to the class:

...    
$_obs =$_POST['observacion']<>'' ? strtoupper($_POST['observacion']) : $_POST['observacion'];
....
array_push($values, $_obs);
...
$mov_dat = $mov->new_mov($values);

The DB Class have the function, it call another function and return the result:

    public function new_mov($array){
            return $mov = $this->db->procedure("insert_art_mov",$array);
        }
...
public function procedure($procedure=false,$rows=false){
        $response=array();
        if($procedure==false){
            $response="Query is Empty!";
        }else{
            $sql = "SELECT ".$procedure."(";
            for ($i=1; $i<=count($rows); $i++){
                $sql .= "$".$i.",";
            }
            $sql = substr($sql,0,-1);
            $sql .= ")";
            //echo $sql;
            $res = pg_prepare($this->linkid, "my_query", $sql);
            $res = @pg_execute($this->linkid, "my_query", $rows);
            if($res){
                while($consF=pg_fetch_assoc($res))
                    array_push($response,$consF[$procedure]);
            }else{
                array_push($response,"ERROR");
                array_push($response,$this->validateOperation());
            }
            $sql = sprintf('DEALLOCATE "%s"',pg_escape_string("my_query"));
             if(!pg_query($sql)) {
                die("Can't query '$sql': " . pg_last_error());
             }
            return $response;
        }
    }

Then i execute the Procedure in the DB:

CREATE OR REPLACE FUNCTION public.insert_art_mov(...IN _observacion text,..)
INSERT INTO art_movimientos(tipo,fecha_mov,documento,control,fecha_doc,corden,cproveedor,calmacen,status,donado,monto_mov,monto_desc,monto_total,observacion,crea_user)
            VALUES(_tipo,NOW(),_documento,_control,_fecha_doc,_corden,_cprov,_calmacen,'PENDIENTE',_donado,MONTO_MOV,_monto_desc,MONTO_TOT,_observacion,_crea_user) RETURNING cmovimiento INTO new_cmov;

But in the table in observacion i see '' (simple cuotes) and i wanna know why?

CAllen · Accepted Answer · 2017-03-31 16:22:27Z

1

You will get those single quotes because you do not check for empty strings. What I would recommend and I do this as well is that for every string field pass it to a function to return either data or NULL.

DICLAIMER: Half answer but mostly a guide :)

For the backend, you can try the below function when there is a string that needs to be added:

function chech_string ($str) {
    return (0 != strlen($str)) ? $str : NULL;
}

Or you create your sql string as follow

$sql = "INSERT INTO table(col1, col2) VALUES (';

if (0 != strlen($str1))
    $sql .= "'$str1'";
else
    $sql .= "NULL";

if (0 != strlen($str2))
    $sql .= "'$str2'";
else
    $sql .= "NULL";

    $sql .= ')';

At the end your string would look like

$sql = "INSERT INTO table(col1, col2) VALUES ('STR1', 'STR2')";
OR
$sql = "INSERT INTO table(col1, col2) VALUES ('STR1', NULL)";

So you can use this mething to create you procedure as well

For the front end:

<input type="text" value="<?php echo (0 != strlen($col_val)) ? $col_val : '' ?>"/>

that will give you the result you need in the front end

edited Mar 31, 2017 at 16:22

answered Mar 31, 2017 at 15:58

CAllen

8265 silver badges14 bronze badges

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

6 Comments

JuJoGuAl Over a year ago

But i dont wanna get NULLS or simple quotes ('') just a field empty

CAllen Over a year ago

Is your database returning NULLS or empty strings? Because in postgresql is can be stored as an emoty string but in either cas you can use the same logic in the front end as well, value='<?php echo (0 != strlen($col_val)) ? $col_val : '' ?>' that will give you the result you need in the front end

JuJoGuAl Over a year ago

Any of both, my Front end, shows me nothing (empty values) but if there a query that I need to do, for select the "observaciones" without data? SELECT * FROM art_movimientos WHERE observaciones = '' ?

CAllen Over a year ago

If you say that it is null, the you must use SELECT * FROM art_movimientos WHERE observaciones IS NULL;

CAllen Over a year ago

Your welcome and no you don't but if there are ways to make it cleaner then it would be good practice :) Glad I can help. You got this :) keep it up mein

|

Collectives™ on Stack Overflow

Postgresql Text Fields get '' when they are nulls

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related