How can I create a blank/hardcoded column in a sql query?

Question

I want have a query with a column that is a hardcoded value not from a table, can this be done? I need it basically as a placeholder that I am going to come back to later and fill in.

example:

SELECT
hat,
shoe,
boat,
somevalue = 0 as placeholder
FROM
objects

then I would loop through this query later and fill in the placeholder

in this example someValue is not a field in objects, I need to fake it. I am doing this in coldfusion and using two datasources to complete one query. I have tried the space() function but have been unable to get it to work.

Thanks.

The trouble is the equals sign I believe. See my answer below. — g.d.d.c, Mar 03 '11 at 19:52
Yes, but the `somevalue = 0 as placeholder` syntax doesn't make sense. Simply `0 as placeholder` as suggested by Galz is the way to go. — Rob, Aug 14 '12 at 19:40

score 98 · Accepted Answer · edited May 28 '14 at 09:31

98

SELECT
    hat,
    shoe,
    boat,
    0 as placeholder
FROM
    objects

And '' as placeholder for strings.

edited May 28 '14 at 09:31

Bitterblue

13,162
17
86
124

answered Mar 03 '11 at 19:52

Galz

6,713
4
33
39

How about for type timestamptz? – Jobs Aug 15 '19 at 19:14

score 10 · Answer 2 · answered Mar 03 '11 at 19:52

10

This should work on most databases. You can also select a blank string as your extra column like so:

Select
  Hat, Show, Boat, '' as SomeValue
From
  Objects

answered Mar 03 '11 at 19:52

g.d.d.c

46,865
9
101
111

score 4 · Answer 3 · answered Mar 12 '15 at 18:40

4

For varchars, you may need to do something like this:

select convert(varchar(25), NULL) as abc_column into xyz_table

If you try

select '' as abc_column into xyz_table

you may get errors related to truncation, or an issue with null values, once you populate.

answered Mar 12 '15 at 18:40

Benjamin Sternlieb

41
2

Yes, when using `select...into` to create a new table it is important to be more explicit about data type and capacity. Otherwise, the db will use the empty string to "guess" about the target column's properties. In the case of an empty string - it will almost certainly come up with the wrong answer. – Leigh Mar 12 '15 at 19:49

score 2 · Answer 4 · answered Mar 04 '11 at 20:32

The answers above are correct, and what I'd consider the "best" answers. But just to be as complete as possible, you can also do this directly in CF using queryAddColumn.

See http://www.cfquickdocs.com/cf9/#queryaddcolumn

Again, it's more efficient to do it at the database level... but it's good to be aware of as many alternatives as possible (IMO, of course) :)

score 0 · Answer 5 · edited Feb 25 '16 at 16:14

0

SELECT
    hat,
    shoe,
    boat,
    0 as placeholder -- for column having 0 value    
FROM
    objects


--OR '' as Placeholder -- for blank column    
--OR NULL as Placeholder -- for column having null value

edited Feb 25 '16 at 16:14

James A Mohler

11,060
15
46
72

answered Feb 25 '16 at 09:14

Shiraj Momin

665
6
8

4

This is a [repeat](http://stackoverflow.com/a/5185769/104223) of the [existing answers](http://stackoverflow.com/a/29017794/104223). – Leigh Feb 25 '16 at 18:31

score 0 · Answer 6 · answered Jan 25 '19 at 12:50

0

Thank you, in PostgreSQL this works for boolean

SELECT
hat,
shoe,
boat,
false as placeholder
FROM
objects

answered Jan 25 '19 at 12:50

mapping dom

1,737
4
27
50

How can I create a blank/hardcoded column in a sql query?

6 Answers6

Linked