Rectangle 27 8

Creating a table with text columns

Basically you should prepare the data (including its structure) outside the database, with ready-made tools or using python, ruby or a language of your choice. However, in the lack of such opportunities you can do much using plpgsql.

Files in csv format do not contain any information about column types, primary or foreign keys etc. You can relatively easily create a table with text columns and copy data to it. After that you should manually alter types of columns and add constraints.

c:\data\test.csv
id,a_text,a_date,a_timestamp,an_array
1,str 1,2016-08-01,2016-08-01 10:10:10,"{1,2}"
2,str 2,2016-08-02,2016-08-02 10:10:10,"{1,2,3}"
3,str 3,2016-08-03,2016-08-03 10:10:10,"{1,2,3,4}"
select import_csv('c:\data\test.csv', 'new_table');

select * from new_table;

 id | a_text |   a_date   |     a_timestamp     | an_array  
----+--------+------------+---------------------+-----------
 1  | str 1  | 2016-08-01 | 2016-08-01 10:10:10 | {1,2}
 2  | str 2  | 2016-08-02 | 2016-08-02 10:10:10 | {1,2,3}
 3  | str 3  | 2016-08-03 | 2016-08-03 10:10:10 | {1,2,3,4}
(3 rows)

The above function imports data two times (to temporary and target tables). For large files this may be a serious loss of time and unnecessary load on the server. A solution would be splitting a csv file into two files, one with header and one with data. Then the function should look like this:

create or replace function import_csv(header_file text, data_file text, table_name text)
returns void language plpgsql as $$
begin
    create temp table import (line text) on commit drop;
    execute format('copy import from %L', header_file);

    execute format('create table %I (%s);', 
        table_name, concat(replace(line, ',', ' text, '), ' text'))
    from import;

    execute format('copy %I from %L (format csv)', table_name, data_file);
end $$;

You can try to automatically change the column types based on their content. You can succeed if you are dealing with simple types and data in the file consistently retains a specific format. However, in general it is a complex task and functions listed below should be considered only as an example.

Determine a column type based on its content (edit the function to add desired conversions):

create or replace function column_type(val text)
returns text language sql as $$
    select 
        case 
            when val ~ '^[\+-]{0,1}\d+$' then 'integer'
            when val ~ '^[\+-]{0,1}\d*\.\d+$' then 'numeric'
            when val ~ '^\d\d\d\d-\d\d-\d\d$' then 'date'
            when val ~ '^\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d$' then 'timestamp'
        end
$$;

Alter column types using the above function:

create or replace function alter_column_types(table_name text)
returns void language plpgsql as $$
declare
    rec record;
    qry text;
begin
    for rec in
        execute format(
            'select key, column_type(value) ctype
            from (
                select row_to_json(t) a_row 
                from %I t 
                limit 1
            ) s, json_each_text (a_row)',
            table_name)
    loop
        if rec.ctype is not null then
            qry:= format(
                '%salter table %I alter %I type %s using %s::%s;', 
                qry, table_name, rec.key, rec.ctype, rec.key, rec.ctype);
        end if;
    end loop;
    execute(qry);
end $$;
select alter_column_types('new_table');

\d new_table

               Table "public.new_table"
   Column    |            Type             | Modifiers 
-------------+-----------------------------+-----------
 id          | integer                     | 
 a_text      | text                        | 
 a_date      | date                        | 
 a_timestamp | timestamp without time zone | 
 an_array    | text                        |

(well, proper recognition of array types is quite complicated)

How to generate a schema from a CSV for a PostgreSQL Copy - Stack Over...

postgresql csv schema postgresql-copy
Rectangle 27 2

Summarize all NULLs of a table

In order to count NULL values for all columns of a table T you could run

SELECT COUNT(*) - COUNT(col1) col1_nulls
     , COUNT(*) - COUNT(col2) col2_nulls
     ,..
     , COUNT(*) - COUNT(colN) colN_nulls
     , COUNT(*) total_rows
