I want to write a simple query to select a number of columns in PostgreSQL. However, I keep getting errors - I tried a few options but they did not work for me. At the moment I am getting the following error:
org.postgresql.util.PSQLException: ERROR: syntax error at or near "column"
To get the columns with values I try the followig:
select * from weather_data where column like '%2010%'
Any ideas?
Removing duplicate rows from a query result set in PostgreSQL can be done using the SELECT statement with the DISTINCT clause. It keeps one row for each group of duplicates. The DISTINCT clause can be used for a single column or for a list of columns.
Wildcards in PostgreSQL is used to find matching rows values from tables; it is also used to find matching patterns rows from tables, Wildcards is also used to find matching rows, column and tables names; the output of the wildcard operator will return matching name, which was table name, column name or rows, In ...
column
is a reserved word. You cannot use it as identifier unless you double-quote it. Like: "column"
.
Doesn't mean you should, though. Just don't use reserved words as identifiers. Ever.
To ...
select a list of columns with 2010 in their name:
.. you can use this function to build the SQL command dynamically from the system catalog table pg_attribute
:
CREATE OR REPLACE FUNCTION f_build_select(_tbl regclass, _pattern text)
RETURNS text AS
$func$
SELECT format('SELECT %s FROM %s'
, string_agg(quote_ident(attname), ', ')
, $1)
FROM pg_attribute
WHERE attrelid = $1
AND attname LIKE ('%' || $2 || '%')
AND NOT attisdropped -- no dropped (dead) columns
AND attnum > 0; -- no system columns
$func$ LANGUAGE sql;
Call:
SELECT f_build_select('weather_data', '2010');
Returns something like:
SELECT foo2010, bar2010_id, FROM weather_data;
You cannot make this fully dynamic, because the return type is unknown until we actually build the query.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With