Postgres - Create Table from Select

How create a table AS SELECT with a serial field

You can create the table as select:

create table source.road_nodes as
select (row_number() over())::int node_id, node::text
from (  
    select node_begin node from map.rto
    union
    select node_end node from map.rto
    ) sub;

and the data in the table will be as expected, but the column node_id will have no default.

You can, however, manually add the appropriate default post factum:

create sequence road_nodes_node_id_seq;
select setval('road_nodes_node_id_seq', 
    (select node_id from source.road_nodes order by 1 desc limit 1));
alter table source.road_nodes alter node_id set not null; -- not necessary
alter table source.road_nodes alter node_id set default 
    nextval('road_nodes_node_id_seq'::regclass);

create table from select - any options to refresh or auto update?

A dynamic view is better as data will always be fresh and there won't be any extra space used. If tuning can't be done then materialized view should be used.

Here are query tuning tips that can be useful in your case. This will help for materialized view or a dynamic view whichever way you want to go.

1. Avoid joining with an inline view having 'group by' init because it prevents predicate push down. Instead of joining with 'ecom_events' which has group by in it, join with 'ga_flagship_ecom.events' table directly and then group by on the result.
2. ga_flagship_ecom.sessions can be range partitioned on "date" column. When you are needing data for last 30 days then one or few partitions will be scanned instead of full table.
3. Build the final view without any 'group by' in it. Apply the group by condition while queuing the view.
4. Index both tables on session_id as join is happening on this.

    combined as (
    select 
        s.date,
        s.user_type,
        s.device_category,
        s.operating_system,
        s.country,
        s.region,
        s.source,
        s.medium,
        s.campaign,
        s.channel_grouping,
        coalesce(e.event_action, 'All Sessions') as event_action,
        coalesce(e.event_label, 'All Sessions') as event_label,
        count(distinct s.client_id) as users,
        sum(s.sessions) as sessions,
        sum(s.bounces) as bounces,
        sum(s.transactions) as transactions,
        sum(s.transaction_revenue) as revenue
    from sessions s 
    left join ga_flagship_ecom.events e on e.session_id = s.session_id
    where e.event_category = 'ecom'
    )

Apply group by condition and predicates while querying combined. This will push session_id predicate inside the query, few results will be returned and the group by will be faster too.

    select ... from combined c where c.session_id = ? group by .... 

postgresql : create table as union select with a serial key

The syntax of "Create table ... As ..." does not allow defining additional columns, it defines only columns from the select statement. Of course you can persist by selecting a constant as a place holder and establish the column name. Then create a sequence, update the table to set value for the place holder and then a few alter tables to complete the desired definition.

A much easier way would just be a 2 step process:

1. Create the table.
2. Populate with simple select.

create table DIM_airport( airportkey  integer generated always as identity 
                        , airportcode text 
                        , airportname text
                        , city        text
                        , constraint  DIM_airport_pk
                                      primary key (airportkey)
                        ) ;

insert into  DIM_airport(airportcode, airportname, city)
      select originairportcode  
           , origairportname 
           , origincityname
      union 
      select destairportcode  
           , destairportname  
           , destcityname ;  

You do not need DISTINCT on either select as the UNION itself eliminates duplicates.
See examples here.

PostgreSQL, CREATE TABLE AS with predefined column(s)

you either create table, defining its structure (with all handy shortcuts and options in one statement), or create table as select, "inheriting" [partially] the structure. Thus if you want primary key, you will need alter tabale any way...

To put id as first column in one statement, you can simply use a dummy value, eg sequential number:

t=# create table s as select row_number() over()  as id,chr(n) from generate_series(197,200) n;
SELECT 4
t=# select * from s;
 id | chr
----+-----
  1 | Å
  2 | Æ
  3 | Ç
  4 | È
(4 rows)

Of course after that you still need to create sequence, assign its value as default to the id column and add primary key on ot. Which makes it even more statements then you have ATM...

CREATE TABLE WITH NO DATA is very slow

WITH NO DATA still executes the query, it just ignores the result.

The better way to do that would be to avoid CREATE TABLE ... AS:

CREATE TABLE my_temp_table (LIKE my_enormous_table);

That also allows you to use the INCLUDING clause to copy default values, storage parameters, constraints and other things from the original table:

CREATE TABLE my_temp_table (LIKE my_enormous_table
                            INCLUDING CONSTRAINTS INCLUDING DEFAULTS);

CREATE TABLE if not exists based on a SELECT statement

I did get an alternate for this. I used the below mentioned code.

CREATE OR REPLACE FUNCTION ccdb_archival.ccdb_archival()
 RETURNS void AS
$BODY$
BEGIN

CREATE TABLE IF NOT EXISTS ccdb_archival.bills (LIKE ccdb.bills INCLUDING ALL);
    BEGIN
        ALTER TABLE ccdb_archival.bills ADD COLUMN archival_date timestamp;
    EXCEPTION
        WHEN duplicate_column THEN RAISE NOTICE 'column archival_date already exists in ccdb_archival.bills.';
    END;

INSERT INTO ccdb_archival.bills
SELECT *, now() AS archival_date
FROM ccdb.bills
WHERE bill_date::date >= current_date - interval '3 years' AND bill_date::date < current_date - interval '8 years';

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

How do I create a IF statement creating a table in Postgres?

Use a CHECK constraint with a CASE expression:

CREATE TABLE cat_accident (
    acc_type VARCHAR(30) NOT NULL,
    acc_descrip VARCHAR(30) NOT NULL 
    CHECK(
      CASE acc_type
        WHEN 'Home accident' THEN acc_descrip IN ('Intoxication', 'burns', 'Kitchen wound')
        WHEN 'Work accident' THEN acc_descrip IN ('freezing', 'electrocution')
      END
    )
);

See the demo.

---

Related Topics