FROM   T
/

Where col1, col2, .., colN should be replaced with actual names of columns of T table.

Aggregate functions -like COUNT()- ignore NULL values, so COUNT(*) - COUNT(col) will give you how many nulls for each column.

If you want to know how many fields are NULL, I mean every NULL of every record you can

WITH d as (    
    SELECT COUNT(*) - COUNT(col1) col1_nulls
         , COUNT(*) - COUNT(col2) col2_nulls
         ,..
         , COUNT(*) - COUNT(colN) colN_nulls
         , COUNT(*) total_rows
    FROM   T
) SELECT col1_nulls + col1_nulls +..+ colN_null
  FROM d 
/

Following is an improvement in which you need to now nothing but table name and it is very easy to code a function based on it

DECLARE
  T    VARCHAR2(64) := '<YOUR TABLE NAME>';
  expr VARCHAR2(32767);
  q    INTEGER;
BEGIN
  SELECT 'SELECT /*+FULL(T) PARALLEL(T)*/' || COUNT(*) || ' * COUNT(*) OVER () - ' || LISTAGG('COUNT(' || COLUMN_NAME || ')', ' + ') WITHIN GROUP (ORDER BY COLUMN_ID) || ' FROM ' || T
  INTO   expr
  FROM   USER_TAB_COLUMNS
  WHERE  TABLE_NAME = T;

  -- This line is for debugging purposes only
  DBMS_OUTPUT.PUT_LINE(expr);

  EXECUTE IMMEDIATE expr INTO q;

  DBMS_OUTPUT.PUT_LINE(q);
END;
/

Due to calculation implies a full table scan, code produced in expr variable was optimized for parallel running.

CREATE OR REPLACE FUNCTION null_fields(table_name IN VARCHAR2, owner IN VARCHAR2 DEFAULT USER)
  RETURN INTEGER IS
  T    VARCHAR2(64) := UPPER(table_name);
  o    VARCHAR2(64) := UPPER(owner);
  expr VARCHAR2(32767);
  q    INTEGER;
BEGIN
  SELECT 'SELECT /*+FULL(T) PARALLEL(T)*/' || COUNT(*) || ' * COUNT(*) OVER () - ' || listagg('COUNT(' || column_name || ')', ' + ') WITHIN GROUP (ORDER BY column_id) || ' FROM ' || o || '.' || T || ' t'
  INTO   expr
  FROM   all_tab_columns
  WHERE  table_name = T;

  EXECUTE IMMEDIATE expr INTO q;

  RETURN q;
END;
/

-- Usage 1
SELECT null_fields('<your table name>') FROM dual
/

-- Usage 2
SELECT null_fields('<your table name>', '<table owner>') FROM dual
/

sql - Count the number of null values into an Oracle table? - Stack Ov...

sql oracle plsql
Rectangle 27 10

Hide columns when the table is initialized

The previous answers are using legacy DataTables syntax. In v 1.10+, you can use column().visible():

var dt = $('#example').DataTable();
//hide the first column
dt.column(0).visible(false);

To hide multiple columns, columns().visible() can be used:

var dt = $('#example').DataTable();
//hide the second and third columns
dt.columns([1,2]).visible(false);

To hide columns when the table is initialized, you can use the columns option:

$('#example').DataTable( {
    'columns' : [
        null,
        //hide the second column
        {'visible' : false },
        null,
        //hide the fourth column
        {'visible' : false }
    ]
});

For the above method, you need to specify null for columns that should remain visible and have no other column options specified. Or, you can use columnDefs to target a specific column:

$('#example').DataTable( {
    'columnDefs' : [
        //hide the second & fourth column
        { 'visible': false, 'targets': [1,3] }
    ]
});

It could also be done with columns.visible option. If you want your answer to be thorough, I would mention that as well.

columns,visible

@Gyrocode.com - aha, yes. I was thinking dynamic visibility. I've added detail about hiding columns on init.

