Select Columns with Particular Column Names in Postgresql

Select columns with particular column names in PostgreSQL

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.

Selecting certain columns from a table with dates as columns

It's nearly impossible to get the actual value of the current month as the column name, but you can do something like this:

select d.item_sku,
       d.status,
       to_jsonb(d) ->> to_char(current_date, 'yyyy-mm') as current_month,
       to_jsonb(d) ->> to_char(current_date + interval '1 month', 'yyyy-mm') as "month + 1",
       to_jsonb(d) ->> to_char(current_date + interval '2 month', 'yyyy-mm') as "month + 2"
from bad_design d
;

Select from columns where column names are in list of strings

Using sql module from psycopg2. An example that I believe is more on point and cleaner then the answer posted in the comment.

import psycopg2
from psycopg2 import sql 

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)

cols = ['first', 'second', 'third']

qry = sql.SQL("SELECT {} FROM table_x WHERE year=2021").format(sql.SQL(", ").join(map(sql.Identifier, cols)))

print(qry.as_string(con))                                                                                                                                                 
SELECT "first", "second", "third" FROM table_x WHERE year=2021

How to get a list column names and datatypes of a table in PostgreSQL?

SELECT
        a.attname as "Column",
        pg_catalog.format_type(a.atttypid, a.atttypmod) as "Datatype"
    FROM
        pg_catalog.pg_attribute a
    WHERE
        a.attnum > 0
        AND NOT a.attisdropped
        AND a.attrelid = (
            SELECT c.oid
            FROM pg_catalog.pg_class c
                LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
            WHERE c.relname ~ '^(hello world)$'
                AND pg_catalog.pg_table_is_visible(c.oid)
        );

More info on it : http://www.postgresql.org/docs/9.3/static/catalog-pg-attribute.html

How to list the columns of a view in Postgres?

Postgres has dedicated System Catalog Information Functions to help with that.

To get the full view definition:

SELECT pg_get_viewdef('public.view_name');

Schema-qualification is optional. If no schema is prefixed, the current search_path setting decides visibility.

A quick hack would be to just:

SELECT * FROM public.view_name LIMIT 0;

Depending on your client, column names and types should still be displayed. Or LIMIT n to get some sample values, too. The underlying query is actually executed then (unlike with LIMIT 0).

To list columns and their data type, in order, you might base the query on pg_attribute:

SELECT attname AS column_name, format_type(atttypid, atttypmod) AS data_type
FROM   pg_attribute
WHERE  attrelid = 'public.view_name'::regclass
-- AND    NOT attisdropped
-- AND    attnum > 0
ORDER  BY attnum;

Type modifiers like maximum length are included in data_type this way.

Internally, a VIEW is implemented as special table with a rewrite rule. Details in the manual here. The table is saved in the system catalogs much like any regular table.

About the cast to regclass:

• How to check if a table exists in a given schema

The same query works for tables or materialized views as well. Uncomment the additional filters above to only get visible user columns for tables.

Select * vs Selecting All Column Names

This is an elaboration on my comment.

There is definitely no harm in using select * for an ad hoc query. It is a great convenience and common.

The problem arises when you want to run the same query over time. In particular, if the query is compiled, then changes to the underlying table can cause unexpected problems. I "fondly" recall spending about 10 hours debugging a problem (starting at 6:00 a.m.) caused by a select * in a view, when a column type in the underlying table was changed. The code was not recompiled and the offsets in the data records were off.

Even this situation can be fixed by forcing a re-compile. And, I commonly use SELECT * in ad hoc queries.

Your question summarizes some of the key points, such as wide columns slowing down a query.

Rename all columns from all tables with specific column name in PostgreSQL?

If you have superuser privileges you can make the changes in one sweep in the system catalogs:

UPDATE pg_attribute
SET attname = 'location_name'
WHERE attname = 'location';

---

Related Topics