Thread: any psql \copy tricks for default-value columns without source data?
So, I am trying to import a file into a table and want to assign a sequence value to each record as it is imported. I know that I can pre-process the input file and simply add the needed data but I am curious if maybe there is some trick to having defaults populate for missing columns WITHOUT explicitly specifying each and every column that is present? Thanks! David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/any-psql-copy-tricks-for-default-value-columns-without-source-data-tp5802795.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, May 05/06/14, 2014 at 01:22:20PM -0700, David G Johnston wrote: > So, I am trying to import a file into a table and want to assign a sequence > value to each record as it is imported. > > I know that I can pre-process the input file and simply add the needed data > but I am curious if maybe there is some trick to having defaults populate > for missing columns WITHOUT explicitly specifying each and every column that > is present? There isn't really any way to get around specifying the columns. I wrote a tool to help import files into the database, you could probably make use of it: https://github.com/f0rk/csv2table Assuming the table already exists and your csv has columns with the same names as the columns in your table: csv2table --file /path/to/your/file.csv --no-create --copy --backslash | psql If the table doesn't exist, you could do: csv2table --file /path/to/your/file.csv --copy --backslash | vipe | psql And edit the create statement to add a SERIAL column (the copy command will specify all of the columns in the file for you). It's a 95% solution I use to get delimited files into the database. It's not perfect but it works most of the time. If you have any issues or feature requests feel free to open an issue on github. -Ryan Kelly On Tue, May 6, 2014 at 4:22 PM, David G Johnston <david.g.johnston@gmail.com> wrote: > So, I am trying to import a file into a table and want to assign a sequence > value to each record as it is imported. > > I know that I can pre-process the input file and simply add the needed data > but I am curious if maybe there is some trick to having defaults populate > for missing columns WITHOUT explicitly specifying each and every column that > is present? > > Thanks! > > David J. > > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/any-psql-copy-tricks-for-default-value-columns-without-source-data-tp5802795.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On 5/6/2014 1:22 PM, David G Johnston wrote: > I know that I can pre-process the input file and simply add the needed data > but I am curious if maybe there is some trick to having defaults populate > for missing columns WITHOUT explicitly specifying each and every column that > is present? if you didn't specify the columns in your file, how would you expect it to know whats there and not there? -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: any psql \copy tricks for default-value columns without source data?
From
David G Johnston
Date:
On 5/6/2014 1:22 PM, David G Johnston wrote:if you didn't specify the columns in your file, how would you expect it
> I know that I can pre-process the input file and simply add the needed data
> but I am curious if maybe there is some trick to having defaults populate
> for missing columns WITHOUT explicitly specifying each and every column that
> is present?
to know whats there and not there?
The default copy behavior is column-order dependent. If your input file has 10 columns and the table has 10 columns they get matched up 1-to-1 and everything works just fine. It would be nice if there was some way to say that if the table has 12 columns but the file has 10 columns that the first 10 columns of the table get matched to the file and the remaining two columns use their default values; that way you can add default columns to the end of the table and still do an auto-matching import.
David J.
View this message in context: Re: any psql \copy tricks for default-value columns without source data?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Using filter you could get the function you want.
Another solution is pgloader (http://pgloader.tapoueh.org) , but I don't know if it is as fast as copy.
Cheers,Another solution is pgloader (http://pgloader.tapoueh.org) , but I don't know if it is as fast as copy.
Rémi-C
2014-05-06 23:04 GMT+02:00 David G Johnston <david.g.johnston@gmail.com>:
On 5/6/2014 1:22 PM, David G Johnston wrote:if you didn't specify the columns in your file, how would you expect it
> I know that I can pre-process the input file and simply add the needed data
> but I am curious if maybe there is some trick to having defaults populate
> for missing columns WITHOUT explicitly specifying each and every column that
> is present?
to know whats there and not there?The default copy behavior is column-order dependent. If your input file has 10 columns and the table has 10 columns they get matched up 1-to-1 and everything works just fine. It would be nice if there was some way to say that if the table has 12 columns but the file has 10 columns that the first 10 columns of the table get matched to the file and the remaining two columns use their default values; that way you can add default columns to the end of the table and still do an auto-matching import.David J.
View this message in context: Re: any psql \copy tricks for default-value columns without source data?
Re: any psql \copy tricks for default-value columns without source data?
From
David G Johnston
Date:
Thank you everyone; some good programs to check out but I just went ahead and used "awk" to add two additional columns of data to the input file before sending it onto psql. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/any-psql-copy-tricks-for-default-value-columns-without-source-data-tp5802795p5802914.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.