A brief discussion on the comparison of varchar, char and text in postgresql database

A brief discussion on the comparison of varchar, char and text in postgresql database

As shown below:

name describe
character varying(n), varchar(n) Variable length, with length limit
character(n), char(n) Fixed length, fill in the blanks if the length is insufficient
text Variable length, no length limit

In short, varchar has a variable length, while char has an immutable length. For the postgresql database, the only difference between varchar and char is that the former is variable length, while the latter is fixed length. The maximum length of both is 10485760 (1GB).

Varchar does not specify a length and can store strings of the maximum length (1GB). However, char does not specify a length and the default is 1. This is something you need to pay attention to.

Text type: In the PostgreSQL database, there is almost no performance difference between text and varchar. The only difference is the storage structure.

The use of char should be used when the length of the string is certain, otherwise varchar or text should be used.

Official interpretation:

SQL defines two basic character types: character varying(n) and character(n), where n is a positive integer. Both types can store strings (not bytes) of up to n characters. Attempting to store a longer string into these types of fields will result in an error unless the excess characters are all whitespace, in which case the string will be truncated to the maximum length. This seemingly odd exception is required by the SQL standard. If the string to be stored is shorter than the declared length, a value of type character will be padded with blanks; a value of type character varying will simply store the shorter string.

If we explicitly convert a value to character varying(n) or character(n), then the overlong value will be truncated to n characters without throwing an error. This is also a requirement of the SQL standard.

varchar(n) and char(n) are aliases for character varying(n) and character(n) respectively. A character with no length declared is equivalent to character(1). If character varying is used without a length specifier, the type accepts strings of any length. The latter is a PostgreSQL extension.

In addition, PostgreSQL provides the text type, which can store strings of any length. Although the type text is not part of the SQL standard, many other SQL database systems have it.

Character values ​​are physically padded with blanks to the specified length n and are stored and displayed in this manner. However, the filled-in blank is semantically meaningless. When comparing two character values, any padding spaces are ignored. When converting to other string types, the spaces in the character value are removed. Note that in character varying and text values, trailing whitespace is semantic. And when using pattern matching, such as LIKE, use regular expressions.

The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, including the space-padding character. Longer strings have 4 bytes of overhead, not 1. Long strings will be automatically compressed by the system, so the physical requirements on disk may be smaller. Longer values ​​are also stored in a backend table so that they do not interfere with fast access to short field values. In any case, the longest string allowed to be stored is about 1GB. The maximum value of n allowed to appear in a data type declaration is smaller than this. There is little point in changing this behavior, since the number of characters and bytes can vary greatly in multi-byte encodings. If you want to store long strings with no specific upper limit, use text or character varying without a length declaration, rather than choosing an arbitrary length limit.

Tip: There is no performance difference between the three types, except for increased storage space when using blank-padded types, and some extra CPU cycles to check the length on storage when storing length-constrained columns. Although character(n) has certain performance advantages in some other database systems, it does not in PostgreSQL. In fact, character(n) is usually the slowest of the three because of the extra storage cost. In most cases, text or character varying should be used.

Supplement: Use PostGreSQL database for text input and text retrieval

Chinese word segmentation

ChineseParse.cs

using System;
using System.Collections;
using System.IO;
using System.Text.RegularExpressions;
namespace FullTextSearch.Common
{
  /// <summary>
  /// Chinese word segmenter.
  /// </summary>
  public class ChineseParse
  {
    private static readonly ChineseWordsHashCountSet _countTable;
    static ChineseParse()
    {
      _countTable = new ChineseWordsHashCountSet();
      InitFromFile("ChineseDictionary.txt");
    }
    /// <summary>
    /// Initialize the Chinese word dictionary and string frequency dictionary from the specified file.
    /// </summary>
    /// <param name="fileName">File name</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>
    /// Parse a string into ChineseWordUnit.
    /// </summary>
    /// <param name="s">string</param>
    /// <returns>The parsed 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("String parsing error: " + s);
      //}
      if (temp.Length != 1)
      {
        throw new Exception("String parsing error: " + s);
      }
      return new ChineseWordUnit(temp[0], Int32.Parse("1"));
    }
    /// <summary>
    /// Analyze the input string and cut it into individual words.
    /// </summary>
    /// <param name="s">String to be cut</param>
    /// <returns>The Chinese word array obtained by cutting</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>
  /// A dictionary class that records the number of times a string appears at the beginning of a Chinese word recorded in a Chinese dictionary. If the character string "中" appears at the beginning of "中国", the number of times is recorded in the dictionary.
  /// </summary>
  public class ChineseWordsHashCountSet
  {
    /// <summary>
    /// A Hashtable that records the number of times a string appears in a Chinese word. The key is a specific string, and the value is the number of times the string appears in the Chinese word.
    /// </summary>
    private readonly Hashtable _rootTable;
    /// <summary>
    /// Type initialization.
    /// </summary>
    public ChineseWordsHashCountSet()
    {
      _rootTable = new Hashtable();
    }
    /// <summary>
    /// Query the number of times the specified string appears at the beginning of a Chinese word recorded in a Chinese dictionary.
    /// </summary>
    /// <param name="s">Specify string</param>
    /// <returns>The number of times the string appears at the beginning of a Chinese word recorded in the Chinese dictionary. If it is -1, it means it does not appear. </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>
    /// Insert a word into the frequency dictionary. Parse the word and insert it into the frequency dictionary.
    /// </summary>
    /// <param name="s">The string to be processed. </param>
    public void InsertWord(string s)
    {
      for (int i = 0; i < s.Length; i++)
      {
        string _s = s.Substring(0, i + 1);
        InsertSubString(_s);
      }
    }
    /// <summary>
    /// Insert a string count record into the count dictionary.
    /// </summary>
    /// <param name="s">The string to be inserted. </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>
    /// Structure initialization.
    /// </summary>
    /// <param name="word">Chinese word</param>
    /// <param name="power">The weight of the word</param>
    public ChineseWordUnit(string word, int power)
    {
      _word = word;
      _power = power;
    }
    /// <summary>
    /// The Chinese word corresponding to the Chinese word unit.
    /// </summary>
    public string Word
    {
      get { return _word; }
    }
    /// <summary>
    /// The weight of the Chinese word.
    /// </summary>
    public int Power
    {
      get { return _power; }
    }
  }
}

