CODEWITHSUNDEEP
amazon-redshiftplpgsqlpg-dump
Flo
Flo

Reputation: 253

pg_dump outputs empty create statements

So I am trying to dump a couple of tables from a Redshift database using pg_dump. I am not interested in the data itself here, which will be extracted in a seperate process using UNLOAD.

However, altough pg_dump executes without any errors, the CREATE TABLE statements in the output-sql-script are empty, e.g. I get something like

CREATE TABLE test_data_sampling.orders();

Funnily enough, this is followed by

ALTER TABLE ONLY test_data_sampling.orders ALTER COLUMN orders_id SET NOT NULL;

which seems to indicate that pg_dump is fully aware of at least one column in the table.

My pg_dump call looks like this:

pg_dump -s -t test_data_sampling.customer -t test_data_sampling.orders -t test_data_sampling.addr --dbname=postgresql://<user>:<password>@<host>:<port>/dbName?ssl=true --file=backup_dbName.sql

When I log into the DB with the same user I specified in the pg_dump call, I can query the tables without any issues (i.e. they do have columns). My user ist the owner of the tables as well as the owner of the schema.

I am at a loss. Any help would be greatl appreciated.

EDIT: Some additional information.

The tables in question are the product of a 

CREATE TABLE <table> (LIKE <parentTable>);

In the case of orders, the ddl of the parent looks like this:

CREATE TABLE IF NOT EXISTS <parentTableName>(
    orders_id int primary key, 
    cust_id int, 
    item varchar(50), 
    order_date date, 
    descr varchar(100)
);

As one might guess from these DDL, these are just some dummy test tables.

Upvotes: 1

Views: 522

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

There is a view for generating table DDL in the Redshift Utils library on GitHub .

Other items in the library are also useful for extracting existing items.

Upvotes: 1

Related Questions