淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較

更新時(shí)間：2022年07月07日 15:47:44 作者：password-u

這篇文章主要介紹了淺談postgresql數(shù)據(jù)庫(kù)varchar、char、text的比較，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。一起跟隨小編過來看看吧

如下所示

名字	描述
character varying(n), varchar(n)	變長(zhǎng)，有長(zhǎng)度限制
character(n), char(n)	定長(zhǎng)，不足補(bǔ)空白
text	變長(zhǎng)，無長(zhǎng)度限制

簡(jiǎn)單來說，varchar的長(zhǎng)度可變，而char的長(zhǎng)度不可變，對(duì)于postgresql數(shù)據(jù)庫(kù)來說varchar和char的區(qū)別僅僅在于前者是變長(zhǎng)，而后者是定長(zhǎng)，最大長(zhǎng)度都是10485760（1GB）

varchar不指定長(zhǎng)度，可以存儲(chǔ)最大長(zhǎng)度（1GB）的字符串，而char不指定長(zhǎng)度，默認(rèn)則為1，這點(diǎn)需要注意。

text類型：在postgresql數(shù)據(jù)庫(kù)里邊，text和varchar幾乎無性能差別，區(qū)別僅在于存儲(chǔ)結(jié)構(gòu)的不同

對(duì)于char的使用，應(yīng)該在確定字符串長(zhǎng)度的情況下使用，否則應(yīng)該選擇varchar或者text

官方解讀

SQL定義了兩種基本的字符類型：character varying(n) 和character(n)，這里的n 是一個(gè)正整數(shù)。兩種類型都可以存儲(chǔ)最多n個(gè)字符的字符串（沒有字節(jié)）。試圖存儲(chǔ)更長(zhǎng)的字符串到這些類型的字段里會(huì)產(chǎn)生一個(gè)錯(cuò)誤，除非超出長(zhǎng)度的字符都是空白，這種情況下該字符串將被截?cái)酁樽畲箝L(zhǎng)度。這個(gè)看上去有點(diǎn)怪異的例外是SQL標(biāo)準(zhǔn)要求的。如果要存儲(chǔ)的字符串比聲明的長(zhǎng)度短，類型為character的數(shù)值將會(huì)用空白填滿；而類型為character varying的數(shù)值將只是存儲(chǔ)短些的字符串。

如果我們明確地把一個(gè)數(shù)值轉(zhuǎn)換成character varying(n) 或character(n)，那么超長(zhǎng)的數(shù)值將被截?cái)喑蒼個(gè)字符，且不會(huì)拋出錯(cuò)誤。這也是SQL標(biāo)準(zhǔn)的要求。

varchar(n)和char(n) 分別是character varying(n) 和character(n)的別名，沒有聲明長(zhǎng)度的character等于character(1)；如果不帶長(zhǎng)度說明詞使用character varying，那么該類型接受任何長(zhǎng)度的字符串。后者是PostgreSQL的擴(kuò)展。

另外，PostgreSQL提供text類型，它可以存儲(chǔ)任何長(zhǎng)度的字符串。盡管類型text不是SQL 標(biāo)準(zhǔn)，但是許多其它SQL數(shù)據(jù)庫(kù)系統(tǒng)也有它。

character類型的數(shù)值物理上都用空白填充到指定的長(zhǎng)度n，并且以這種方式存儲(chǔ)和顯示。不過，填充的空白是無語意的。在比較兩個(gè)character 值的時(shí)候，填充的空白都不會(huì)被關(guān)注，在轉(zhuǎn)換成其它字符串類型的時(shí)候， character值里面的空白會(huì)被刪除。請(qǐng)注意，在character varying和text數(shù)值里，結(jié)尾的空白是有語意的。并且當(dāng)使用模式匹配時(shí)，如LIKE，使用正則表達(dá)式。