ChineseDictionary.txt

Main window interface

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 + "Add to database!");
        rTB_unosTextaUBazu.Clear();
      }
      else
      {
        MessageBox.Show("Empty data is not allowed!");
      }
    }
    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 = "Number of files found: " + 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("You are successfully connected!");
        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 code:

using System.Collections.Generic;
namespace FullTextSearch
{
  internal class SQLquerys
  {
    private static string giveMeTheKey;
    private static int tempInt = 1;
    //Set the connection string public void setTheKey(string connString)
    {
      giveMeTheKey = connString;
      giveMeTheKey += "";
    }
    //Store the connection string in a static variable public string getTheKey()
    {
      giveMeTheKey += "";
      return giveMeTheKey;
    }
    public void setCounter()
    {
      tempInt = 1;
    }
    //Analyze the string for search based on the selection of AND 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;
    }
    //Helper method public List<string> splitInputField(string[] inputField)
    {
      var unfinishedList = new List<string>();
      foreach (string splitString in inputField)
      {
        unfinishedList.Add(splitString);
      }
      return unfinishedList;
    }
    //Helper method public string[] splitListForInput(string inputString)
    {
      string[] parsedList = null;
      parsedList = inputString.Split(' ');
      return parsedList;
    }
    // Function to create ts function in PostgreSQL for dictionary search 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;
    }
    //Create SQL query depends on which type of search is selected and also depends on AND or OR selector 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')
      {
        //This query is written like this just for testing purposes, it needs to be changed 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')
      {
        //This query is written like this just for testing purposes, it needs to be changed 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;
    }
    //This method is used to parse the date 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;
    }
    // This code is used to create an analysis, it does some date/time manipulation to be able to create an analysis for the selected date/time.
    public string createSqlForDayAnalysis(string dateFrom, string dateTo)
    {
      string insertIntoTempTable = "";
      string dateTimeForAnalysis = "";
      int[] tempFrom = parseForDates(dateFrom);
      int[] tempTo = parseForDates(dateTo);
      //Month change algorithm 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 code:

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 script:

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;

The running results are shown in the figure:

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in:
  • Usage of varchar field type in PostgreSQL
  • PostgreSQL common command operations for modifying table fields
  • Compatibility comparison between PostgreSQL and MySQL data types
  • Solve the problem of postgreSql reporting an error when changing the Varchar type field to Int type
  • How to modify the storage method of text type fields in PostgreSQL
  • PostgreSQL TIMESTAMP type timestamp operations

<<:  Docker - Summary of 3 ways to modify container mount directories

>>:  Introduction to the usage of common XHTML tags

Recommend

Detailed explanation of JavaScript timers

Table of contents Brief Introduction setInterval ...

HTML input box optimization to improve user experience and ease of use

In order to improve user experience and ease of us...

A brief analysis of React's understanding of state

How to define complex components (class component...

Introduction to Jenkins and how to deploy Jenkins with Docker

1. Related concepts 1.1 Jenkins Concepts: Jenkins...

Detailed explanation of several storage methods of docker containers

Table of contents Written in front Several storag...

11 Linux KDE applications you didn't know about

KDE Abbreviation for Kool Desktop Environment. A ...

CSS3 changes the browser scroll bar style

Note: This method is only applicable to webkit-ba...

How to install the green version of MySQL Community Server 5.7.16 and implement remote login

1. Download MySQL Community Server 5.7.16 and ins...

Summary of Linux operation and maintenance from elementary to advanced knowledge points

Being an operation and maintenance engineer is a ...

Summary of basic usage of $ symbol in Linux

Linux version: CentOS 7 [root@azfdbdfsdf230lqdg1b...

Detailed installation and configuration tutorial of MySQL 5.7 under Win10

1. Unzip MySQL 5.7 2. Create a new configuration ...

Windows10 mysql 8.0.12 non-installation version configuration startup method

This article shares the specific steps for config...

Semantics, writing, and best practices of link A

The semantics, writing style, and best practices ...

js code that associates the button with the enter key

Copy code The code is as follows: <html> &l...

MySQL 5.7.18 zip version installation and configuration method graphic tutorial (win7)

The installation of mysql5.7.18zip version on Win...