pq_query Syntax Error because of a hyphen. How do i write this?

Question

This does not work

$query = "SELECT * FROM time-lords WHERE user='thedoctor'";

I get this error:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "-"

This one does work

$query = "SELECT * FROM time_lords WHERE user='thedoctor'";

So I suppose the issue is the hyphen. What is the correct way to write this? I've tried wrapping the column name in just about everything....."time-lords", [time-lords], `time-lords` and nothing seems to work.

You'll need (escaped) double-quotes. See https://stackoverflow.com/questions/50751724/double-quotes-around-hyphenated-table-name-fails But you really ought to be using an underscore to prevent issues like this. — aynber, Mar 21 '19 at 17:24
It's a good practice to wrap table and column names with backticks — Dev Man, Mar 21 '19 at 17:27
@ImmortalDude It's an even better practice to make sure your table and column names are simple enough you don't need to. Dashes, spaces, and other things are allowed but are *annoying*. — tadman, Mar 21 '19 at 17:31
@tadman it's still a good practice even if your table names are simple — Dev Man, Mar 21 '19 at 17:32
@ImmortalDude *backticks* are an oddity of mysql and are not used in Postgress — Nigel Ren, Mar 21 '19 at 17:33

score 3 · Answer 1 · answered Mar 21 '19 at 17:30

3

You can escape object names with double quotes ("):

$query = "SELECT * FROM \"time-lords\" WHERE user='thedoctor'";

answered Mar 21 '19 at 17:30

Mureinik

297,002
52
306
350

score 0 · Accepted Answer · answered Mar 21 '19 at 17:55

0

Thanks! That solved the syntax error. To get the query to filter I had to do the escape double quote on the column as well:

$query = "SELECT * FROM \"time-lords\" WHERE \"user\"='thedoctor'";

answered Mar 21 '19 at 17:55

Rex Reyes

91
6

pq_query Syntax Error because of a hyphen. How do i write this?

2 Answers2