javascript - jquery datatables hide column - Stack Overflow

javascript jquery datatables
Rectangle 27 478

You need to set the cell width (either th or td worked, I set both) AND set the table-layout to fixed. For some reason, the cell width seems to only stay fixed if the table width is set, too (i think that's silly but whatev). Also useful is setting the overflow property to hidden.

You should make sure to leave all of the bordering and sizing for CSS, too.

<table>
   <tr>
      <th>header 1</th>
      <th>header 234567895678657</th>
   </tr>
   <tr>
      <td>data asdfasdfasdfasdfasdf</td>
      <td>data 2</td>
   </tr>
</table>
table{
    border: 1px solid black;
    table-layout: fixed;
    width: 200px;
}

th, td {
    border: 1px solid black;
    width: 100px;
}

Its important to notice this: "The browser will then set column widths based on the width of cells in the first row of the table", from stackoverflow.com/questions/570154/

It does work when the table width is not fixed. jsfiddle.net/lavinski/CGCFW/3 You just need a dynamic row to take up the remaining space.

@DanielLittle alternatively you can set the table width to 1px; with overflow: visible for tables of dynamic size, as long as the size of the cells is fixed and overflow is visible it doesn't matter if the size of the table itself is bigger or smaller than the actual cells.

overflow: hidden;
td

html - Set the table column width constant regardless of the amount of...

html css column-width
Rectangle 27 470

You need to set the cell width (either th or td worked, I set both) AND set the table-layout to fixed. For some reason, the cell width seems to only stay fixed if the table width is set, too (i think that's silly but whatev). Also useful is setting the overflow property to hidden.

You should make sure to leave all of the bordering and sizing for CSS, too.

<table>
   <tr>
      <th>header 1</th>
      <th>header 234567895678657</th>
   </tr>
   <tr>
      <td>data asdfasdfasdfasdfasdf</td>
      <td>data 2</td>
   </tr>
</table>
table{
    border: 1px solid black;
    table-layout: fixed;
    width: 200px;
}

th, td {
    border: 1px solid black;
    width: 100px;
}

Its important to notice this: "The browser will then set column widths based on the width of cells in the first row of the table", from stackoverflow.com/questions/570154/

It does work when the table width is not fixed. jsfiddle.net/lavinski/CGCFW/3 You just need a dynamic row to take up the remaining space.

@DanielLittle alternatively you can set the table width to 1px; with overflow: visible for tables of dynamic size, as long as the size of the cells is fixed and overflow is visible it doesn't matter if the size of the table itself is bigger or smaller than the actual cells.

overflow: hidden;
td

html - Set the table column width constant regardless of the amount of...

html css column-width
Rectangle 27 91

SQL> create table a(id number);

Table created.

SQL> alter table a rename column id to new_id;

Table altered.

SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 NEW_ID                                             NUMBER

had to use COLUMN keyword before the column name.

oracle10g - How to rename a table column in Oracle 10g - Stack Overflo...

oracle oracle10g
Rectangle 27 109

After the table tag, use the col element. you don't need a closing tag.

<table>
  <colgroup>
    <col style="width:40%">
    <col style="width:30%">
    <col style="width:30%">
  </colgroup>  
  <tbody>
    ...
  </tbody>
</table>

This! This is the HTML5 answer and it freaking works without my having to jump through stupid hoops. Plus you can use <col class="someClassName"> instead to keep your widths in CSS and not pollute your HTML with style info.

@Sam you may have had some other issue overriding this such as CSS, inline style, or incorrect doctype etc.. This definitely works, its the standard way to set column styles.

table-layout: fixed; width: 100%;

html - Set the table column width constant regardless of the amount of...

html css column-width
Rectangle 27 107

After the table tag, use the col element. you don't need a closing tag.

<table>
  <colgroup>
    <col style="width:40%">
    <col style="width:30%">
    <col style="width:30%">
  </colgroup>  
  <tbody>
    ...
  </tbody>
</table>

This! This is the HTML5 answer and it freaking works without my having to jump through stupid hoops. Plus you can use <col class="someClassName"> instead to keep your widths in CSS and not pollute your HTML with style info.

@Sam you may have had some other issue overriding this such as CSS, inline style, or incorrect doctype etc.. This definitely works, its the standard way to set column styles.

table-layout: fixed; width: 100%;

html - Set the table column width constant regardless of the amount of...

html css column-width
Rectangle 27 17

alter table table_name rename column oldColumn to newColumn;

Any ideas when i need to rename from 'Employee Name' to 'Employee_name' . (Exisiting column name with space)

oracle10g - How to rename a table column in Oracle 10g - Stack Overflo...

oracle oracle10g
Rectangle 27 132

You can query the USER_TAB_COLUMNS table for table column metadata.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

Note that this is specific to Oracle.

If you have 'no rows selected' then you could try to change USER_TAB_COLUMNS to all_tab_columns. To be 100% sure about result you could speficy owner.

You can add "ORDER by column_id" in case you want to retrieve them in the same order they were created in the table. Here are some other relevant column data from the table docs.oracle.com/cd/B19306_01/server.102/b14237/

The Oracle table USER_TAB_COLUMNS describe the columns owns by the current user. To access to all columns of all owner, you can query DBA_TAB_COLUMNS.

Sign up for our newsletter and get our top new questions delivered to your inbox (see an example).

sql - How can I get column names from a table in Oracle? - Stack Overf...

sql database oracle
Rectangle 27 132

You can query the USER_TAB_COLUMNS table for table column metadata.

SELECT table_name, column_name, data_type, data_length
FROM USER_TAB_COLUMNS
WHERE table_name = 'MYTABLE'

Note that this is specific to Oracle.

If you have 'no rows selected' then you could try to change USER_TAB_COLUMNS to all_tab_columns. To be 100% sure about result you could speficy owner.

You can add "ORDER by column_id" in case you want to retrieve them in the same order they were created in the table. Here are some other relevant column data from the table docs.oracle.com/cd/B19306_01/server.102/b14237/

The Oracle table USER_TAB_COLUMNS describe the columns owns by the current user. To access to all columns of all owner, you can query DBA_TAB_COLUMNS.

sql - How can I get column names from a table in Oracle? - Stack Overf...

sql database oracle
Rectangle 27 23

-- Creating and Populating the Orders Table
USE tempdb;
GO

IF OBJECT_ID('dbo.Orders') IS NOT NULL
DROP TABLE dbo.Orders;
GO

CREATE TABLE dbo.Orders
(
orderid   int        NOT NULL PRIMARY KEY NONCLUSTERED,
orderdate datetime   NOT NULL,
empid     int        NOT NULL,
custid    varchar(5) NOT NULL,
qty       int        NOT NULL
);

CREATE UNIQUE CLUSTERED INDEX idx_orderdate_orderid
ON dbo.Orders(orderdate, orderid);

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30001, '20020802', 3, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10001, '20021224', 1, 'A', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10005, '20021224', 1, 'B', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40001, '20030109', 4, 'A', 40);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(10006, '20030118', 1, 'C', 14);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20001, '20030212', 2, 'B', 12);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(40005, '20040212', 4, 'A', 10);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(20002, '20040216', 2, 'C', 20);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30003, '20040418', 3, 'B', 15);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30004, '20020418', 3, 'C', 22);
INSERT INTO dbo.Orders(orderid, orderdate, empid, custid, qty)
VALUES(30007, '20020907', 3, 'D', 30);
GO

