GQeDRC8i

1How to check if record exists in database - fastest method
2String checkIfAlreadyScanned = "SELECT id FROM "STRINGS_DB"  where STR ='" + mystring + "'";
3	
4INSERT INTO silly_table(the_text)
5 'literal_text'
6WHERE NOT EXISTS (
7    SELECT *
8    FROM silly_table
9    WHERE the_text = 'literal_text'
10    );
11	
12DROP TABLE exitsnot CASCADE;
13CREATE TABLE exitsnot
14        ( id SERIAL NOT NULL PRIMARY KEY
15        , val INTEGER -- REFERENCES something
16        , str varchar -- REFERENCES something
17        );
18
19INSERT INTO exitsnot (val)
20SELECT 42
21WHERE NOT EXISTS (
22        SELECT * FROM exitsnot
23        WHERE val = 42
24        );
25INSERT INTO exitsnot (str)
26SELECT 'silly text'
27WHERE NOT EXISTS (
28        SELECT * FROM exitsnot
29        WHERE str = 'silly text'
30        );
31SELECT version();
32	
33DROP TABLE
34NOTICE:  CREATE TABLE will create implicit sequence "exitsnot_id_seq" for serial column "exitsnot.id"
35NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "exitsnot_pkey" for table "exitsnot"
36CREATE TABLE
37INSERT 0 1
38INSERT 0 1
39                                           version                                            
40----------------------------------------------------------------------------------------------
41 PostgreSQL 9.1.2 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
42(1 row)
43	
44String checkIfAlreadyScanned = "SELECT 1 FROM "STRINGS_DB"  where STR ='" + mystring + "'";
45	
46CREATE TABLE strings_db (
47    id       PRIMARY KEY INT,
48    text     TEXT,
49    hash     TEXT
50);
51	
52SELECT id FROM strings_db WHERE hash=calculate_hash(?)
53	
54String checkIfAlreadyScanned = @"
55    SELECT id 
56    FROM ""STRINGS_DB""  
57    where STR ='" + mystring + @"'
58    limit 1";
59	
60String checkIfAlreadyScanned = @"
61    SELECT id 
62    FROM ""STRINGS_DB""  
63    where STR = '@mystring'
64    limit 1
65    ".replace("@mystring", mystring);
66	
67CREATE INDEX strings_hash_idx ON "STRINGS_DB" USING hash (str);
68	
69select 1
70where exists(
71    select 1 
72    from STRINGS_DB
73    where STR = 'MyString'
74)
75	
76String checkIfAlreadyScanned = "SELECT TOP 1 id FROM "STRINGS_DB"  where STR ='" + mystring + "'";