19

I have the following table:

CREATE TABLE lawyer (
  id SERIAL PRIMARY KEY,
  name VARCHAR NOT NULL UNIQUE,
  name_url VARCHAR check(translate(name_url, 'abcdefghijklmnopqrstuvwxyz-', '') = '') NOT NULL UNIQUE
);

I want to SELECT * FROM lawyer where name_url = "john-doe"

Anders Marzi Tornblad
  • 18,896
  • 9
  • 51
  • 66
Jeka
  • 1,600
  • 3
  • 22
  • 36
  • 1
    You could use `SELECT * FROM lawyer where name_url = 'john-doe'` Which is pretty much what you wrote, but with single quotes around your string literal. – JNevill Jun 16 '16 at 20:44
  • So what is your question? Don't use double-quotes for string literals, use single-quotes as in `'john-doe'`. Also, using `varchar` without specifying column width is kind of bad practice. – jpw Jun 16 '16 at 20:44

2 Answers2

36

Character literals are put into single quotes:

SELECT * 
FROM lawyer 
where name_url = 'john-doe';

See the manual for details:
https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

1

Looking for exact match:

select column1, column2 from mytable where column2 like 'string';

Pattern Match can be achieved using:(returns stringxx, stringyyy..) 

select column1, column2 from mytable where column2 like 'string%';

This will return any column2 that matches string***.

For using OR condition, to compare multiple strings below one can be used:

select column1, column2 from mytable where column2 ~* 'string1|string2';
Anto
  • 3,128
  • 1
  • 20
  • 20