PostgreSQL - NULL display issue

klobinoid
2013-05-09
2013-06-06
  • klobinoid
    klobinoid
    2013-05-09

    Hi Jakub,

    I'm having strange issue with PostgreSQL. I have table defined with usual NULL and NOT NULL columns - usual business. However, Adminer is displaying these columns reversely. NOT NULL column displayed as NULL and vice versa.

    Table definition (via dump):

    CREATE TABLE membership_type (
        id integer NOT NULL,
        code character varying(10) NOT NULL,
        description character varying(100),
        price money
    );

    It's representation in Adminer:

    Is it a bug or am I missunderstanding the visual representation?

    Adminer - 3.6.4
    Postgre - 9.2

    Thanks,
    Petr

     
  • Jakub Vrána
    Jakub Vrána
    2013-05-09

    I couldn't reproduce it, NULL is correctly displayed next to the description column for me.

    The screenshot doesn't correspond to the SQL command (there's no auto increment). Can you try to recreate the table with the provided command?

     
  • klobinoid
    klobinoid
    2013-05-10

    You are right, the table was not created with that command. The table in screenshot was created from binary backup. However, the command was made with pg_dump of this database. Therefore it looks like the definition inside Postgre is right.

    The whole command should be:

    • Name: membership_type; Type: TABLE; Schema: public; Owner: -; Tablespace:

    CREATE TABLE membership_type (
        id integer NOT NULL,
        code character varying(10) NOT NULL,
        description character varying(100),
        price money
    );

    • Name: membership_type_id_seq; Type: SEQUENCE; Schema: public; Owner: -

    CREATE SEQUENCE membership_type_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

    I have tried run these two in a new database, created via Adminer with this result:

    Also, when I check this new table with pgAdmin, column definitions are OK (id NOT NULL etc.)

     
  • klobinoid
    klobinoid
    2013-05-10

    Edit: there's one more command assigning sequence to id column.
    ALTER SEQUENCE membership_type_id_seq OWNED BY membership_type.id;

    But visual representation of the table stays the same as in my previous post.

     
  • klobinoid
    klobinoid
    2013-05-10

    Final edit:
    Ok, I've gone through the whole dump, and these are the commands manipulating with membership type:

    CREATE TABLE membership_type (
        id integer NOT NULL,
        code character varying(10) NOT NULL,
        description character varying(100),
        price money
    );

    CREATE SEQUENCE membership_type_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1;

    ALTER SEQUENCE membership_type_id_seq OWNED BY membership_type.id;

    ALTER TABLE ONLY membership_type ALTER COLUMN id SET DEFAULT nextval('membership_type_id_seq'::regclass);

    ALTER TABLE ONLY membership_type
        ADD CONSTRAINT membership_type_pkey PRIMARY KEY (id);

    And this the representation when run in a new database:

    The original problem however stays the same - still displaying the NULL definitions reversely.

     
  • Jakub Vrána
    Jakub Vrána
    2013-05-11

    Can you please check the contents of the field attnotnull in pg_attribute table? The query could be something like:

    SELECT "attnotnull" FROM "pg_attribute" WHERE "attname" = 'code'
    
     
  • klobinoid
    klobinoid
    2013-05-13

    Result of your query in my database is 1.

     
  • Jakub Vrána
    Jakub Vrána
    2013-05-13

    Interesting. It should be t.

     
  • klobinoid
    klobinoid
    2013-05-13


    Version string: PostgreSQL 9.2.3, compiled by Visual C++ build 1600, 64-bit

    There's a new version out - 9.2.4. Should I check if updating to latest version will help?

     
  • Jakub Vrána
    Jakub Vrána
    2013-05-13

    I've found someone having a similar problem on Stack Overflow. There's no solution though. I'll just cast it to int to circumvent this problem.

     
  • Jakub Vrána
    Jakub Vrána
    2013-05-13

    Fixed in Git.

     
  • klobinoid
    klobinoid
    2013-05-13

    Ok, thank you for your time.