浅谈postgresql数据库varchar、char、text的比较

时间：2022-09-27 11:20:40

如下所示：

名字 描述
character varying(n), varchar(n) 变长，有长度限制
character(n), char(n) 定长，不足补空白
text 变长，无长度限制

简单来说，varchar的长度可变，而char的长度不可变，对于postgresql数据库来说varchar和char的区别仅仅在于前者是变长，而后者是定长，最大长度都是10485760（1GB）

varchar不指定长度，可以存储最大长度（1GB）的字符串，而char不指定长度，默认则为1，这点需要注意。

text类型：在postgresql数据库里边，text和varchar几乎无性能差别，区别仅在于存储结构的不同

对于char的使用，应该在确定字符串长度的情况下使用，否则应该选择varchar或者text

官方解读：

 

SQL定义了两种基本的字符类型：character varying(n) 和character(n)，这里的n 是一个正整数。两种类型都可以存储最多n个字符的字符串（没有字节）。试图存储更长的字符串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字符串将被截断为最大长度。这个看上去有点怪异的例外是SQL标准要求的。如果要存储的字符串比声明的长度短，类型为character的数值将会用空白填满；而类型为character varying的数值将只是存储短些的字符串。

如果我们明确地把一个数值转换成character varying(n) 或character(n)，那么超长的数值将被截断成n 个字符，且不会抛出错误。这也是SQL标准的要求。

varchar(n)和char(n) 分别是character varying(n) 和character(n)的别名，没有声明长度的character等于character(1)；如果不带长度说明词使用character varying，那么该类型接受任何长度的字符串。后者是PostgreSQL的扩展。

另外，PostgreSQL提供text类型，它可以存储任何长度的字符串。尽管类型text不是SQL 标准，但是许多其它SQL数据库系统也有它。

character类型的数值物理上都用空白填充到指定的长度n，并且以这种方式存储和显示。不过，填充的空白是无语意的。在比较两个character 值的时候，填充的空白都不会被关注，在转换成其它字符串类型的时候， character值里面的空白会被删除。请注意，在character varying和text数值里，结尾的空白是有语意的。并且当使用模式匹配时，如LIKE，使用正则表达式。

一个简短的字符串（最多126个字节）的存储要求是1个字节加上实际的字符串，其中包括空格填充的character。更长的字符串有4个字节的开销，而不是1。长的字符串将会自动被系统压缩，因此在磁盘上的物理需求可能会更少些。更长的数值也会存储在后台表里面，这样它们就不会干扰对短字段值的快速访问。不管怎样，允许存储的最长字符串大概是1GB 。允许在数据类型声明中出现的n 的最大值比这还小。修改这个行为没有什么意义，因为在多字节编码下字符和字节的数目可能差别很大。如果你想存储没有特定上限的长字符串，那么使用text 或没有长度声明的character varying，而不要选择一个任意长度限制。

提示： 这三种类型之间没有性能差别，除了当使用填充空白类型时的增加存储空间，和当存储长度约束的列时一些检查存入时长度的额外的CPU周期。虽然在某些其它的数据库系统里，character(n) 有一定的性能优势，但在PostgreSQL里没有。事实上，character(n)通常是这三个中最慢的，因为额外存储成本。在大多数情况下，应该使用text 或character varying。

补充：使用PostGreSQL数据库进行text录入和text检索

中文分词

ChineseParse.cs

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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>
    ///   从指定的文件中初始化中文词语字典和字符串次数字典。
    /// </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>
    ///   将一个字符串解析为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("字符串解析错误：" + s);
      //}
      if (temp.Length != 1)
      {
        throw new Exception("字符串解析错误：" + s);
      }
      return new ChineseWordUnit(temp[0], Int32.Parse("1"));
    }
    /// <summary>
    ///   分析输入的字符串，将其切割成一个个的词语。
    /// </summary>
    /// <param name="s">待切割的字符串</param>
    /// <returns>所切割得到的中文词语数组</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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
using System.Collections;
namespace FullTextSearch.Common
{
  /// <summary>
  ///   记录字符串出现在中文字典所录中文词语的前端的次数的字典类。如字符串"中"出现在"中国"的前端，则在字典中记录一个次数。
  /// </summary>
  public class ChineseWordsHashCountSet
  {
    /// <summary>
    ///   记录字符串在中文词语中出现次数的Hashtable。键为特定的字符串，值为该字符串在中文词语中出现的次数。
    /// </summary>
    private readonly Hashtable _rootTable;
    /// <summary>
    ///   类型初始化。
    /// </summary>
    public ChineseWordsHashCountSet()
    {
      _rootTable = new Hashtable();
    }
    /// <summary>
    ///   查询指定字符串出现在中文字典所录中文词语的前端的次数。
    /// </summary>
    /// <param name="s">指定字符串</param>
    /// <returns>字符串出现在中文字典所录中文词语的前端的次数。若为-1，表示不出现。</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>
    ///   向次数字典中插入一个词语。解析该词语，插入次数字典。
    /// </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>
    ///   向次数字典中插入一个字符串的次数记录。
    /// </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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
namespace FullTextSearch.Common
{
  public struct ChineseWordUnit
  {
    private readonly int _power;
    private readonly string _word;
    /// <summary>
    ///   结构初始化。
    /// </summary>
    /// <param name="word">中文词语</param>
    /// <param name="power">该词语的权重</param>
    public ChineseWordUnit(string word, int power)
    {
      _word = word;
      _power = power;
    }
    /// <summary>
    ///   中文词语单元所对应的中文词。
    /// </summary>
    public string Word
    {
      get { return _word; }
    }
    /// <summary>
    ///   该中文词语的权重。
    /// </summary>
    public int Power
    {
      get { return _power; }
    }
  }
}

ChineseDictionary.txt

浅谈postgresql数据库varchar、char、text的比较

主窗体界面

MainManager.cs

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
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 + " 添加到数据库!");
        rTB_unosTextaUBazu.Clear();
      }
      else
      {
        MessageBox.Show("不允许空数据!");
      }
    }
    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 = "找到的文件数量: " + 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代码:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
using System.Collections.Generic;
namespace FullTextSearch
{
  internal class SQLquerys
  {
    private static string giveMeTheKey;
    private static int tempInt = 1;
    //设置连接字符串
    public void setTheKey(string connString)
    {
      giveMeTheKey = connString;
      giveMeTheKey += "";
    }
    //将连接字符串存储在静态变量中
    public string getTheKey()
    {
      giveMeTheKey += "";
      return giveMeTheKey;
    }
    public void setCounter()
    {
      tempInt = 1;
    }
    //根据AND和OR的选择分析字符串进行搜索
    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中创建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;
    }
    //创建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')
      {
        //这个查询是这样写的只是为了测试的目的，它需要改变
        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')
      {
        //这个查询是这样写的只是为了测试的目的，它需要改变
        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;
    }
    //此代码用于创建分析，它执行一些日期/时间操作，以便能够为选定的日期/时间创建分析。
    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代码:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
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脚本:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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;
?
1
2
3
4
5
6
7
8
9
10
11
12
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;

浅谈postgresql数据库varchar、char、text的比较

运行结果如图:

浅谈postgresql数据库varchar、char、text的比较

浅谈postgresql数据库varchar、char、text的比较

浅谈postgresql数据库varchar、char、text的比较

浅谈postgresql数据库varchar、char、text的比较

以上为个人经验，希望能给大家一个参考，也希望大家多多支持服务器之家。如有错误或未考虑完全的地方，望不吝赐教。

原文链接：https://blog.csdn.net/u013992330/article/details/76653361

标签：

相关文章