-- Static PIVOT
SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN([2002],[2003],[2004])) AS P;
GO

-- Dynamic PIVOT
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

DECLARE
@cols AS NVARCHAR(MAX),
@y    AS INT,
@sql  AS NVARCHAR(MAX);

-- Construct the column list for the IN clause
-- e.g., [2002],[2003],[2004]
SET @cols = STUFF(
(SELECT N',' + QUOTENAME(y) AS [text()]
FROM (SELECT DISTINCT YEAR(orderdate) AS y FROM dbo.Orders) AS Y
ORDER BY y
FOR XML PATH('')),
1, 1, N'');

-- Construct the full T-SQL statement
-- and execute dynamically
SET @sql = N'SELECT *
FROM (SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM dbo.Orders) AS D
PIVOT(SUM(qty) FOR orderyear IN(' + @cols + N')) AS P;';

EXEC sp_executesql @sql;
GO

@JohnnyBones why would you think that? EXEC sp_executesql @sql is correct syntax. it will fail if you try EXEC (@sql) because it will try to find stored procedure with that text.

tsql - T-SQL Pivot? Possibility of creating table columns from row val...

tsql pivot database-table
Rectangle 27 1570

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL
ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

Let me value-add to the above two correct responses. The semantics of NULL can have a few meanings. One of the common meaning is UNKNOWN. Take SCORE as an example. A null SCORE and a ZERO SCORE is a world of difference! Before you did the above recommendations. Make sure you application does not take null in its business logic.

