服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - PostgreSQL - 浅谈postgresql数据库varchar、char、text的比较

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

2021-03-25 20:53password-u PostgreSQL

这篇文章主要介绍了浅谈postgresql数据库varchar、char、text的比较，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧

如下所示：

名字 描述
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

  • PostgreSQL
  • text
  • char
  • varchar

    • 延伸 · 阅读

    精彩推荐
    • PostgreSQL深入理解PostgreSQL的MVCC并发处理方式

      深入理解PostgreSQL的MVCC并发处理方式

      这篇文章主要介绍了深入理解PostgreSQL的MVCC并发处理方式,文中同时介绍了MVCC的缺点,需要的朋友可以参考下 ...

      PostgreSQL教程网3622020-04-25
    • PostgreSQLpostgresql 数据库中的数据转换

      postgresql 数据库中的数据转换

      postgres8.3以后，字段数据之间的默认转换取消了。如果需要进行数据变换的话，在postgresql数据库中，我们可以用"::"来进行字段数据的类型转换。...

      postgresql教程网12482021-10-08
    • PostgreSQLPostgresql开启远程访问的步骤全纪录

      Postgresql开启远程访问的步骤全纪录

      postgre一般默认为本地连接，不支持远程访问，所以如果要开启远程访问，需要更改安装文件的配置。下面这篇文章主要给大家介绍了关于Postgresql开启远程...

      我勒个去6812020-04-30
    • PostgreSQLRDS PostgreSQL一键大版本升级技术解密

      RDS PostgreSQL一键大版本升级技术解密

      一、PostgreSQL行业位置 （一）行业位置 在讨论PostgreSQL（下面简称为PG）在整个数据库行业的位置之前，我们先看一下阿里云数据库在全球的数据库行业里的...

      未知1192023-05-07
    • PostgreSQLPostgreSQL标准建表语句分享

      PostgreSQL标准建表语句分享

      这篇文章主要介绍了PostgreSQL标准建表语句分享，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      码上得天下7962021-02-27
    • PostgreSQLPostgresql查询效率计算初探

      Postgresql查询效率计算初探

      这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用Postgresql具有一定的参考学习价...

      轨迹4622020-05-03
    • PostgreSQLpostgresql 中的to_char()常用操作

      postgresql 中的to_char()常用操作

      这篇文章主要介绍了postgresql 中的to_char()常用操作，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...

      J符离13432021-04-12
    • PostgreSQL分布式 PostgreSQL之Citus 架构

      分布式 PostgreSQL之Citus 架构

      节点 Citus 是一种 PostgreSQL 扩展，它允许数据库服务器(称为节点)在“无共享(shared nothing)”架构中相互协调。这些节点形成一个集群，允许 PostgreSQL 保存比单...

      未知802023-05-07
    最近更新
    编辑推荐
    阅读排行
    热门标签
    5432 　 postgresql命令 　 数据库教程 　 入门命令 　 死锁进程 　 PostgreSQL教程 　 表继承 　 事物隔离 　 性能提升技巧 　 PL/pgSQL 　 过程语言 　 partitioning 　 PostgreSQL安装教程 　 跨库访问 　 postgresql数据库 　 并发处理 　 MVCC 　 Citus 　 OID 　 XID 　 表空间大小 　 Postgre 　 phpPgAdmin 　 phpPgAdmin错误 　 phpPgAdmin问题 　 phpPgAdmin配置文件 　 app应用 　 PostgreSQL配置 　 Postgresql下载 　 PostgreSQL入门教程 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图