Tag: NULL

How to allow NULL in foreign keys of a compound primary key

I want to have NULLs in foreign keys of a compound primary key. This is an example of what I would expect to be valid data.

product_id variant_id
123-123 ABC
123-123 NULL
456-456 ABC

I cannot figure out why the following SQL in postgres gives NOT NULL violation constraint me when inserting NULL as variant_id.

CREATE TABLE IF NOT EXISTS inventory.price (   product_id             UUID NOT NULL, -- this has to be always to a valid product   variant_id             UUID,          -- this could be NULL   amount                 MONEY NOT NULL,   created_at             TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),   -- Constraints   CONSTRAINT inventory_price_pkey PRIMARY KEY (product_id, variant_id),   CONSTRAINT inventory_price_inventory_product_fkey FOREIGN KEY (product_id)     REFERENCES inventory.product (id) MATCH FULL,   CONSTRAINT inventory_price_inventory_variant_fkey FOREIGN KEY (variant_id)     REFERENCES inventory.variant (id) MATCH SIMPLE,   CONSTRAINT inventory_price_amount_gt_0 CHECK (amount > '0'::money) );

And the inspection to information_schema confirms the non-nullable constraint.

column_name column_default is_nullable data_type
product_id NULL NO uuid
variant_id NULL NO uuid
amount NULL NO money
created_at now() NO timestamp with time zone

How to check if a column in a query can produce NULL?

I am scheming how to implement a type generator for arbitrary SQL queries.

I found that for any query, the simplest way to get the types of every column is to create a temporary table and then inspect that table. However, this tells nothing about which columns can be NULL.

I am interested to find a solution to identifying which columns can be NULL for any arbitrary query with the goal of generating types for that query.

What’s the way to go about it?

I am trying to convert my SQLite database into Postgres database, but I don’t know how to convert name TEXT (32) NOT NULL

CREATE TABLE integrals (    id SERIAL PRIMARY KEY NOT NULL UNIQUE,    name TEXT (32) NOT NULL,    ip TEXT (32),    posx REAL NOT NULL,    posy REAL NOT NULL,    port INTEGER,    image INT,    zones TEXT,    pass TEXT );

This is my SQLite generated database, and I am trying to convert it, but I am very new to Postgres.

get_comments with post_status ‘public’ retrieves NULL result

This is my loop:

<?php $  comments = get_comments(array(     'status' => 'approve',     'type' => 'comment',     'number' => 10,     'post_status' => 'public' )); ?>      <ul class="sidebar-comments">         <?php foreach ($  comments as $  comment) { ?>             <li>                 <div><?php echo get_avatar($  comment, $  size = '35'); ?></div>                 <em style="font-size:12px"><?php echo strip_tags($  comment->comment_author); ?></em> (<a href="<?php echo get_option('home'); ?>/?p=<?php echo ($  comment->comment_post_ID); ?>/#comment-<?php echo ($  comment->comment_ID); ?>">link</a>)<br>                 <?php echo wp_html_excerpt($  comment->comment_content, 35); ?>...             </li>         <?php } ?>     </ul>

This always gives an empty result (no errors). If I remove 'post_status' => 'public' from the get_comments arguments, the function works, comments load but also comments from private posts (which I don’t want).

Any ideas on why 'post_status' => 'public' is not working?

meta_query order by date present -> future then show null

Thanks for taking the time to read this. I’ve been struggling with a meta_query for an events site I’m working on.

I have used ACF to create a field for date start and date end, but not all events will have a date.

What I’m trying to achieve, is when you go to the archive or tax view, the first thing you see are the posts that have a date assigned, in order from today’s date into the future. Then after those dated events have been output, to cycle through all empty date posts.

So far I have the below in my functions.php file. This kind of works, but in the wrong order. So the correct events that are dated are output and in the right order. But only after the null valued items have output. I thought that may be because of the ordering in the arrays themselves, so moved the date ordered array to the end. That had no effect.

