Postgresql Select Constant

Question

In Oracle I can select a constant value that will populate down the column like this:

Select 
     "constant" constantvalue,
     orders.name
from 
     orders

and it will yield:

ConstantValue     Name
  constant       sandwich
  constant        burger

For whatever reason, when I try to do this in postgres I receive this error.

ERROR:  column "Constant" does not exist

here is my code

    select
        date_trunc('day', measurement_date + (interval '1 day' * (6 - extract(dow from measurement_date)))) week,
        "AROutstanding" colname,
        round(avg(Total_Outstanding),0) numbah
    from
                (
                select
                    measurement_date,

                    sum(cast(sum_of_dollars as numeric)) Total_Outstanding
                from
                    stock_metrics
                where
                    invoice_status not in  ('F','Write off')
                group by
                    measurement_date
                ) tt
            group by
                week

Single quotes are the correct way to express a constant string value in SQL (and they work in any database). I consider this a simple typographic error. — Gordon Linoff
– Gordon Linoff, Commented Feb 25, 2016 at 2:18

Adrian Lynch · Accepted Answer · 2022-11-29 10:12:05Z

42

Change your double quotes to single quotes.

So this:

Select 
     "constant" as constantvalue,
     orders.name
from 
     orders

Should be this:

Select 
     'constant' as constantvalue,
     orders.name
from 
     orders

edited Nov 29, 2022 at 10:12

answered Feb 25, 2016 at 2:34

Adrian Lynch

8,5442 gold badges36 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brett Brockway Over a year ago

Thanks, that worked. I thought I tried it but apparently not :)

Adrian Lynch Over a year ago

It's always a pain moving from one DB to another. I went from MSSQL to MySQL and I'm googling for answers all the time!

AdamKalisz Over a year ago

You should be careful when "Omitting the AS Key Word" as noted in the manual postgresql.org/docs/current/sql-select.html some of the keywords postgresql.org/docs/current/sql-keywords-appendix.html could well interfere with the chosen column name.

RCRalph · Accepted Answer · 2022-06-23 18:03:16Z

3

For anyone confused by @Adrian Lynch's answer, I found out that doing

SELECT
    'constant' AS ConstantValue,
    orders.name
FROM
    orders

did the trick for me.

This could be used for numbers as follows: 1234 AS constantvalue.

edited Jun 23, 2022 at 18:03

answered Jun 15, 2022 at 13:17

RCRalph

4049 silver badges19 bronze badges

2 Comments

Adrian Lynch Over a year ago

What part was confusing @RCRalph? The lack of as? I've added it to the answer.

RCRalph Over a year ago

@AdrianLynch yes that was it, thanks for editing

Collectives™ on Stack Overflow

Postgresql Select Constant

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related