一個(gè)簡(jiǎn)短的字符串（最多126個(gè)字節(jié)）的存儲(chǔ)要求是1個(gè)字節(jié)加上實(shí)際的字符串，其中包括空格填充的character。更長(zhǎng)的字符串有4個(gè)字節(jié)的開銷，而不是1。長(zhǎng)的字符串將會(huì)自動(dòng)被系統(tǒng)壓縮，因此在磁盤上的物理需求可能會(huì)更少些。更長(zhǎng)的數(shù)值也會(huì)存儲(chǔ)在后臺(tái)表里面，這樣它們就不會(huì)干擾對(duì)短字段值的快速訪問。不管怎樣，允許存儲(chǔ)的最長(zhǎng)字符串大概是1GB 。允許在數(shù)據(jù)類型聲明中出現(xiàn)的n 的最大值比這還小。修改這個(gè)行為沒有什么意義，因?yàn)樵诙嘧止?jié)編碼下字符和字節(jié)的數(shù)目可能差別很大。如果你想存儲(chǔ)沒有特定上限的長(zhǎng)字符串，那么使用text 或沒有長(zhǎng)度聲明的character varying，而不要選擇一個(gè)任意長(zhǎng)度限制。

提示：這三種類型之間沒有性能差別，除了當(dāng)使用填充空白類型時(shí)的增加存儲(chǔ)空間，和當(dāng)存儲(chǔ)長(zhǎng)度約束的列時(shí)一些檢查存入時(shí)長(zhǎng)度的額外的CPU周期。雖然在某些其它的數(shù)據(jù)庫(kù)系統(tǒng)里，character(n) 有一定的性能優(yōu)勢(shì)，但在PostgreSQL里沒有。事實(shí)上，character(n)通常是這三個(gè)中最慢的，因?yàn)轭~外存儲(chǔ)成本。在大多數(shù)情況下，應(yīng)該使用text 或character varying。

使用PostGreSQL數(shù)據(jù)庫(kù)進(jìn)行text錄入和text檢索

中文分詞

ChineseParse.cs