Backup your database first in case you make a typo and your DB explodes.

Its strange the MS SQL Management Studio does not allow that option, it brags about "Dropping table ..."

Databases should always be backed up. You never know when one of your people might write and UPDATE or DELETE statement and forget the WHERE clause.

@SebastianGodelet: there's a setting that allows you to turn off that warning, or make it so that it doesn't prevent you from modifying the table. In some cases, changing the schema of a table requires that a new table be created, the data copied over from the old and the old table dropped. Because an error in this process could result in data loss, SSMS warns you and, by default, prevents you from doing it.

sql server - Altering a column: null to not null - Stack Overflow

sql-server tsql null alter-table alter-column
Rectangle 27 1568

UPDATE [Table] SET [Column]=0 WHERE [Column] IS NULL
ALTER TABLE [Table] ALTER COLUMN [Column] INTEGER NOT NULL

Let me value-add to the above two correct responses. The semantics of NULL can have a few meanings. One of the common meaning is UNKNOWN. Take SCORE as an example. A null SCORE and a ZERO SCORE is a world of difference! Before you did the above recommendations. Make sure you application does not take null in its business logic.

Backup your database first in case you make a typo and your DB explodes.

Its strange the MS SQL Management Studio does not allow that option, it brags about "Dropping table ..."

Databases should always be backed up. You never know when one of your people might write and UPDATE or DELETE statement and forget the WHERE clause.

@SebastianGodelet: there's a setting that allows you to turn off that warning, or make it so that it doesn't prevent you from modifying the table. In some cases, changing the schema of a table requires that a new table be created, the data copied over from the old and the old table dropped. Because an error in this process could result in data loss, SSMS warns you and, by default, prevents you from doing it.

sql server - Altering a column: null to not null - Stack Overflow

sql-server tsql null alter-table alter-column
Rectangle 27 94

The method java.sql.Date.valueOf(java.lang.String) received a string representing a date in the format yyyy-[m]m-[d]d. e.g.:

ps.setDate(2, java.sql.Date.valueOf("2013-09-04"));
ps.setDate(2, new java.sql.Date(endDate.getTime());
  • Current If you want to insert the current date: ps.setDate(2, new java.sql.Date(System.currentTimeMillis())); // Since Java 8 ps.setDate(2, java.sql.Date.valueOf(java.time.LocalDate.now()));

If your table has a column of type TIMESTAMP or DATETIME:

java.lang.String

The method java.sql.Timestamp.valueOf(java.lang.String) received a string representing a date in the format yyyy-[m]m-[d]d hh:mm:ss[.f...]. e.g.:

ps.setTimestamp(2, java.sql.Timestamp.valueOf("2013-09-04 13:30:00");
java.util.Date

Suppose you have a variable endDate of type java.util.Date, you make the conversion thus:

ps.setTimestamp(2, new java.sql.Timestamp(endDate.getTime()));
  • Current If you require the current timestamp: ps.setTimestamp(2, new java.sql.Timestamp(System.currentTimeMillis())); // Since Java 8 ps.setTimestamp(2, java.sql.Timestamp.from(java.time.Instant.now())); ps.setTimestamp(2, java.sql.Timestamp.valueOf(java.time.LocalDateTime.now()));

+1 also I think its worth noting that if the date is not the current date but a java Date object, you can use getTime() method to get it to work in a similar manner.

FYI, this Answer is correct but now out-of-date. These troublesome badly-designed old date-time classes are now legacy, supplanted by the java.time classes.

java - Using setDate in PreparedStatement - Stack Overflow

java sql oracle jdbc prepared-statement
Rectangle 27 472

To change the default character set and collation of a table including those of existing columns (note the convert to clause):

alter table <some_table> convert to character set utf8 collate utf8_unicode_ci;

Exactly was I was looking for, thanks. The other answers explain the problem, but this one has the solution.

What are the implications of changing the default character set? Does it update existing data and therefore need to run through the table and make updates, locking it, etc.?

mysql - How to change the default collation of a table? - Stack Overfl...

mysql sql collation
Rectangle 27 103

You need to query the data dictionary, specifically the USER_CONS_COLUMNS view to see the table columns and corresponding constraints:

SELECT *
  FROM user_cons_columns
 WHERE table_name = '<your table name>';

FYI, unless you specifically created your table with a lower case name (using double quotes) then the table name will be defaulted to upper case so ensure it is so in your query.

If you then wish to see more information about the constraint itself query the USER_CONSTRAINTS view:

SELECT *
  FROM user_constraints
 WHERE table_name = '<your table name>'
   AND constraint_name = '<your constraint name>';

If the table is held in a schema that is not your default schema then you might need to replace the views with:

all_cons_columns

and

all_constraints
AND owner = '<schema owner of the table>'
USER_CONS_COLUMNS

<your table name> is case sensitive, I think; It should be in upper case.

Display names of all constraints for a table in Oracle SQL - Stack Ove...

sql oracle oracle11g constraints
Rectangle 27 29

MySQL has 4 levels of collation: server, database, table, column. If you change the collation of the server, database or table, you don't change the setting for each column, but you change the default collations.

E.g if you change the default collation of a database, each new table you create in that database will use that collation, and if you change the default collation of a table, each column you create in that table will get that collation.

In fact, MySQL has FIVE Levels of collation, there is a character set level default collation setting that many people forget about.

show variables like "collation%";

mysql - How to change the default collation of a table? - Stack Overfl...

mysql sql collation
Rectangle 27 23

Use a CHAR(1), and a constraint to allow only '1' and '0'.

col CHAR(1),
CONSTRAINT cons_atable_col1 CHECK (col1 IN ('1','0'))

That is what I was going to write after doing some further research on the case. Anyway, here's a link that should help. thinkoracle.blogspot.com/2005/07/oracle-boolean.html Furthermore, you can define yourself a user type which would be BIT, for instance, then accepting only those constrainted values. Another useful link: download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/

Don't forget about "Default 0 Not Null", if necessary. I tested necessity - to avoid null inserting

boolean - Oracle's lack of a Bit datatype for table columns - Stack Ov...

oracle boolean
Rectangle 27 39

CREATE TABLE foog(a varchar(10));

ALTER TABLE foog ALTER COLUMN a TYPE varchar(30);

postgres=# \d foog

 Table "public.foog"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 a      | character varying(30) |

Note that it works only because you're specifying a bigger size (30 > 10). If the size is smaller, you'll get the same error than I had.

Postgresql - change the size of a varchar column - Stack Overflow

postgresql varchar alter-table