$  query->set( 'post_type', 'courses' ); $  query->set( 'meta_query', array(     'relation' => 'OR',     array(         'key'        => '_course_date_from',         'compare'    => '=',         'value'      => '',     ),     array(         'key'     => '_course_date_from',         'compare' => '>=',         'value'   => date('Ymd'),     ) ) ); $  query->set( 'orderby', 'meta_value title' ); $  query->set( 'order', 'ASC' );

Thanks for your time. Ben.

Null byte injection using JSON

I’m trying to make a chatroom for my university, It takes username in JSON, and then stores it in an array, then takes it to DB for keeping logs, but the thing is, that array also has a "status" key, whose value is set to guest my default, but is set to ADMIN if I log in or any member from my team logs in. I know that the idea of storing "status" with username is bad but I just started working on the project. I want to confirm that is it possible to inject NULL byte using username field via JSON and add another key with same name "status" to gain admin privileges??

Will Postgres use a multi-column index when setting multiple colums not null?

Typically, when I set a column not null, if it doesn’t have an index then I add it first, so that postgres can (hopefully) use the index while doing the table scan while locking the table, so that the table is locked for a shorter amount of time.

I want to set several columns not null, like so:

alter table foos   alter column bar1 set not null   alter column bar2 set not null   alter column bar3 set not null   alter column bar4 set not null;

If I make a multicolumn index for these columns, will postgres use it when scanning the locked table before making this alteration?

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4);

What if I made a partial index on IS NULL (or, IS NOT NULL)

CREATE INDEX CONCURRENTLY my_index on foos (bar1, bar2, bar3, bar4) where bar1 is null and bar2 is null and bar3 is null and bar4 is null;

How to create a Datum for database NULL in PostgreSQL a server side C function?

I was just wondering how to create a Datum for the database NULL value in PostgreSQL?

I known there is PG_RETURN_NULL() for returning a database NULL in a LANGUAGE C function. But I just want to create a Datum for NULL, e.g. to be stored in an array (or record). Should I just use (Datum) 0?

(This is with PostgreSQL 12)

MySQL trigger calling stored procedure always get null value for out parameter of stored procedure

My stored procedure OUT parameter, always return a null value.

Here is sample Table, Trigger and Procedure code.

Table: test
Columns:

  • id - Int

  • status - enum(‘pass’, ‘fail’)

    • status - enum(‘pass’, ‘fail’) (null is allowed)

Values in a table:

id  |  status 1   |  null

Trigger: 

create trigger BEFORE_UPDATE_TEST before update on `test` for each row begin      call Test_BEFORE_UPDATE_TEST(old.id, @updatedStatus);         ## I always get @updatedStatus null/nil      if (@updatedStatus is not null and @updatedStatus <> new.status) then         set new.status = @updatedStatus;     end if;  end;

Procedure: 

create procedure Test_BEFORE_UPDATE_TEST (   IN id int(5),   OUT status enum(‘pass’, ‘fail’) ) begin    @status = ‘pass’;  END;

What is wrong with this code, as I get unexpected result as null in the value @updatedStatus, which should be 'pass'.

I looked around following QAs on dba.stackexchange but could’t find solution.

I use MySQLWorkbench in MacOS Catalina and version of MySQL is 8.0.19.

add_action(‘the_content’, ‘my_plugin_content’) is null

I am creating in personal plugin where I recover data from a personal table. Con add_action(‘the_content’, ‘my_plugin_content’); posso far vedere il contenuto in una pagina. I would like to save the content in wp_posts with add_filter (‘save_post’, ‘my_plugin_content’), but the content is empty.

function my_plugin_content($  content){      $  current_page = $  wp->request;      include_once(plugin_dir_path( __FILE__ ).'views/view.php');      $  obj = new Loader;      $  content.=$  obj->controller($  current_page);      $  my_post = array();       $  my_post['ID'] = $  post->ID;      $  my_post['post_content']=$  cont; }

in another file I run the data recovery query that I refer to the file loader that loads the views

File loader

public function view($  view, array $  dati){       require(DIR_PLUGIN.'views/'.$  view.'_v.php'); }

File view

    <?php foreach($  dati as $  p){  echo '<p><strong>'.$  p->NAME.'</strong></p>; }?>