Postgresql – How to Substitute Variables in PostgreSQL

postgresqlpsqlsqlplus

The given Oracle query inserts data into table by substituting variables. How I achieve the same in PostgreSQL?

INSERT INTO control_threshold (
    threshold_id, group_name, description, sql, low_value, high_value)
VALUES(
    threshold_seq.nextval, '&2', '&1',  TRIM('&5' || '&6' || '&7' || '&8' || '&9'),
    trim('&3'), trim('&4'));

Best Answer

These variables are a feature of SQL*Plus.

psql, the equivalent program in the PostgreSQL world, also has variables.

Use

\set variable 'value'

and

insert into mytable(mycolumn)
values (:variable);

Related Solutions

PostgreSQL – How to Convert Numeric Values to Text in SQL for Select Query

Once you have removed the [ and ] you can use string_to_array() to convert the list to an array of integers. This can be used directly in the where clause:

select * 
from mytable 
where numeric_column = ANY(string_to_array(trim('[123, 124]', '[]'),',')::numeric[])

PostgreSQL – Insert from Select Query with Static Values

You can put static values into SELECT clause.

INSERT INTO new_tbl (column1, column2, column3)
    SELECT DISTINCT id, 'a string', 0 FROM -- long where clause --;

Best Answer

Related Solutions

PostgreSQL – How to Convert Numeric Values to Text in SQL for Select Query

PostgreSQL – Insert from Select Query with Static Values

Related Question