using System;
using System.Collections;
using System.IO;
using System.Text.RegularExpressions;
namespace FullTextSearch.Common
{
  /// <summary>
  ///   中文分詞器。
  /// </summary>
  public class ChineseParse
  {
    private static readonly ChineseWordsHashCountSet _countTable;
    static ChineseParse()
    {
      _countTable = new ChineseWordsHashCountSet();
      InitFromFile("ChineseDictionary.txt");
    }
    /// <summary>
    ///   從指定的文件中初始化中文詞語字典和字符串次數(shù)字典。
    /// </summary>
    /// <param name="fileName">文件名</param>
    private static void InitFromFile(string fileName)
    {
      string path = Path.Combine(Directory.GetCurrentDirectory(), @"..\..\Common\", fileName);
      if (File.Exists(path))
      {
        using (StreamReader sr = File.OpenText(path))
        {
          string s = "";
          while ((s = sr.ReadLine()) != null)
          {
            ChineseWordUnit _tempUnit = InitUnit(s);
            _countTable.InsertWord(_tempUnit.Word);
          }
        }
      }
    }
    /// <summary>
    ///   將一個(gè)字符串解析為ChineseWordUnit。
    /// </summary>
    /// <param name="s">字符串</param>
    /// <returns>解析得到的ChineseWordUnit</returns>
    /// 4
    /// 0
    private static ChineseWordUnit InitUnit(string s)
    {
      var reg = new Regex(@"\s+");
      string[] temp = reg.Split(s);
      //if (temp.Length != 2)
      //{
      //  throw new Exception("字符串解析錯(cuò)誤：" + s);
      //}
      if (temp.Length != 1)
      {
        throw new Exception("字符串解析錯(cuò)誤：" + s);
      }
      return new ChineseWordUnit(temp[0], Int32.Parse("1"));
    }
    /// <summary>
    ///   分析輸入的字符串，將其切割成一個(gè)個(gè)的詞語。
    /// </summary>
    /// <param name="s">待切割的字符串</param>
    /// <returns>所切割得到的中文詞語數(shù)組</returns>
    public static string[] ParseChinese(string s)
    {
      int _length = s.Length;
      string _temp = String.Empty;
      var _words = new ArrayList();
      for (int i = 0; i < s.Length;)
      {
        _temp = s.Substring(i, 1);
        if (_countTable.GetCount(_temp) > 1)
        {
          int j = 2;
          for (; i + j < s.Length + 1 && _countTable.GetCount(s.Substring(i, j)) > 0; j++)
          {
          }
          _temp = s.Substring(i, j - 1);
          i = i + j - 2;
        }
        i++;
        _words.Add(_temp);
      }
      var _tempStringArray = new string[_words.Count];
      _words.CopyTo(_tempStringArray);
      return _tempStringArray;
    }
  }
}

ChineseWordsHashCountSet.cs

using System.Collections;
namespace FullTextSearch.Common
{
  /// <summary>
  ///   記錄字符串出現(xiàn)在中文字典所錄中文詞語的前端的次數(shù)的字典類。如字符串"中"出現(xiàn)在"中國(guó)"的前端，則在字典中記錄一個(gè)次數(shù)。
  /// </summary>
  public class ChineseWordsHashCountSet
  {
    /// <summary>
    ///   記錄字符串在中文詞語中出現(xiàn)次數(shù)的Hashtable。鍵為特定的字符串，值為該字符串在中文詞語中出現(xiàn)的次數(shù)。
    /// </summary>
    private readonly Hashtable _rootTable;
    /// <summary>
    ///   類型初始化。
    /// </summary>
    public ChineseWordsHashCountSet()
    {
      _rootTable = new Hashtable();
    }
    /// <summary>
    ///   查詢指定字符串出現(xiàn)在中文字典所錄中文詞語的前端的次數(shù)。
    /// </summary>
    /// <param name="s">指定字符串</param>
    /// <returns>字符串出現(xiàn)在中文字典所錄中文詞語的前端的次數(shù)。若為-1，表示不出現(xiàn)。</returns>
    public int GetCount(string s)
    {
      if (!_rootTable.ContainsKey(s.Length))
      {
        return -1;
      }
      var _tempTable = (Hashtable) _rootTable[s.Length];
      if (!_tempTable.ContainsKey(s))
      {
        return -1;
      }
      return (int) _tempTable[s];
    }
    /// <summary>
    ///   向次數(shù)字典中插入一個(gè)詞語。解析該詞語，插入次數(shù)字典。
    /// </summary>
    /// <param name="s">所處理的字符串。</param>
    public void InsertWord(string s)
    {
      for (int i = 0; i < s.Length; i++)
      {
        string _s = s.Substring(0, i + 1);
        InsertSubString(_s);
      }
    }
    /// <summary>
    ///   向次數(shù)字典中插入一個(gè)字符串的次數(shù)記錄。
    /// </summary>
    /// <param name="s">所插入的字符串。</param>
    private void InsertSubString(string s)
    {
      if (!_rootTable.ContainsKey(s.Length) && s.Length > 0)
      {
        var _newHashtable = new Hashtable();
        _rootTable.Add(s.Length, _newHashtable);
      }
      var _tempTable = (Hashtable) _rootTable[s.Length];
      if (!_tempTable.ContainsKey(s))
      {
        _tempTable.Add(s, 1);
      }
      else
      {
        _tempTable[s] = (int) _tempTable[s] + 1;
      }
    }
  }
}

ChineseWordUnit.cs

namespace FullTextSearch.Common
{
  public struct ChineseWordUnit
  {
    private readonly int _power;
    private readonly string _word;
    /// <summary>
    ///   結(jié)構(gòu)初始化。
    /// </summary>
    /// <param name="word">中文詞語</param>
    /// <param name="power">該詞語的權(quán)重</param>
    public ChineseWordUnit(string word, int power)
    {
      _word = word;
      _power = power;
    }
    /// <summary>
    ///   中文詞語單元所對(duì)應(yīng)的中文詞。
    /// </summary>
    public string Word
    {
      get { return _word; }
    }
    /// <summary>
    ///   該中文詞語的權(quán)重。
    /// </summary>
    public int Power
    {
      get { return _power; }
    }
  }
}

ChineseDictionary.txt

主窗體界面

MainManager.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using FullTextSearch.Common;
using Npgsql;
namespace FullTextSearch
{
  public partial class MainManager : Form
  {
    private readonly PostgreSQL pg = new PostgreSQL();
    private readonly SQLquerys sqlQuerys = new SQLquerys();
    private char analysisType;
    private string createConnString = "";
    private DataSet dataSet = new DataSet();
    private DataTable dataTable = new DataTable();
    private char odabirAndOr;
    private char vrstaPretrazivanja;
    public MainManager()
    {
      InitializeComponent();
      rbtn_AND.Checked = true;
      rbtnNeizmjenjeni.Checked = true;
      odabirAndOr = '*';
      radioButton_Day.Checked = true;
      radioButton_Day.Checked = true;
    }
    private void Form1_Load(object sender, EventArgs e)
    {
      gb_unosPodataka.Enabled = false;
      groupBox_Search.Enabled = false;
      groupBox_Analysis.Enabled = false;
      button_Disconnect.Enabled = false;
      button_Pretrazi.BackColor = Color.WhiteSmoke;
      button_Disconnect.BackColor = Color.WhiteSmoke;
      button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
      button1.BackColor = Color.WhiteSmoke;
    }
    private void button_unosTekstaUBazu_Click(object sender, EventArgs e)
    {
      string searchTextBoxString = rTB_unosTextaUBazu.Text;
      if (searchTextBoxString != "")
      {
        pg.insertIntoTable(searchTextBoxString, pg.conn);
        MessageBox.Show(searchTextBoxString + " 添加到數(shù)據(jù)庫(kù)!");
        rTB_unosTextaUBazu.Clear();
      }
      else
      {
        MessageBox.Show("不允許空數(shù)據(jù)!");
      }
    }
    private void button_Pretrazi_Click(object sender, EventArgs e)
    {
      string stringToSearch;
      string sql;
      string highlitedText;
      string rank;
      string check;
      stringToSearch = txt_Search.Text.Trim();
      var list = new List<string>(ChineseParse.ParseChinese(stringToSearch));
      ;
      sql = sqlQuerys.createSqlString(list, odabirAndOr, vrstaPretrazivanja);
      richTextBox1.Text = sql;
      check = sqlQuerys.testIfEmpty(stringToSearch);
      pg.insertIntoAnalysisTable(stringToSearch, pg.conn);
      pg.openConnection();
      var command = new NpgsqlCommand(sql, pg.conn);
      NpgsqlDataReader reader = command.ExecuteReader();
      int count = 0;
      linkLabel_Rezultat.Text = " ";
      while (reader.Read())
      {
        highlitedText = reader[1].ToString();
        rank = reader[3].ToString();
        linkLabel_Rezultat.Text += highlitedText + "[" + rank + "]\n";
        count++;
      }
      labelBrojac.Text = "找到的文件數(shù)量: " + count;
      pg.closeConnection();
    }
    private void rbtn_AND_CheckedChanged(object sender, EventArgs e)
    {
      odabirAndOr = '*';
    }
    private void rbtn_OR_CheckedChanged(object sender, EventArgs e)
    {
      odabirAndOr = '+';
    }
    private void rbtnNeizmjenjeni_CheckedChanged(object sender, EventArgs e)
    {
      vrstaPretrazivanja = 'A';
    }
    private void rbtn_Rijecnici_CheckedChanged(object sender, EventArgs e)
    {
      vrstaPretrazivanja = 'B';
    }
    private void rbtn_Fuzzy_CheckedChanged(object sender, EventArgs e)
    {
      vrstaPretrazivanja = 'C';
    }
    private void button_Connect_Click(object sender, EventArgs e)
    {
      if (connectMe())
      {
        gb_unosPodataka.Enabled = true;
        groupBox_Search.Enabled = true;
        groupBox_Analysis.Enabled = true;
        textBox_Database.Enabled = false;
        textBox_IP.Enabled = false;
        textBox_Port.Enabled = false;
        textBox_Password.Enabled = false;
        textBox_UserID.Enabled = false;
        button_Connect.Enabled = false;
        button_Disconnect.Enabled = true;
        button_Pretrazi.BackColor = Color.SkyBlue;
        button_Disconnect.BackColor = Color.IndianRed;
        button_unosTekstaUBazu.BackColor = Color.MediumSeaGreen;
        button1.BackColor = Color.MediumSeaGreen;
        button_Connect.BackColor = Color.WhiteSmoke;
      }
    }
    private void button_Disconnect_Click(object sender, EventArgs e)
    {
      gb_unosPodataka.Enabled = false;
      groupBox_Search.Enabled = false;
      groupBox_Analysis.Enabled = false;
      textBox_Database.Enabled = true;
      textBox_IP.Enabled = true;
      textBox_Port.Enabled = true;
      textBox_Password.Enabled = true;
      textBox_UserID.Enabled = true;
      button_Connect.Enabled = true;
      button_Disconnect.Enabled = false;
      button_Pretrazi.BackColor = Color.WhiteSmoke;
      button_Disconnect.BackColor = Color.WhiteSmoke;
      button_unosTekstaUBazu.BackColor = Color.WhiteSmoke;
      button1.BackColor = Color.WhiteSmoke;
      button_Connect.BackColor = Color.MediumSeaGreen;
      txt_Search.Text = "";
      linkLabel_Rezultat.Text = "";
      richTextBox1.Text = "";
      labelBrojac.Text = "";
    }
    private bool connectMe()
    {
      createConnString += "Server=" + textBox_IP.Text + ";Port=" + textBox_Port.Text + ";User Id=" +
                textBox_UserID.Text + ";Password=" + textBox_Password.Text + ";Database=" +
                textBox_Database.Text + ";";
      sqlQuerys.setTheKey(createConnString);
      pg.setConnectionString();
      pg.setConnection();
      if (pg.openConnection())
      {
        MessageBox.Show("您已成功連接!");
        pg.closeConnection();
        return true;
      }
      return false;
    }
    private void button1_Click(object sender, EventArgs e)
    {
      string selectedTimestamp;
      selectedTimestamp = dateTimePicker_From.Value.ToString("dd-MM-yyyy hh:mm:ss") + " " +
                dateTimePicker_To.Value.ToString("dd-MM-yyyy hh:mm:ss");
      var analize = new Analysis(selectedTimestamp, analysisType);
      analize.Show();
    }
    private void radioButton_Day_CheckedChanged(object sender, EventArgs e)
    {
      analysisType = 'D';
    }
    private void radioButton_Hour_CheckedChanged(object sender, EventArgs e)
    {
      analysisType = 'H';
    }
  }
}

SQLquerys.cs代碼:

using System.Collections.Generic;
namespace FullTextSearch
{
  internal class SQLquerys
  {
    private static string giveMeTheKey;
    private static int tempInt = 1;
    //設(shè)置連接字符串
    public void setTheKey(string connString)
    {
      giveMeTheKey = connString;
      giveMeTheKey += "";
    }
    //將連接字符串存儲(chǔ)在靜態(tài)變量中
    public string getTheKey()
    {
      giveMeTheKey += "";
      return giveMeTheKey;
    }
    public void setCounter()
    {
      tempInt = 1;
    }
    //根據(jù)AND和OR的選擇分析字符串進(jìn)行搜索
    public string createFunctionString(List<string> searchList, char selector)
    {
      string TempString = "";
      string[] TempField = null;
      int i = 0;
      int j = 0;
      foreach (string searchStringInList in searchList)
      {
        if (j != 0)
        {
          if (selector == '+')
            TempString = TempString + " | ";
          else if (selector == '*')
            TempString = TempString + " & ";
        }
        j = 1;
        TempField = splitListForInput(searchStringInList);
        TempString = TempString + "(";
        foreach (string justTempString in TempField)
        {
          if (i != 0)
          {
            TempString = TempString + " & ";
          }
          TempString = TempString + justTempString;
          i = 1;
        }
        TempString = TempString + ")";
        i = 0;
      }
      return TempString;
    }
    //幫助方法
    public List<string> splitInputField(string[] inputField)
    {
      var unfinishedList = new List<string>();
      foreach (string splitString in inputField)
      {
        unfinishedList.Add(splitString);
      }
      return unfinishedList;
    }
    //幫助方法
    public string[] splitListForInput(string inputString)
    {
      string[] parsedList = null;
      parsedList = inputString.Split(' ');
      return parsedList;
    }
    //在PostgreSQL中創(chuàng)建ts功能的功能，用于字典搜索
    public string createTsFunction(string tsString)
    {
      string tsHeadline = "";
      string tsRank = "";
      string tsFunction = "";
      tsHeadline = ",\n ts_headline(\"content\", to_tsquery('" + tsString + "')), \"content\"";
      tsRank = ",\n ts_rank(to_tsvector(\"content\"), to_tsquery('" + tsString + "')) rank";
      tsFunction = tsHeadline + tsRank;
      return tsFunction;
    }
    //創(chuàng)建SQL查詢依賴于選擇哪種類型的搜索，也取決于AND或OR選擇器
    public string createSqlString(List<string> searchList, char selector, char vrstaPretrazivanja)
    {
      string selectString = "";
      string myTempString = "";
      string TempString = "";
      int i = 0;
      TempString = createFunctionString(searchList, selector);
      TempString = createTsFunction(TempString);
      selectString = "SELECT \"id\"" + TempString + "\nFROM \"texttable\" \nWHERE ";
      if (vrstaPretrazivanja == 'A')
      {
        foreach (string myString in searchList)
        {
          if (i == 0)
          {
            myTempString = myTempString + "\"content\" LIKE '%" + myString + "%' ";
            i++;
          }
          else
          {
            if (selector == '*')
              myTempString = myTempString + "\nAND \"content\" LIKE '%" + myString + "%' ";
            else if (selector == '+')
              myTempString = myTempString + "\nOR \"content\" LIKE '%" + myString + "%' ";
          }
        }
      }
      else if (vrstaPretrazivanja == 'B')
      {
        foreach (string myString in searchList)
        {
          string temporalString = "";
          string[] testingString = myString.Split(' ');
          for (int k = 0; k < testingString.Length; k++)
          {
            if (k != testingString.Length - 1)
            {
              temporalString += testingString[k] + " & ";
            }
            else
            {
              temporalString += testingString[k];
            }
          }
          if (i == 0)
          {
            myTempString = myTempString + "to_tsvector(\"content\") @@ to_tsquery('english', '" +
                    temporalString + "')";
            i++;
          }
          else
          {
            if (selector == '*')
              myTempString = myTempString + "\nAND to_tsvector(\"content\") @@ to_tsquery('english', '" +
                      temporalString + "')";
            else if (selector == '+')
              myTempString = myTempString + "\nOR to_tsvector(\"content\") @@ to_tsquery('english', '" +
                      temporalString + "')";
          }
        }
      }
      if (vrstaPretrazivanja == 'C')
      {
        foreach (string myString in searchList)
        {
          if (i == 0)
          {
            myTempString = myTempString + "\"content\" % '" + myString + "' ";
            i++;
          }
          else
          {
            if (selector == '*')
              myTempString = myTempString + "\nAND \"content\" % '" + myString + "' ";
            else if (selector == '+')
              myTempString = myTempString + "\nOR \"content\" % '" + myString + "' ";
          }
        }
      }
      selectString = selectString + myTempString + "\nORDER BY rank DESC";
      return selectString;
    }
    public string testIfEmpty(string searchedText)
    {
      string checkingIfEmpty = "SELECT * FROM \"analysisTable\" WHERE \"searchedtext\" =' " + searchedText + "'";
      return checkingIfEmpty;
    }
    public string queryForAnalysis(char analysisChoice)
    {
      string myTestsql = "";
      if (analysisChoice == 'H')
      {
        //這個(gè)查詢是這樣寫的只是為了測(cè)試的目的，它需要改變
        myTestsql = "SELECT * FROM crosstab('SELECT CAST((\"searchedtext\") AS text) searchedText,"
              +
              " CAST(EXTRACT(HOUR FROM \"timeOfSearch\") AS int) AS sat, CAST(COUNT(*) AS int) AS broj FROM \"analysisTable\" GROUP BY \"searchedText\", sat"
              +
              " ORDER BY \"searchedtext\", sat', 'SELECT rbrSata FROM sat ORDER BY rbrSata') AS pivotTable (\"searchedText\" TEXT, t0_1 INT, t1_2 INT"
              +
              ", t2_3 INT, t3_4 INT, t4_5 INT, t5_6 INT, t6_7 INT, t7_8 INT, t8_9 INT, t9_10 INT, t10_11 INT, t11_12 INT, t12_13 INT"
              +
              ", t13_14 INT, t14_15 INT, t15_16 INT, t16_17 INT, t17_18 INT, t18_19 INT, t19_20 INT, t20_21 INT, t21_22 INT, t22_23 INT, t23_00 INT) ORDER BY \"searchedText\"";
        return myTestsql;
      }
      if (analysisChoice == 'D')
      {
        //這個(gè)查詢是這樣寫的只是為了測(cè)試的目的，它需要改變
        myTestsql += "SELECT *FROM crosstab ('SELECT CAST((\"searchedtext\") AS text) AS searchedText, CAST(EXTRACT(DAY FROM \"dateOfSearch\") AS int) AS dan"
               + ", CAST(COUNT(*) AS int) AS broj FROM \"analysisTable\" GROUP BY \"searchedText\", "
               +
               "dan ORDER BY \"searchedtext\", dan', 'SELECT rbrDana FROM dan ORDER BY rbrDana') AS pivotTable(\"searchedtext\" TEXT";
        return myTestsql;
      }
      return myTestsql;
    }
    //此方法用于解析日期
    public int[] parseForDates(string date)
    {
      string[] temp;
      var tempInt = new int[3];
      temp = date.Split('-');
      for (int i = 0; i < 3; i++)
      {
        tempInt[i] = int.Parse(temp[i]);
      }
      return tempInt;
    }
    //此代碼用于創(chuàng)建分析，它執(zhí)行一些日期/時(shí)間操作，以便能夠?yàn)檫x定的日期/時(shí)間創(chuàng)建分析。
    public string createSqlForDayAnalysis(string dateFrom, string dateTo)
    {
      string insertIntoTempTable = "";
      string dateTimeForAnalysis = "";
      int[] tempFrom = parseForDates(dateFrom);
      int[] tempTo = parseForDates(dateTo);
      //月份變更算法
      while (tempFrom[0] != tempTo[0] || tempFrom[1] != tempTo[1])
      {
        if (tempFrom[1] == tempTo[1])
        {
          if (tempFrom[0] != tempTo[0])
          {
            for (int i = tempInt + 1; tempFrom[0] + 2 < tempTo[0] + 2; i++)
            {
              insertIntoTempTable += "INSERT INTO \"dan\" VALUES (" + i + ");";
              dateTimeForAnalysis += ",dd" + tempFrom[0] + tempFrom[1] + tempFrom[2] + " INT";
              tempInt = i;
              tempFrom[0]++;
            }
          }
        }
        if (tempFrom[1] != tempTo[1])
        {
          if (tempFrom[1]%2 == 0 || tempFrom[1] == 7 || tempFrom[1] == 1)
          {
            for (int i = tempInt; tempFrom[0] < 31 && tempFrom[1] != tempTo[1]; i++)
            {
              insertIntoTempTable += "INSERT INTO \"dan\" VALUES (" + i + ");";
              dateTimeForAnalysis += ", dd" + tempFrom[0] + tempFrom[1] + tempFrom[2] + " INT";
              tempInt = i;
              tempFrom[0]++;
              if (tempFrom[0] == 31)
              {
                tempFrom[1]++;
                tempFrom[0] = 1;
              }
            }
          }
        }
      }
      dateTimeForAnalysis += ") ORDER BY \"searchedtext\"";
      return dateTimeForAnalysis + "#" + insertIntoTempTable;
    }
  }
}

PostgreSQL.cs代碼:

using System;
using System.Windows.Forms;
using Npgsql;
using NpgsqlTypes;
namespace FullTextSearch
{
  public class PostgreSQL
  {
    private static int tempInt = 1;
    private readonly SQLquerys sql = new SQLquerys();
    public NpgsqlConnection conn;
    public string connectionstring;
    private string newConnString;
    public PostgreSQL()
    {
      setConnectionString();
      setConnection();
    }
    public void setConnectionString()
    {
      newConnString = sql.getTheKey();
      connectionstring = String.Format(newConnString);
      setConnection();
    }
    public void setConnection()
    {
      conn = new NpgsqlConnection(connectionstring);
    }
    public bool openConnection()
    {
      try
      {
        conn.Open();
        return true;
      }
      catch
      {
        MessageBox.Show("Unable to connect! Check parameters!");
        return false;
      }
    }
    public void closeConnection()
    {
      conn.Close();
    }
    public void insertIntoTable(string textToInsert, NpgsqlConnection nsqlConn)
    {
      string mySqlString = "INSERT INTO \"texttable\" (\"content\") VALUES (@Param1)";
      var myParameter = new NpgsqlParameter("@Param1", NpgsqlDbType.Text);
      myParameter.Value = textToInsert;
      openConnection();
      var myCommand = new NpgsqlCommand(mySqlString, nsqlConn);
      myCommand.Parameters.Add(myParameter);
      myCommand.ExecuteNonQuery();
      closeConnection();
    }
    public void insertIntoAnalysisTable(string textToInsert, NpgsqlConnection nsqlConn)
    {
      string dateTime = DateTime.Now.ToString();
      string[] temp;
      temp = dateTime.Split(' ');
      string mySqlString =
        "INSERT INTO \"analysistable\" (\"searchedtext\", \"dateofsearch\", \"timeofsearch\") VALUES ('" +
        textToInsert + "', '" + temp[0] + "'" + ", '" + temp[1] + "');";
      openConnection();
      var myCommand = new NpgsqlCommand(mySqlString, nsqlConn);
      myCommand.ExecuteNonQuery();
      closeConnection();
    }
    public void executeQuery(string queryText, NpgsqlConnection nsqlConn)
    {
      openConnection();
      var myCommand = new NpgsqlCommand(queryText, nsqlConn);
      myCommand.ExecuteNonQuery();
      closeConnection();
    }
    public void createTempTable(NpgsqlConnection nsqlConn, char analysisType, string dateFrom, string dateTo,
      string splitMe)
    {
      if (analysisType == 'H')
      {
        string dropIfExists = "DROP TABLE IF EXISTS \"sat\";";
        string createTempTable = "CREATE TABLE IF NOT EXISTS \"sat\" (rbrSata INT);";
        string insertIntoTempTable = "";
        for (int i = 0; i < 24; i++)
        {
          insertIntoTempTable += "INSERT INTO \"sat\" VALUES (" + i + ");";
        }
        openConnection();
        var commandDrop = new NpgsqlCommand(dropIfExists, nsqlConn);
        commandDrop.ExecuteNonQuery();
        var commandCreate = new NpgsqlCommand(createTempTable, nsqlConn);
        commandCreate.ExecuteNonQuery();
        var commandInsert = new NpgsqlCommand(insertIntoTempTable, nsqlConn);
        commandInsert.ExecuteNonQuery();
        closeConnection();
      }
      else if (analysisType == 'D')
      {
        string dropIfExists = "DROP TABLE IF EXISTS \"dan\";";
        string createTempTable = "CREATE TABLE IF NOT EXISTS \"dan\" (rbrDana INT);";
        string insertIntoTempTable = splitMe;
        openConnection();
        var commandDrop = new NpgsqlCommand(dropIfExists, nsqlConn);
        commandDrop.ExecuteNonQuery();
        var commandCreate = new NpgsqlCommand(createTempTable, nsqlConn);
        commandCreate.ExecuteNonQuery();
        var commandInsert = new NpgsqlCommand(insertIntoTempTable, nsqlConn);
        commandInsert.ExecuteNonQuery();
        closeConnection();
      }
    }
  }
}

PostGreSQL sql腳本:

CREATE TABLE public.analysistable
(
  id integer NOT NULL DEFAULT nextval('analysistable_id_seq'::regclass),
  searchedtext text COLLATE pg_catalog."default" NOT NULL,
  dateofsearch date NOT NULL,
  timeofsearch time without time zone NOT NULL,
  CONSTRAINT analysistable_pkey PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.analysistable
  OWNER to king;

CREATE TABLE public.texttable
(
  id integer NOT NULL DEFAULT nextval('texttable_id_seq'::regclass),
  content text COLLATE pg_catalog."default" NOT NULL,
  CONSTRAINT texttable_pkey PRIMARY KEY (id)
)
WITH (
  OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.texttable
  OWNER to king;