My intuition is NULL will take up less space than {} when used as the default value of an ARRAY[] column in PostgreSQL.  Am I correct?
123651
I have 400 million rows in a Postgres db, and the table has 18 columns:  id serial NOT NULL, a integer, b integer, c integer, d smallint, e timestamp without time zone, f smallint, g timestamp without time zone, h integer, i timestamp without time zone, j integer, k character varying(32), l integer, m smallint, n smallint, o character varying(36), p character varying(100), q character varying(100)   Columns e, k, and n are all NULL, they do not store any values at all and are completely useless at this point. They were part of the original design, but never removed.  Edit - most of the other columns are non-NULL.  Questions:   How can I calculate the impact this has on storage? Is it equal to size of column * # of rows ?  Will dropping these empty columns noticeably improve performance for this table? Would the page cache be able to fit more rows?
87267
I am trying to check how the table size is going to be increased after adding new column with default values.  I use the following default values: NULL, ''(empty string) and some text(some random text)` but it seems the table size is not changed.   I am using the following statements:  EXEC sp_spaceused '[Table_A]';  ALTER TABLE [dbo].[Table_A] ADD [Loops] NVARCHAR(900) CONSTRAINT [DF_Loops] DEFAULT('XXX') WITH VALUES;  EXEC sp_spaceused '[Table_A]';  ALTER TABLE [dbo].[Table_A] DROP CONSTRAINT [DF_Loops]  ALTER TABLE [dbo].[Table_A] DROP COLUMN [Loops];   but the sp_spaceused always returns:  name                                               rows                 reserved           data               index_size         unused ------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------ Table_A                                            73540994             8701688 KB         6658544 KB         2042976 KB         168 KB   Could anyone tell what I am doing wrong?
124212
I'm using the MySQL Query Browser (part of the ) and need to change a field to NULL, but I can't figure out how to do it - if I delete the value it tries to update it to ''. Typing "NULL" makes it try to update to 'NULL' (a string).  I know I could just write a query to do it, but that defeats the entire purpose of the tool, no?
71212
When we use an argument length with numerical data types, as far as I know this specifies the display width. I tried the following:    mysql&gt; create table boolean_test (var1 boolean, var2 tinyint);     Query OK, 0 rows affected (0.10 sec)     mysql&gt; show create table boolean_test;    +--------------+-------------------------    | Table        | Create Table +--------------+-------------------------   | boolean_test | CREATE TABLE `boolean_test` (     `var1` tinyint(1) DEFAULT NULL,     `var2` tinyint(4) DEFAULT NULL   ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |    +--------------+---------------------------   1 row in set (0.00 sec)     First of all tinyint is a 1 byte value. So what is the meaning of tinyint(4)? There can not be 4 digits.    mysql&gt; insert into boolean_test values(101,112);   Query OK, 1 row affected (0.03 sec)     mysql&gt; select * from boolean_test;   +------+------+   | var1 | var2 |   +------+------+   |   10 |  112 |   |  101 |  112 |   +------+------+   2 rows in set (0.00 sec)     I see that in tinyint I stored a 10 and 101 and I could get back these values despite that it is defined as tinyint(1). Shouldn't I see a 1 for var1? I.e. just 1 display digits?
98711
When I try to access a JSON element using -&gt;&gt;'elementName' I get a texttype.  SELECT pg_typeof(x1-&gt;&gt;'a'), jsonb_typeof(x2) FROM ( VALUES   ('{"a":5}'::jsonb, '5'::jsonb) ) AS t(x1,x2);   pg_typeof | jsonb_typeof  -----------+--------------  text      | number (1 row)   However, ...     When converting textual JSON input into jsonb, the primitive types described by RFC 7159 are effectively mapped onto native PostgreSQL types, as shown in Table 8-23.   Here is the table reproduced from the docs,  Table 8-23. JSON primitive types and corresponding PostgreSQL types  JSON primitive type     PostgreSQL type  Notes string                  text             \u0000 is disallowed, as are non-ASCII Unicode escapes if database encoding is not UTF8 number                  numeric          NaN and infinity values are disallowed boolean                 boolean          Only lowercase true and false spellings are accepted null                    (none)           SQL NULL is a different concept
50025
I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.  I need something like  SELECT * FROM table AS t WHERE ANY(t.* IS NULL)   I don't want to have to do  SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL   This would be a HUGE query.
66221
This is a question about the inner-workings of Postgres (v10) and performance.  Given a table, github_repos, with a multi-column unique index on org_id and github_id columns, is there any performance difference (or other issues to be aware of) between the two bulk upsert operations below? The difference is that in the first query, the org_id and github_id columns are included in the UPDATE, whereas in the second query they are not. Since the UPDATE runs ON CONFLICT, the updated values of org_id and github_id will be the same as the old values, but those columns are included in the UPDATE because the underlying library I am using is designed that way. I'm wondering if its safe to use as-is or whether I should be explicitly excluding those columns in the UPDATE.  Query #1:  INSERT INTO "github_repos" ("org_id","github_id","name") VALUES (1,1,'foo') ON CONFLICT (org_id, github_id) DO UPDATE SET "org_id"=EXCLUDED."org_id","github_id"=EXCLUDED."github_id","name"=EXCLUDED."name" RETURNING "id"   Query #2:  INSERT INTO "github_repos" ("org_id","github_id","name") VALUES (1,1,'foo') ON CONFLICT (org_id, github_id) DO UPDATE SET "name"=EXCLUDED."name" RETURNING "id"   github_repos table:        Column       |       Type        | Collation | Nullable  -------------------+-------------------+-----------+----------+  id                | bigint            |           | not null |  org_id            | bigint            |           | not null |  github_id         | bigint            |           | not null |  name              | character varying |           | not null |  Indexes:     "github_repos_pkey" PRIMARY KEY, btree (id)     "unique_repos" UNIQUE, btree (org_id, github_id)
70814
I'm having difficulty finding 'lay' explanations of how indexes are cached in PostgreSQL, so I'd like a reality check on any or all of these assumptions:   PostgreSQL indexes, like rows, live on disk but may be cached. An index may be entirely in the cache or not at all. Whether it is cached or not depends on how often it is used (as defined by the query planner). For this reason most 'sensible' indexes are going to be in the cache all the time. The indexes live in the same cache (the buffer cache?) as rows, and therefore cache space used by an index is not available to rows.    My motivation for understanding this follows on from  where it was suggested that partial indexes can be used on tables where a majority of the data will never be accessed.  Before undertaking this, I'd like to be clear that employing a partial index yields two advantages:   We reduce the size of the index in the cache, freeing up more space for rows themselves in the cache. We reduce the size of the B-Tree, resulting in a faster query response.
1431
Foo* set = new Foo[100]; // ... delete [] set;   You don't pass the array's boundaries to delete[]. But where is that information stored? Is it standardised?
105548
I make a new column in my attribute table and it has a default value (for example 0). I want to (probably using the field calculator) have the result that row 1 has the value 1, row 2=2 and so on, just like a numbered index.  I'd be thankful for a code example for the field calculator (including the use of $rownum (I think I need this for switching to the line)).
28022
I know that it does consider ' ' as NULL, but that doesn't do much to tell me why this is the case.  As I understand the SQL specifications, ' ' is not the same as NULL -- one is a valid datum, and the other is indicating the absence of that same information.  Feel free to speculate, but please indicate if that's the case.  If there's anyone from Oracle who can comment on it, that'd be fantastic!
63419
I'm working with ArcGIS 10.2 for Desktop and OS Windows 7.   I need to set numeric vales from Zero back to NULL, but I can't from the field calculator.  I'm working with  a vector file and I need to display NULL in one column that shows 0 (Zero) as default value.  -------- Edit------- I am adding the error messages that he received  Translation:  An error occurred during the process.  Please consult the Geoprocessing Results Window to obtain more details.  Error at bottom, translation:  The values ​​of the selected column can not be set to null because the field in the database is defined as "Not Null".
91753
In a mysqli prepared statement, a NULL gets turned into '' (in the case of a string) or 0 (in the case of an integer). I would like to store it as a true NULL.  Is there any way of doing this?
69820
In a  about formatting a double[][] to CSV format,  that using StringBuilder would be faster than String.Join. Is this true?
22399
Is it possible to change the natural order of columns in Postgres 8.1?  I know that you shouldn't rely on column order - it's not essential to what I am doing - I only need it to make some auto-generated stuff come out in a way that is more pleasing, so that the field order matches all the way from pgadmin through the back end and out to the front end.
81341
I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values.  Something like that:  CREATE OR REPLACE FUNCTION add_array(     array_received json[])   RETURNS void AS $BODY$      update table set _array_field = _array_field | array_received ...;  $BODY$   LANGUAGE plpgsql VOLATILE;
71438
I am always puzzled regarding some mysterious t-sql behavior, like the following  -- Create table t and insert values.   use tempdb CREATE TABLE dbo.t (a INT NULL);   -- insert 3 values INSERT INTO dbo.t values (NULL),(0),(1);   GO   set ansi_nulls off -- purposely turn off, so we can allow NULL comparison, such as null = null go -- expect 3 rows returned but only 2 returned (without null value row) select * from dbo.t where a = a    This is not about how to retrieve all rows in a table and also not about avoiding use of ANSI_NULLS.   I just want to solicit some insights why t-sql behaves like this.