How to invertly select columns in sql?

Question

If I have a SQL table with columns:

NR_A, NR_B, NR_C, NR_D, R_A, R_B, R_C

and on runtime, I add columns following the column's sequence such that the next column above would be R_D followed by R_E.

My problem is I need to reset the values of columns that starts with R_ (labeled that way to indicate that it is resettable) back to 0 each time I re-run my script . NR_ columns btw are fixed, so it is simpler to just say something like:

UPDATE table set col = 0 where column name starts with 'NR_'

I know that is not a valid SQL but I think its the best way to state my problem. Any thoughts?

EDIT: btw, I use postgres (if that would help) and java.

Answer 1

If you don't have many columns and you don't expect the schema to change, just list them explicitly.

UPDATE table SET NR_A=0;
UPDATE table SET NR_B=0;
UPDATE table SET NR_C=0;
UPDATE table SET NR_D=0;

Otherwise, a simple php script could dynamically build and execute your query:

<?php
    $db = pg_connect("host=localhost port=5432 user=postgres password=mypass dbname=mydb");
    if(!$db) die("Failed to connect");
    $reset_cols = ["A","B","C","D"];
    foreach ($col in $reset_cols) {
        $sql = "UPDATE my_table SET NR_" . $col . "=0";
        pg_query($db,$sql);
    }
?>

You could also lookup table's columns in Postgresql by querying the information schema columns tables, but you'll likely need to write a plpgsql function to loop over the query results (one row per table column starting with "NR_").

Answer 2

SQL doesn't support dynamically named columns or tables--your options are:

• statically define column references
• use dynamic SQL to generate & execute the query/queries

Java PreparedStatements do not insulate you from this--they have the same issue, just in Java.

Answer 3

if you rather using sql query script, you should try to get the all column based on given tablename.

maybe you could try this query to get all column based on given tablename to use in your query.

SELECT attname FROM 
pg_attribute, pg_type
WHERE typname = 'tablename' --your table name
AND attrelid = typrelid
AND attname NOT IN ('cmin', 'cmax', 'ctid', 'oid', 'tableoid', 'xmin', 'xmax') 
--note that this attname is sys column

the query would return all column with given tablename except system column

Answer 4

Are you sure you have to add columns during normal operations? Dynamic datamodels are most of the time a realy bad idea. You will see locking and performance problems.

If you need a dynamic datamodel, take a look at key-value storage. PostgreSQL also has the extension hstore, check the contrib.